SYNTAX error in vba upload in access database

Question

I am trying to update few fields in an Access Database from an Excel file through vba. The field formats are identical. However, I am facing an error message with following text:

run time error (syntax error in FROM clause)

following is my code:


Dim Wb As Workbook
Dim Ws1, Ws2 As Worksheet
Dim CN As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim i As Long
Dim mySQLst As String
Dim IDd As Long
Dim dbPath
Set Wb = ThisWorkbook
Set Ws1 = Wb.Sheets("Sheet1")


dbPath = Ws1.Range("V1").Value
Set CN = New ADODB.Connection
CN.Open "Provider=microsoft.ace.oledb.12.0;data source=" & dbPath

Set Rst = New ADODB.Recordset

For i = 2 To Ws1.Range("A" & Rows.Count).End(xlUp).Row

    IDd = Ws1.Cells(i, 1).Value
    mySQLst = "SELECT * FROM Test_Table1 WHERE (((Test_Table1.ID1)=16));"

    Rst.Open mySQLst, CN, adOpenDynamic, adLockOptimistic, Options:=adCmdTable

        While Not Rst.EOF
            Rst!TASK_STATUS = Ws1.Cells(i, 13).Value
            Rst!TASK_COMPLETED_DATE = Ws1.Cells(i, 14).Value
            Rst!RESPONSE = Ws1.Cells(i, 15).Value
        Rst.MoveNext
        Wend
Next i

Rst.Close
Set Rst = Nothing
CN.Close
Set CN = Nothing

End Sub

I have my activeX library 2.5 active from reference.

Thanks

I don't see anything wrong with SQL. However, loop code does not make sense. It is editing the same set of records in each iteration of range. — June7
– June7, Commented Feb 2, 2020 at 1:14
Yes, I was testing the code which I was to apply for a larger dataset. I have a dynamic range for the ID1 (I'd put 16 for a test) — Souvik Hazra
– Souvik Hazra, Commented Feb 4, 2020 at 22:47

trevor · Accepted Answer · 2020-02-03 03:41:15Z

2

You have a number of issues with your code: Once you fix that error you will see a bunch more.

To fix the error you have asked about please try using Named fields in your recordset:
eg:

"SELECT TASK_STATUS, TASK_COMPLETED_DATE, RESPONSE FROM Test_Table1 WHERE Test_Table1.ID1=16;"
If you have a column in your spreadsheet containing ID1 value, you need to replace the "16" in the select statement with that cell each time you loop through.
remove the rst.movenext - you only want to update one record.
change the While Not Rst.EOF to IF Not Rst.EOF (update if there is a record, not loop)

Things will work much better for you after these changes.

answered Feb 3, 2020 at 3:41

trevor

2673 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Souvik Hazra Over a year ago

Thanks Trevor for your detailed suggestion. I will apply the changes. However, what gave me immediate solution for the error that I was receiving (Syntax error in From clause) when I removed the following code: Options:=adcmdtable while opening the recordset. Thanks Again!

Collectives™ on Stack Overflow

SYNTAX error in vba upload in access database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related