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
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.
If you are going to be doing this regularly;
Use this code:
Dim MyAppendString as String
MyAppendString = " (Code line 1) " & _
" (Code line 2) " & _
" (Last line) ";
docmd.runsql MyAppendString
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.
