select from access database file and insert to sql Database

Question

Hi All, I have an access database file (test.mdb) and I need to write a stored procedure which will select some records from tblTest in test.mdb and insert them into tbsqlTest in my sql database . ==> I need a SP like this :

BEGIN
    select * into tblTest from [test.mdb].[tblTest]
    where (my condition)
END

Answer 1

If you're willing to permit Ad Hoc Distributed Queries on your SQL Server, you could use OPENDATASOURCE to retrieve data from an MDB file.

SELECT * INTO dbo.TestAccess FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\server\share\somefolder\scratchpad.mdb"')...MyTable;

Or after creating the destination table, you might prefer:

INSERT INTO dbo.TestAccess 
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\server\share\somefolder\scratchpad.mdb"')...MyTable;

In order to get those to run, I had to enable Ad Hoc Distributed Queries like this:

sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

I found configuration instructions on TechNet, but it didn't work for me until I added WITH OVERRIDE.

Edit: I added a sql-server tag to your question because I want to find out whether my suggestion is foolishly risky. Perhaps setting up the MDB as a linked server is a safer way to go here. I don't know.