Set TableDef for multiple tables

[dj59]

I have the following function for tableDef that is working, but I don't want to have to repeat the code for each table. Can you tell me know to do this to include all the tables without repeating code?

Function ShowConnectInfo(strConnect_ss)
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb()
 
[COLOR=darkred] Set tdf = dbs.TableDefs("ss_person")[/COLOR]
[COLOR=darkred] tdf.Connect = strConnect_ss[/COLOR]
[COLOR=darkred] tdf.RefreshLink[/COLOR]
 
[COLOR=darkred] Set tdf = dbs.TableDefs("ss_place")[/COLOR]
[COLOR=darkred] tdf.Connect = strConnect_ss[/COLOR]
[COLOR=darkred] tdf.RefreshLink[/COLOR]
  
  Set tdf = Nothing
  Set dbs = Nothing
End Function

 

[Twincam]

Use "for each":

The first time you run it, run it with a msgbox or a print just to check it doesn't contain anything you don't expect!

[COLOR=darkred]  for each tdf in dbs.TableDefs[/COLOR]
[COLOR=#8b0000][/COLOR]    tdf.Connect...
  Next tdf

 

[dj59]

I tried this and get a connection "runtime error 3219, invalid operation"
It isn't recognizing the strConnect.
What needs to change? thanks.

Function ShowConnectInfo(strConnect_ssis)
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb()
 
  For Each tdf In dbs.TableDefs
    tdf.Connect = strConnect_ssis
       Debug.Print tdf.Connect
    tdf.RefreshLink
  Next tdf
 
  Set tdf = Nothing
  Set dbs = Nothing
End Function

 

[CJ_London]

you need to ignore system tables and any tables which are not linked

so only include tabledefs where the type is 6 or alternatively where the foreignname is not null

 

[dj59]

all the tables are linked.
I don't know what you mean by "type 6".

This works if I link each table individually with the code I posted previously. I just didn't want to have to write so much repeat code.

 

[CJ_London]

Sorry, forgot it was different in tabledefs

 For Each tdf In dbs.TableDefs
    [COLOR=red]if (tdf.Attributes And dbAttachedTable)<>0 then[/COLOR]
         tdf.Connect = strConnect_ssis
        Debug.Print tdf.Connect
        tdf.RefreshLink
     [COLOR=red]end if[/COLOR]
 Next tdf

 

[dj59]

Thank you.
This looks good. However it isn't quite working yet for me.
I think I need to set the 'tdf' to 'something' before the 'FOR' statement....otherwise it finds zero attributes / attached tables and goes no further.

something like....

Set tdf = dbs.TableDefs("ssis_person")

or

Set tdf = dbs.TableDefs()

....but neither of those work.

 

[CJ_London]

so long as you have these declared

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb()

it should work

not sure what

However it isn't quite working yet for me.

means

see post #2 on this link

http://p2p.wrox.com/access-vba/37117-finding-linked-tables.html

it may be you need

tdf.properties("Connect") = strConnect_ssis

or perhaps strConnect_ssis is not a valid connection string? see post #3 of the above link

 

[dj59]

I am wrong. This code works fine.

 For Each tdf In dbs.TableDefs
    [COLOR=red]if (tdf.Attributes And dbAttachedTable)<>0 then[/COLOR]
         tdf.Connect = strConnect_ssis
        Debug.Print tdf.Connect
        tdf.RefreshLink
     [COLOR=red]end if[/COLOR]
 Next tdf

I was getting confused by a different problem in my code.
Thank you.
I will mark this one resolved.