Hi ASK SQL, thanks for taking my questions.
I have an Access 2000 backend and many frontends. I want to migrate the Access 2000 backend to SQL Server 2008 Express 32 bit with Tools, using SQL Server Migration Assistant, which seems to create the constraints and indexes just fine. The only problem there is that I can't specify a datatype (date or time) for a specific column, it loads all date/time columns as datetime, which is fine for some not so good for others. If you have any suggestions here, good, otherwise I can work around this.
Real questions: In the Access 2000 frontends, do I need to change all my DAO 3.6 references to ADO? I have ADO 2.1 already checked but never used. My code currently does a DIM as DAO for every recordset. Should I use ADO 2.8, since all the computers are Windows XP? I heard that DAO doesn't work for SQL Server backend. I am also reading that ADO might not work either, might be read only, based on http://support.microsoft.com/kb/227053/EN-US and http://support.microsoft.com/kb/281998. If I switch to ADO, do I need to do SELECT statements all the time, instead of loading a mixture of tables and select statements? Some of my recordsets open as "Set grstLasts = gdbTables.OpenRecordset("tblLasts")" - just the table, not a select statement. I also have updates and deletes too to deal with. Could you give me one example of a proper OLEDB connection/recordset for a SELECT statement in VB in MS Access connecting to SQL Server Express 2008? I’m thinking to access by user=sa.
Thanks, Eunice Eunice Harris IISCC Eunice.Harris@iiscc.com
asked Apr 28, 2011 at 09:24 AM in Default
If you setup the tables in SQL Server as linked tables in the Access 2000 frontend, I'm pretty sure you can go on with DAO objects (though recordsets might behave a little differently in Access and SQL Server).
I'm using Access 2003, but I can't imagine the difference being that drastic between the two versions.
I've setup a few linked tables, and made an extremely basic test:
All it does is proving that a linked table SQL Server table can be accessed with DAO objects, nothing more. You might or might not run into very, very serious performance problems if you have queries that query big tables using Access specific functions (like the IIF function for example), because then Access will have to spool the whole table and apply the function, rather than just sending an SQL statement to SQL Server and have SQL Server do all the data access and filtering.
If you stick to DAO objects, you might have to rewrite some stored queries and change them into Pass Through Queries, so that SQL Server gets to do what it does best. On the other hand - if you decide to switch to ADO, you really won't have much help from Access at all, since ADO won't use linked server definitions, it will use its own Connection object and its own recordsets for accessing data.
answered Apr 28, 2011 at 10:48 AM