Auto-Refresh ODBC Tables (1 Viewer)

S

SkyCraw

Registered User.

Local time

Today, 10:56

Joined

Oct 9, 2013

Messages

100

• Mar 10, 2014

• #1

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

Super Moderator

Local time

Today, 14:56

Joined

Nov 30, 2011

Messages

8,494

• Mar 10, 2014

• #2

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

 

S

SkyCraw

Registered User.

Local time

Today, 10:56

Joined

Oct 9, 2013

Messages

100

• Mar 10, 2014

• #3

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

Super Moderator

Local time

Today, 14:56

Joined

Nov 30, 2011

Messages

8,494

• Mar 10, 2014

• #4

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.

 

S

SkyCraw

Registered User.

Local time

Today, 10:56

Joined

Oct 9, 2013

Messages

100

• Mar 10, 2014

• #5

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

Code:Copy to clipboard

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!