I got an error message when I am trying a select Query to import a table from Ms Acce

[Yogendra kul]

Hi:

I got an error message when I am trying a select Query to import a table from Ms Access Database (.MDB File) in SQL Server 2005.i am using SQL Server 2005. The query I am testing is as below:

SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet. OLEDB.4.0',
'Data Source="d:\Test .mdb";pwd=XXXX' )...Employee

The error message:
OLE DB provider "Microsoft.Jet. OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet. OLEDB.4.0" for linked server "(null)".

What could be the problem? Thanks.

[Kwolma]

You might find this easier if you put in an odbc source. Go to control panel, administration and then click on ODBC. From there choose System DSN and specify your settings via the add function. You can select SQL Server for 2005 and newer sql versions would use something like SQL Server Native Client. Easiest to name the odbc source the same as the sql server database name.

Once you do that, within Access you can choose to import/link and choose the option for odbc and then select the odbc source you created. You will be prompted for the password and can choose the table to work with. SQL will usually put dbo. in front of the file name and you can rename it without that.

Then the table is just another linked table in your database.

If you then want to create a copy of the table, you can build a make query over it.