import data from Access to SQL2005 64bit problem

[Tarvirdi]

Dear friends,
I have a table in an access file and want to import it to a MsSql database.
I have SQL2005 64bit version.
to do import, I tried to create a LinkedServer to access file but failed
because have not jetConnect. As I knew I should install MDAC. I tried to
find MDAC for x64 installation, but failed
-Is there any other way to import data from access table to MSSQL?
-Where can I find and download required jet connect(mdac) for x64 mssql?
Thanks
Tarvirdi

 

[Sylvain Lafontaine]

You can't do it directly: at the present time, all ODBC and OLEDB driver for
Jet/Access are 32 bit driver and you can't use any 32 bit ODBC or OLEDB
driver from the 64 bit world.

However, what you could do would be to install SQL-Server 2005 Express -
it's a 32 bit application - and use it as a proxy between JET and your 64
bit SQL-Server. For an exemple on how to do this for Excel (about the same
thing as for Access) - including how to configure the security (surface
area) for Express - see the end of the following article:

http://blogs.msdn.com/sqldev/archive/2008/10/22/msjet-4-0-in-64-bit-environment.aspx

For a more complete example using ad-hoc queries, see:
http://gorm-braarvig.blogspot.com/2005_11_01_archive.html

 

[Paul Shapiro]

I've imported to a 64-bit SQL Server using SSIS (Sql Server Integration
Services- the successor to DTS). I do seem to remember there was a
configuration issue for which I found a solution in a google search, so try
searching. Maybe I had to set SSIS to run in 32-bit mode, or specifically
use a 32-bit data driver? I can't remember the details.

You can also create an Access ADP working with the 64-bit SQL Server db and
use Access to import from the mdb. That's very simple.

 

[Sylvain Lafontaine]

Yes, of course, you can also use any external program running under the 32
bit mode to act as a messenger between the Access file and the 64 bit
SQL-Server. In fact, the easiest way would be probably to simply use Access
itself to directly export the table toward the SQL-Server; using either a
MDB/ACCDB file or an ADP project.

Unlike JET, the ODBC and OLEDB drivers for SQL-Server can happily use the
TCP/IP protocol; so the frontier between the 32 bit and the 64 bit worlds is
not a barrier at all to them.