[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


can i use DAO to connect to SQL server 2000?

Posted on 2006-04-20
Medium Priority
Last Modified: 2008-02-01
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.
Question by:ircpamanager

Expert Comment

ID: 16500687
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.

Assisted Solution

fcfang earned 1000 total points
ID: 16500704
You may also have to change your code depending on your query from

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


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

Author Comment

ID: 16500721
I linked the tables. Then renamed them to what they were. But I still get the errors "This recordset is not updateable."
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 16500807
I also get "Cannot update. Database ot object is read-only"

Accepted Solution

raopsn earned 1000 total points
ID: 16500845
All the linked tables should have a PRIMARY KEY in the SQL Server, otherwise the recordset or not updatable
LVL 36

Expert Comment

ID: 16500848
"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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question