Import Excel Data to SQL Server
 

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 SQL
functions. I keep getting the "FROM clause syntax error". I matched my
output with the requirement on MS website and am pretty sure I have the
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, adExecuteNoRecords


--
ryu
------------------------------------------------------------------------
ryu's Profile: http://www.thecodecage.com/forumz/member.php?userid=602
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122445

Import Excel Data to SQL Server
 

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 & "$]')"


"ryu" wrote:


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 SQL
functions. I keep getting the "FROM clause syntax error". I matched my
output with the requirement on MS website and am pretty sure I have the
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, adExecuteNoRecords


--
ryu
------------------------------------------------------------------------
ryu's Profile: http://www.thecodecage.com/forumz/member.php?userid=602
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122445