Auto-Refresh ODBC Tables

[SkyCraw]

Hello, me again

Hoping this one isn't too tricky, just need to know how in VB I can refresh all my linked ODBC tables within my "Main Menu" form's OnOpen event.

Any assistance is always welcome!

 

[pr2-eugin]

I think it should be either adodbRS.Refresh or adodbRS.Requery or both.

 

[SkyCraw]

Is the adodbRS reference exclusive to Access 2010? We're currently running 2007 and, when I test this, I get an "object required" error (run-time 424).

Is there anything additional I would need to declare first?

 

[pr2-eugin]

adodbRS would have to be declared, Yes. The name I used is just a random name. You have to use the Recordset Object you have declared.

 

[SkyCraw]

After a bit of searching, I eventually went with the following code found on this forum post

Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Set db = CurrentDb
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
Set db = Nothing

I appreciate all the help though, pr2!