I was able to get the hard-coded select statement to work, but when I put in variables, I get an error: 'Must declare the scalar variable "@lcSQL"

declare @DB_in		nvarchar(200),
		@Table_in	nvarchar(50),
		@lcSQL		nvarchar(200);

SET @DB_in = N'C:\files\myTestDB.mdb'
SET @Table_in = N'Table_1'
SET @lcSQL = N'SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'','''+@DB_in+''';''admin'';'''','+@Table_in+');'

PRINT @lcSQL

exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC sp_executesql @lcSQL

go
exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;

The variable is declared, so why the error?? ... looking at the preview, the code segment does not interpret the single quotes correctly, but in SQL they are correct and the correct colors. :|

Thanks

>Hi Rollin,
>
>You can use Ad hoc Query on MSSQL: http://www.sqlservercentral.com/Forums/Topic334873-338-1.aspx#bm335131
>It's simply use {Microsoft Access Driver (*.mdb, *.accdb)} instead "Microsoft.ACE.OLEDB.12.0" if you need read *.accdb on 64 bit SQL
>
>

>SET @lcLKV='\\server\folder\any.accdb'
> SET @lcSQL=N'INSERT INTO #LKV '+
>    'SELECT *
>       FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq='+@lcLKV+';Uid=;Pwd='',
>                       ''SELECT * FROM [anytable] '')
>    '
>
> --PRINT @lcSQL
>
> EXEC sp_executesql @lcSQL
>

>
>MartinaJ
>
>>I need to automate the importation of Access tables into SQL Server. The Access database(s) are sent in on a regular schedule and I need to bring the data into SQL Server. I am not as familiar with bcp as I would like (I assume this would be the best tool to use). Any help on this would be appreciated.