• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

can i use DAO to connect to SQL server 2000?

I have an application that used Access 2000 as backend and frontend. We moved backend to SQL 2000 and of course we are getting errors "record not updateable. etc..."
It seems that the whole CBF is DAO. If I leave the DAO code:
Dim db As Database
Dim rs AS Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("table name", dbOpenDynaset)

It does not work, it is looking for the db locally, and the tables are linked to SQL. Is there a way to keep DAO or am I going to have rewrite the frontend?
Thank You ahead of time.
0
ircpamanager
Asked:
ircpamanager
2 Solutions
 
fcfangCommented:
I still have a bunch of legacy apps that use DAO to access that that has since moved to SQL2k.

I just have to make sure that the tables from the SQL2k server are Linked into the current db. Once this is done, you can rename the tables to match what they once were and it should work without a problem.

"Record not updateable" problems are sometimes also caused when there is no primary key on the SQL2k table and none chosen to be a pk when linking into the AccessDB.

Hope this helps.
Frank.
0
 
fcfangCommented:
You may also have to change your code depending on your query from

set rs = db.openrecordset("tbalename",dbopendynaset)

to

set rs = db.openrecordset("tbalename",dbopendynaset, dbseechanges)
0
 
ircpamanagerAuthor Commented:
I linked the tables. Then renamed them to what they were. But I still get the errors "This recordset is not updateable."
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ircpamanagerAuthor Commented:
I also get "Cannot update. Database ot object is read-only"
0
 
raopsnCommented:
All the linked tables should have a PRIMARY KEY in the SQL Server, otherwise the recordset or not updatable
0
 
SidFishesCommented:
"This recordset is not updateable."

likely means you doen't have primary keys defined. SQL server needs them, access doesn't "require" them

"Cannot update. Database ot object is read-only"

Make sure you have you sql server permissions set so the table rows can be updated and inserted and deleted
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now