Compile Error with ADODB.Connection Object pararmeters

Hello Experts,

I'm attempting to load data from Excel on the client to SQL Server in the clouds.  I'm getting a "Compile Error Syntax error" on the line where 'rs_sql.Open' is executed. (see code).  The empty tables have been created on SQL Server and correspond with the columns in Excel.

Thank you for your help.

 
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

Sub loadSQLServer()

conn_sql.Open ("Provider=sqloledb; Data Source=database.hostedresource.com;Initial Catalog=intCat;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()

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?
 
cyberkiwiConnect With a Mentor Commented:
Hi there,

Not only that line but at least the next line as well has an extra "
0
 
Máté FarkasDatabase Developer and AdministratorCommented:

rs_sql.Open("SELECT * FROM SalesOrders WHERE 0=1", conn_sql, adOpenDynamic, adLockOptimistic)

Open in new window

0
 
cyberkiwiCommented:
Hi there,

If you amenable, I am open to splitting the points since agux3e did in fact solve the immediate issue on the line reported.
0
 
brohjoeAuthor Commented:
Ok, that sounds fair.
0
 
brohjoeAuthor Commented:
I've got another open question that hasnt been answered concerning the same code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.