select from access database file and insert to sql

Question

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 are going to be doing this regularly; 1.Create an append query in design view that does what you want it to do, including the criteria by which to filter the query results. 2.View the query in SQL view. 3. Copy the SQL Text 4. Create a button on your form. Go to the properties window, under the event tab, and select the "on click" event. Click the ellipsis "..." and open the code.

Use this code:

---

Dim MyAppendString as String

MyAppendString = " (Code line 1) " & _ " (Code line 2) " & _ " (Last line) ";

docmd.runsql MyAppendString

**************** End of code

Every time you click the button, it will execute the append query, hardcoded with the criteria you selected.

Let me know if you stick on any of these points. I'll send you more detailed instructions.

Answer 2

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.