Access 2007 on 64-bit server and SQL 08 Express on 32-bit server - which version of product do I use?
Category: sql server dataaccess
DaytonITMgr on Wed, 10 Apr 2013 18:37:20
I have a small number of small Access databases (split into front-end/back-end) and would like to move the back-end databases to SQL. There are both Access 2003 and 2007 databases. I have 3 servers virtualized on a machine running VMware: the file server on which the Access databases reside and the SQL server to which I’d like to move the back-end database are both running Server 2003 R2 Standard Service Pack 2 (32-bit). I have SQL 08 Express installed on the SQL server. Users access the databases via RDP to a 2008 Windows Server Enterprise without Hyper-V Service Pack 2.
I started with a 2007 back end database and in order to get the data into SQL I had to Save As to Access 2003 (from .accdb to .mdb) to use the Import and Export Data (32-bit) tool. I have created and deleted it several times because I am having trouble accessing it to link it to my front-end database. Also, since the owner of the db seems to matter – as far as providing access - and since my SQL server is also a backup domain controller, it appears that I need to have the db admin login have system administrator privileges.
But I still can’t seem to figure out how to set up the DSN properly. I read through the forums and tried logging into the terminal server as admin and using odbcad32.exe. But do I use the 64-bit or the 32-bit version?
Also, the only driver for SQL in the DSN list is SQLSRV32.DLL 6.00.6002.18005 4/11/2009. Is this the right one? If not, where do I get the right one (e.g. native client.)
Allen Li - MSFT on Fri, 12 Apr 2013 05:42:12
Please install Microsoft SQL Server Native Client first, and then please refer to the following document to import data from Access 2003 to SQL Server 2008.
Microsoft SQL Server 2008 R2 Native Client (can be used with SQL Server 2008):
Transferring Data from MS Access 2003 to SQL Server 2008
DaytonITMgr on Wed, 01 May 2013 18:37:46
Thank you for the links. The download link was useful.
Do you know how to import from an Access 2007 database?
Once the Native Client is installed on the SQL server it will be available for creating the DSN for my front-end Access databases?