Trying to update my query from Excel VBA

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
Office Version
  1. 2019
Platform
  1. Windows
I think you are right with the above but could you help to try to run or activate a query from VBA Excel
VBA Code:
   strQuery = "Job Card Master Linked Append"
This is not right but how can you change it to make it run
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 2019
'Send SQL String to Open DB

Call OpenDB() 'ODBC open connect string

strQuery = "Select * from tablename"

rs.Open strQuery, ConnectStringFromOpenDB, adOpenStatic
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top