How a storied procedure can read a table in MS Access?

Hi,

We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.

I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.

I've found info on how to add one record at a time but I need a more of a batch process approach.

Can you point me in the right direction?

Thanks,

Mary

You could use OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx) function for reading table in Ms Access:

Code Snippet

USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO

Also you could use OPENQUERY (http://msdn2.microsoft.com/en-us/library/ms188427.aspx) function for running queries at other databses, but you need to configure linked server first.|||

Thanks, I haven't gotten it quite working yet....took me a bit to turn on Ad Hoc Queries. I'm getting a could not be bound error on the table that is in SQL. If you have any ideas on how to fix that, sure would appreciate it.

-Mary

|||Never mind, I just forgot to alais the table...duh|||

It would be better to use a SQL Linked Server to the Excel file and use that instead of Access. What Access does behind the scenes in terms of query generation and calls to SQL Server should be outlawed.

Once the Excel sheet is linked, you can have a proc designed to do what you need with the data.