can i use DAO to connect to SQL server 2000?

Posted on 2006-04-20
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
    LVL 3

    Expert Comment

    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.
    LVL 3

    Assisted Solution

    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)
    LVL 5

    Author Comment

    I linked the tables. Then renamed them to what they were. But I still get the errors "This recordset is not updateable."
    LVL 5

    Author Comment

    I also get "Cannot update. Database ot object is read-only"
    LVL 9

    Accepted Solution

    All the linked tables should have a PRIMARY KEY in the SQL Server, otherwise the recordset or not updatable
    LVL 36

    Expert Comment

    "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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now