Modifying the Connect property of all TableDefs in a collection. 2

[DSect]

Hello - I am trying to modify the Connect property of every table in an Access DB w/ linked tables.

I need to modify the connect string to represent a change in server name, but when I tried a simple test, I receive an error: "Invalid Operation"

Here's my test procedure:

Sub DefConSet()
Dim strCon As String
strCon = "TESTING"
Dim db As Database
Dim td As TableDef
Set db = CurrentDb
For Each td In db.TableDefs
'Debug.Print td.Connect
td.Connect = strCon
Next td
Set db = Nothing
End Sub

Before I did this test, I tried doing it by using the previous connect property value, slightly modified, in the strCon variable. I received the same error, so I don't think it's the fact that I am trying to set the connect property to "TESTING". I maybe wrong?

Anyways, thanks in advance for the help.

I am hoping it is possible to modify the connect properties of all tabledefs in the TableDefs collection using some sort of loop, but I am very new to all this stuff!

 

[KenReay]

Hi

I think the problem is that you are trying to set the connect string of ALL tables. In all Db's theer are System tables (not normally visible to the user, depends on the settings in tools \ options). You cannot set the connection string of these tables, indeed you should alter them in any way at your peril, so try something like

Public Sub ReAttach(strAttachedFilePath As String)
Dim db As DAO.Database
Dim tdf As TableDef
'
DoCmd.Hourglass True
Set db = CurrentDb()
'
For Each tdf In db.TableDefs
If Len(Trim(Nz(tdf.Connect, "&quot & "&quot) > 0 Then
tdf.Connect = ";Database=" & strAttachedFilePath
tdf.RefreshLink
End If
Next
DoCmd.Hourglass False
Set tdf = Nothing
Set db = Nothing
End Sub Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[VicM]

DSect:

KenReay gave you code that should do the trick. One of the things I noticed in your initial code is that you forgot the RefreshLink method.

However, KenReay is also correct about the system tables. But I believe they always reside in the frontend. You can try this out yourself with the following sub run from a module:

Sub viewtblinfo()
Dim db As Database, tbl As TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs
Debug.Print tbl.Name & ", " & tbl.Connect
Next
End Sub

And another 2 cents worth. Since all system tables begin with MSys, you can substitute If Len(Trim(Nz(tdf.Connect, "&quot & "&quot) > 0 Then in KenReay's code, with If InStr(tdf.Name,"MSys&quot > 0 Then . (Two less functions to be evaluated, should run faster.)

From experience, if you are relinking to a backend db on a WAN server, or even a LAN server, the relink process can take some time. So I wouldn't suggest doing it on a regular basis because the user might get frustrated waiting. Found out the hard way.

Vic

 

[KenReay]

Hi

You will find the System tables in ANY jet mdb no matter if it is a backend of a front end.

Other database systems (eg SQl Server, Oracle etc) also have System Tables, but not with same names etc as those found in Jet.

Using If Len(Trim(Nz(tdf.Connect, "&quot & "&quot) > 0 Then allows for having 'local' jet tables in the front end, which people (including me) often do.

Another problem with using MSys to identiy System tables, if you are working on other peoples databases, is that programmers often use the trick of beginning their table names with MSys, to cause them to be hidden in the databse window.

I agree with comments relating to the speed (or lack thereof), which means you propbably do not want to do this as a matter of routine, but only if a broken link is found.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[VicM]

KenReay:

You are correct about system tables residing in any db. But no system tables should be linked.

I was unaware that some programmers would use the MSys to hide their tables. I would consider that potentially 'dangerous'. Take the scenario whereby MS upgrades Access with a patch which includes creating another system table (to help speed things along?). Probabilities may be low, but the name MS chooses could possibly be identical to what a programmer was using in his/her database.

I generally prefer to use the hidden attribute of a table to do the hiding. (Yes, I know that a savvy user can unhide the table if he/she wishes. But if the user is savvy enough he/she can also printout a list of all table names and discover the pseudo 'system' tables as well.)

However, KenReay, you make a valid point. When I submitted my '2 cents', I was not considering other dbs, eg Oracle, SQL, etc. I must keep that in mind.

Vic

 

[KenReay]

Hi

Yes, I agree with your points, a word of warning about the hide table property, in Access97 there was a bug which caused tables with the hidden attribute set to be deleted when a databsae was compacted, this may have been fixed in later versions.

Regards Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DSect]

Hey All! Thanks for the input. I knew there were system tables in the collection, but I didn't even think about them in terms of my operation.

So in short:

1. You can't alter connect strings of sys tables.

2. Sys tables should have a Null (or Zero length after an Nz) connect string, but so will "local" tables and if not, most SYS tables will begin with a prefix that helps to ID for exclusion.

I'll use the MSYS (or whatever my systables are prefixed with) name to exclude the system tables from the loop.

Thanks!

(ps - I never knew about the Nz function! That will come in handy in the future for preventing goofiness in my expressions, I see!)

Oh yeah - Tek-Tips and it's members ROCK!

 

[DSect]

Bloops - I misstated a little of #2, but I do understand exactly what you mean. Wouldn't be the first time I've spouted some bad #2!