Import Excel Data to SQL Server

[ryu]

Not sure if I am posting in the right forum but I really need help.

I am trying to import data from Excel to an Access database using SQ
functions. I keep getting the "FROM clause syntax error". I matched m
output with the requirement on MS website and am pretty sure I have th
syntax right. Can someone help? Thanks.

Here is my code.

strSQL = "SELECT * INTO [" & sheetName & "] FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:\clients\CTim\test2.xls;', " & _
"'SELECT * FROM [" & sheetName & "$]')"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecord

 

[Joel]

There is a single quote in the wrong place. I also think (not sure) you need
each SELECT on its own line. Adding the vbCRLF should accomplish this. If
it doesn't work try eliminating one of the SELECT statements and see if it
works.

strSQL = "SELECT * INTO [" & sheetName & "] FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:\clients\CTim\test2.xls;', " & vbcrlf & _
"SELECT * FROM '[" & sheetName & "$]')"