Trying to update my query from Excel VBA

[Darren Smith]

This code I wrote is to update access queries. But it does not create a record set?
Please help to get the record set working I also think the strQuery is not right?

Sub UpdateJobCardMasterLink()

    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strQuery    As String
    Dim i           As Integer
    Dim ws          As Worksheet
    Dim LRow        As Long
           

    Application.ScreenUpdating = False
   

    AccessFile = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Access Files\Job Cards Inventory1.accdb"
   

    strQuery = "Job Card Master Linked Append"
   
    On Error Resume Next

    Set con = CreateObject("ADODB.connection")

    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0

    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
   
    On Error Resume Next

    Set rs = CreateObject("ADODB.Recordset")

    If Err.Number <> 0 Then

        Set rs = Nothing
        Set con = Nothing

        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0


    rs.CursorLocation = 3
    rs.CursorType = 1

    rs.Open strQuery, con
   

    If rs.EOF And rs.BOF Then

        rs.Close
        con.Close

        Set rs = Nothing
        Set con = Nothing

        Application.ScreenUpdating = True

        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If
   
    Set ws = ThisWorkbook.Worksheets("StockItems")

    LRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("A2:E" & LRow).CopyFromRecordset rs
 
    rs.Close
    con.Close

    Set rs = Nothing
    Set con = Nothing

    ws.Columns("A:E").AutoFit


    Application.ScreenUpdating = True



    MsgBox "All data were  successfully retrieved from the '" & strQuery & "' query!", vbInformation, "Done"

End Sub

 

[RayFrye]

TryL
Set rs = New ADODB.Recordset

 

[Darren Smith]

I think you are right with the above but could you help to try to run or activate a query from VBA Excel

   strQuery = "Job Card Master Linked Append"

This is not right but how can you change it to make it run

 

[RayFrye]

'Send SQL String to Open DB

Call OpenDB() 'ODBC open connect string

strQuery = "Select * from tablename"

rs.Open strQuery, ConnectStringFromOpenDB, adOpenStatic