VBA Excel get data from Access database Error

Question

Hi i have problem to ran VBA code in excel to get data from access get Error code runtime error: -2147217900 "Invalid SQL statement when check debug have problem on code rst.Open "Production Result", con when i change another table in same data base file not error. rst.Open "Production cost", con

Sub getdata()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\C:Mydata\database.accdb;")
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "Production Result", con
    Dim r As Long
    r = 3
    rst.MoveFirst
    Do While Not rst.EOF
        Sheets("sheet1").Cells(r, 3).Value = rst.Fields("lot_no").Value
        r = r + 1
        rst.MoveNext
    Loop
End Sub

Hope this fixes my issue

Answer 1

[Production Result] works. Note "Production cost" didn't work either for me but [Production cost] did.

Sub getdata()

    Const DB = "\\C:Mydata\database.accdb;"
    Const SQL = "SELECT lot_no FROM [Production Result]"
    
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DB)
    With Sheets("Sheet1").Range("C3")
        .CopyFromRecordset con.Execute(SQL)
    End With
    
End Sub