Run Time Error --"Syntax Error in From Clause"

Hello Experts,

I've created a script to insert data into SQL Server from an Excel workbook.  I'm getting an error, "Run-Time error '-2147217900(80040e14)': Syntax error in FROM clause."

This error occurs when the recordset is attempting to open.  Am I doing something wrong with the rsdata.open() pararmeters?

(Code Attached)
Dim cnn As ADODB.Connection
Dim objCommand As ADODB.Command
Dim canConnect As Boolean
Dim strCnn As String
Dim rsData As ADODB.Recordset
Dim strSQL As String



Public Sub Export()

Set cnn = New ADODB.Connection

strCnn = "Provider=sqloledb; Data Source=devhybrid2.db.12345.hostedresource.com;Initial Catalog=devhybrid2;Uid=userid; Password=pw;"
cnn.ConnectionString = strCnn
cnn.Open (strCnn)

If cnn.State = adStateOpen Then
    canConnect = True
   
End If

MsgBox canConnect


'Create the connection string to Excel
strXLCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\XLHybrid.xlsm; Extended Properties=Excel 12.0;"
'Create the command object for Excel
Set objCommand = New ADODB.Command
Set rsData = New ADODB.Recordset

'Open the recordset - This is where the code dies
rsData.Open strXLCnn, strXLCnn, adOpenStatic, adLockOptimistic, adCmdTable
'cnn.Open strXLCnn
strSQL = "INSERT * INTO SalesOrders FROM OPENDATASOURCE (strXLCnn[SalesOrders$])"
objCommand.CommandText = strSQL
objCommand.ActiveConnection = strXLCnn







'Verify that the Recordset contains data
If Not rsData.EOF Then
'Loop until we reach the end of the Recordset
Do While Not rsData.EOF
'Load the SQL string into the Command object


Debug.Print rsData.Fields(0).Value
'Move to the next record
rsData.MoveNext
Loop

Else
    MsgBox "Error, no records returned.", vbCritical
End If


'Error checking
ErrorExit:
Set cnn = Nothing
Set rsData = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Description, vbCritical





If CBool(cnn.State And adStateOpen) Then


Set cnn = Nothing
Set rsData = Nothing

cnn.Close

End If

End Sub

Open in new window

brohjoeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Try replace
INSERT * INTO
to
SELECT * INTO

Raj
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Instead of "INSERT * INTO SalesOrders FROM OPENDATASOURCE (strXLCnn[SalesOrders$])", use the below constructs

-- INSERT into SalesOrders table already created
INSERT INTO SalesOrders (columns_list)
SELECT * FROM OPENDATASOURCE (strXLCnn[SalesOrders$])

-- INSERT into SalesOrders table by creating it
SELECT * INTO SalesOrders FROM OPENDATASOURCE (strXLCnn[SalesOrders$])
brohjoeAuthor Commented:
Ok, I changed it to 'SELECT * INTO......' but I'm still getting the same error.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If the table already exists as I mentioned earlier, it would error out..
Try the other syntax provided by me above..
brohjoeAuthor Commented:
Look at where you have, 'INSERT INTO SalesOrders (columns_list)'.  This implies you're inserting one worksheet.  But I want to insert the entire workbook.  Do I have to list each column individually?  Is there VBA code that will insert the entire workbook , or even a worksheet without listing each column individually?  There has to be a more efficient method.
Brain2000Commented:
I see a number of things wrong.  For one, you're not running the SELECT or INSERT statement that is in question.  While you're populating a command object, it is not being opened.  Also, you need to specify the table when opening the Excel recordset as the first parameter, not the connection string twice.

i.e.
rsData.Open "SalesOrders$", strXLCnn, adOpenStatic, adLockOptimistic, adCmdTable

Next, you are using OPENDATASOURCE, but I believe this only works if the Excel spreadsheet is opened from the server, not the client.  Also, if you are going to open it from the server, you need to pass the contents of strXLCnn, not literally "strXLCnn".

If you are copying the Excel spreadsheet from the client to the SQL server, I believe you will need to open both recordsets with two separate select statements, then loop through all the source records, looping through all source fields for each record, copying each one from the source to the destination recordset, calling AddNew() and Update() in the appropriate places.
brohjoeAuthor Commented:
When you say, "open both recordsets," what are you saying?  Once you have a  connnection open to SQL server, we want to just transfer data from Excel to SQL Server.  There is no recordset data in SQL Server at this time, just empty fields.

Could you demostrate what you're proposing in code?
Brain2000Commented:
Transferring data from Excel to SQL server can be done in several ways.  In one scenario, the Excel spreadsheet lives on the server.  In that case, you can do an INSERT INTO xx SELECT * FROM OPENDATASOURCE(...) statement and be done with it.  In another scenario, the Excel file exists on the client.  Unless the SQL server is connecting to it across the network, I believe it has to be copied manually using two recordsets.

In this scenario, I'm getting the feeling that the Excel spreadsheet is on the client and not the server.  That could be done similar to this: (note, the table would have to already exist in the SQL server.  If it doesn't, you'll need to create it manually or loop through the Excel fields and assemble a "CREATE TABLE" statement)


Dim conn_excel As New ADODB.Connection
Dim conn_sql as New ADODB.Connection

Dim rs_excel As New ADODB.Recordset
Dim rs_sql As New ADODB.Recordset

conn_sql.Open("Provider=sqloledb; Data Source=devhybrid2.db.12345.hostedresource.com;Initial Catalog=devhybrid2;Uid=userid; Password=pw;")
conn_excel.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\XLHybrid.xlsm; Extended Properties=Excel 12.0;")

rs_sql.Open("SELECT * FROM SalesOrders WHERE 0=1", conn_sql, adOpenDynamic, adLockOptimistic)
rs.excel.Open("SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable)

Do While Not rs_excel.EOF
  rs_sql.AddNew()
  For i As Integer = 0 To rs_excel.Fields.Count - 1
    rs_sql.Fields(rs_excel.Fields(i).Name).Value = rs_excel.Fields(i).Value
  Next
  rs_sql.Update()
  rs_excel.MoveNext()
Loop

rs_excel.Close()
rs_sql.Close()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brain2000Commented:
Typo, put a period instead of an underscore:

rs.excel.Open("SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable)

should be:

rs_excel.Open("SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable)
brohjoeAuthor Commented:
Ok, I'll try it.  Get right back to you Brain.
brohjoeAuthor Commented:
You mean put an underscore, not a period.
Brain2000Commented:
Yes, I meant that I put a period.  It'll be obvious though, it won't compile if it's wrong.
brohjoeAuthor Commented:
rs_sql.Open("SELECT * FROM SalesOrders WHERE 0=1", conn_sql, adOpenDynamic, adLockOptimistic)

causes a syntax error.  I"m attempting to debug.
Brain2000Commented:
That will happen if that table doesn't exist yet.  For now, create it manually.
brohjoeAuthor Commented:
Don't you need a command object?
Brain2000Commented:
Nah, not for this.  A command object is used if you want to parameterize your queries in an ad-hoc where clause.
brohjoeAuthor Commented:
The SalesOrders table exists.
Brain2000Commented:
Ok, let's look at the error.  Syntax errors should be highlighted by Visual Studio.  When you highlight over it, what is the actual error it tells you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.