migrating backend database to SQL Server 2008 Express

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
more ▼

asked Apr 28 '11 at 09:24 AM in Default

EuniceH gravatar image

83 8 8 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

Dim i As Integer
i = 0
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("busline")
Do Until rs.EOF
    i = i + 1
MsgBox i

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.
more ▼

answered Apr 28 '11 at 10:48 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

for the data types, to specify Date and Time(0) rather than all dateime, I ended up using SSMA (SQL Server Migration Assistant) to create the tables in a preliminary database, then copied the script for creating the tables from SQL Server Mgmt Studio to notepad, then changed the datatype and simplified some of the constraints to use just getdate() rather than just the date portion of getdate(). I then created a new db in Mgmt Studio, ran the script, then did Database/Task/Import to load the data.
May 12 '11 at 06:50 AM EuniceH
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 28 '11 at 09:24 AM

Seen: 1247 times

Last Updated: Apr 28 '11 at 09:24 AM