Pass parameters for filename and spreadsheet name to SQL Server SELECT with OpenRowset
Posted on 2014-02-18
Several part question, part 1
I would like to pass parameters for the filename and spreadsheet name into this SQL Statement in a T-SQL stored procedure, from Access.
SELECT * INTO #AccountLedger
'EXCEL 12.0 xml;HDR=YES;DATABASE=S:\Acct_Ledger\December Net.xlsx',
'SELECT * FROM [Sheet1$]')
So, what I need is:
1. the syntax for the Access side to call the SP and pass the appropriate values, and
2. the syntax for the Stored procedure to receive the parameters and perform the upload to the temp table.
Once I get the data into #AccountLedger, I'll have some other steps to perform to massage the data before it gets pushed into tbl_Account_Ledger, but this is the critical part of that equation.
If I can simply call this stored procedure from Access and pass in the filename and sheetname, I'll be on my way. Actually, I might also need to pass in the parameters for the ACE version and the Excel file format, since I've got users that will be running this from a variety of Windows / Office combinations.