Copying SQL Server query results into an Access 20

Question

Monthly, I need to get "new" data from our SQL Server business application into an Access 2010 database that contains all our "Management Reports". The Access database has a "staging table" that is to contain the raw data on which the reports are based.

I have no Access experience, but I suggested that we:

1. Write a query (stored proc?) on our SQL Server that returns the required raw data (...this bit was easy.)
2. At the end of each month, call the SQL Server stored proc from within Access 2010 (...click a button?)
3. Save the results of the stored proc into the staging table within Access.

But I'm finding it harder than I expected. I think I can get something ugly working using ADODB in a code-behind, looping through rows in a recordset one by one, and then setting column values one by one. But there must be a better way :)

• How should I go about getting SQL Server data from Access 2010? (ADODB? DAO? QueryDesigner? other?)

• Is there a "Insert Recordset Into Table" (or similar) mechanism that I can leverage?

Answer 1

Link the relevant sql server table or view to MS Access. Run a query against the linked table using MS Access syntax and update the staging table.

It is also possible to update an MS Access table using a connection string for SQL Server in-line in your query.

SELECT *
INTO   newtable
FROM   [odbc;filedsn=Z:\DSN\test.dsn].table1

Working from the SQL Server end, you can use MS Access as a linked server or run a query and update from there.

INSERT INTO
OPENDATASOURCE(
   'Microsoft.ACE.OLEDB.12.0', 'Data Source=z:\docs\test.accdb')...[table1] 
   ( atext )
SELECT atext FROM table1 WHERE id=2