Code:
Private Sub Command202_Click()
On Error GoTo Err_Command202_Click
Dim ExcelPath As String, sConnect As String, sql As String
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'browse to xls
ExcelPath = BrowseFile("Excel Workbook to Populate from This Disk", "xls")
'open connection to xls file
sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ExcelPath & ";HDR=Yes';"
Conn.Open sConnect
sql = "Select [File Name], Title, Album, Year, Comment, [Track #], Composer from [Sheet1$]"
rs.Open sql, Conn, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount
rs.MoveFirst
Do
Debug.Print rs(0)
rs(3) = "test"
rs.Update
rs.MoveNext
Loop Until rs.EOF
rs.Close
Conn.Close
Exit_Command202_Click:
Exit Sub
Err_Command202_Click:
Debug.Print Err.Description
Resume Exit_Command202_Click
End Sub
[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '(File Name=Pa_RaM001 AND Title=Pa_RaM002 AND Album=Pa_RaM003 AND Year=Pa_RaM004 AND Comment IS NULL AND Track #=Pa_RaM005 AND Composer IS NULL )'.
This isn't my sql and it does work ok for reading, but errors trying to update. Any help appreciated.