asked
can I still use DAO 3.6 in Access 2000 with a SQL Server 2008 Express backend?
I migrated the backend from Access 2000 to SQL Server 2008 Express. Now in my Access 2000 frontend, can I still use DAO 3.6 OpenRecordset - .AddNew and .Index and .Seek?
Like I wrote in the question you had about migrating Access2000 to SQL Server 2008 Express the other day([
http://ask.sqlservercentral.com/questions/36630/migrating-backend-database-to-sql-server-2008-express][1]): You should be careful with operations which forces Access to download a whole table and perform the data operations in Access rather than in SQL Server. Apart from that, I don't see why these oprations wouldn't work. But during such migration, there are tons of functionality to test. Why don't you try and see? [1]:
http://ask.sqlservercentral.com/questions/36630/migrating-backend-database-to-sql-server-2008-express
I should have said I'm getting an error when I try to just open a table using DAO, I have to change it to a select statement and use "dbOpenDynaset, dbSeeChanges". Then it seems .Index and .Seek and maybe .AddNew don't work. I can change the .AddNew to an INSERT statement, but then what about ".Bookmark = .LastModified", how do I get the primary key id from the insert I just did? Do I trust that noone else is adding a record to that table at the moment and just do "SELECT * FROM tblUsers WHERE UserID in (SELECT MAX(UserID) FROM tblUsers)"? Thanks very much for your help.
What error are you getting when you do something like **CurrentDB.OpenReocrdset("tablename")**?
Set rst = gdbTables.OpenRecordset("tblUsers", dbOpenTable, dbSeeChanges) get Error 3219:Invalid Operation Set rst = gdbTables.OpenRecordset("tblUsers", , dbSeeChanges) get no error With rst .Index = "PrimaryKey" error 3251: Operation is not supported for this type of object. I have learned so far that for DAO with SQL Server backend, that you need to use OpenDynaset and Select statements, then you can't use .Index and .Seek, but you can use .AddNew and .Bookmark.
@EuniceH - To get the primary key back is not simple. However, if you are talking about just an IDENTITY column (whether it is the primary key or not), then you should run `SELECT scope_identity()` in order to retrieve that value...
