VBA Excel get data from Access database Error

Question

Hi, I have a problem running the VBA code in excel to get data from access get Error code runtime error: -2147217900 "Invalid SQL statement when check to debug have a problem on code rst.Open "Production Result", con  when I change another table in same database 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

Answer 1

Try this:

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