loop through TableDefs? (DSNless connection) (1 Viewer)

[madEG]

Hello,

Trying to finally learn DSNless connections...

This works fine, to connect me to a single named table:

     '####### DSNless Connection #######
    Dim sConnect As String
    sConnect = "ODBC;DRIVER={sql server};DATABASE=PrioPlansDB3;SERVER=Alex;Trusted_Connection=Yes;"

    Dim tdf As DAO.TableDef
    Dim db As DAO.Database

    Set db = CurrentDb
    Set tdf = db.TableDefs("dbo_AppUsers")

    tdf.Connect = sConnect
    tdf.RefreshLink

    Set tdf = Nothing
    Set db = Nothing

...but when i wanted to loop through the various linked tables, connecting all of them, and try the below:

    '####### DSNless Connection #######
    Dim sConnect As String
    sConnect = "ODBC;DRIVER={sql server};DATABASE=PrioPlansDB3;SERVER=Alex;Trusted_Connection=Yes;"

    Dim tdf As DAO.TableDef
    Dim db As DAO.Database

    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        ' ignore system and temp tables
        If Not (tdf.Name Like "msys" Or tdf.Name Like "~*") Then
            Set tdf = tdf.Name
            tdf.Connect = sConnect
            tdf.RefreshLink
        End If
    Next

I get a "Compile error: Type Mismatch" on the open form event where this is being run from...

Can someone push me in the right direction? I thought this would work...?

Thanks!
-Matt G.

 

[madEG]

Doh!

removed:

Set tdf = tdf.Name

...and I'm good to go.

Thanks anyway!

 

[madEG]

Oh, and for the record, I had to edit the IF line to include an wildcard:

If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then

 

[arnelgp]

you add another test on your code:

If (Not (tdf.Name Like "msys" Or tdf.Name Like "~*")) And Instr(tdf.Connect,"DATABASE")>0 Then

in case there are local access table in your db, it will be excluded.

 

[madEG]

Thank you! You're absolutely right! - I had a 'Paste Errors" table created when messing with data and suddenly I couldn't through the line:

tdf.Connect = sConnect

without getting an error 3219...

It was the local table attempted to being connected to!

Did I already say thanks?