ODBC Connect issue

[daft]

Hi guys

Following on from an issue a couple of years back, now archived:

http://www.thescripts.com/forum/thre...f.connect.html

I'm having the same problems.

I can update the Querydef.connect property programmatically without any problems, however if I try to update the Tabledef.connect property it won't do it, even though it runs through the code successfully.

When I try to manually update the connect column in MsysObjects it won't let me do it, gives a "Control Can't be Edited; It's Bound to Replication System Column Connect" message in the control bar, so I'm guessing this is related...

Any help on this would be appreciated.

FYI the code is below, and we do not use in-built Access database security on our Access dbs (could this be why it won't let me edit it through code?)

Dim db As DAO.Database
Set db = CurrentDb
 
'Reset the connection property for all pass through queries for DB1 database only
 
For Each QueryDef In db.QueryDefs
  If QueryDef.Connect Like "*DATABASE=DB1*" Then
    QueryDef.Properties("Connect") = gblConnectionString
    QueryDef.Properties.Refresh
  End If
Next

[daft]

OK I think I might be on to something here

"For Database objects, new Connection objects, linked tables, and TableDef objects not yet appended to a collection, this property setting is read/write. For QueryDef objects and base tables, this property is read-only."

This seems to suggest that because a querydef is opened then closed I can edit the connection, but because a connection is open to an ODBC linked table it is seen as a base table and therefore read-only... so maybe I need to close the connection before updating? Hmmm.

Also I missed a section of the code snippet... trying this one...

Dim db As DAO.Database
Set db = CurrentDb
 
Dim strTableName, strSourceTableName As String
Dim objTableDef As TableDef
 
'Reset the connection property for all pass through queries for DB1 database only
 
For Each QueryDef In db.QueryDefs
 
 If QueryDef.Properties("Connect") Like "*DATABASE=DB1*" Then
  QueryDef.Properties("Connect") = gblConnectionString
  QueryDef.Properties.Refresh
 End If
 
Next
 
'Reset connections for linked tables for DB1 database only
For Each TableDef In db.TableDefs
 If TableDef.Connect Like "*DATABASE=DB1*" Then
  TableDef.Properties("Connect") = gblConnectionString
  TableDef.Properties.Refresh
 End If
Next
 
End Function

[nico5038]

Hmm, not sure or the LIKE will work as expected, why not try for your tables:

For Each TableDef In db.TableDefs
  If TableDef.Connect <> "" Then
    TableDef.Properties("Connect") = gblConnectionString
    TableDef.Properties.Refresh
  End If
Next
 

Thus all tables with a connection will be reconnected and the Msys (read-only) tables skipped.

Nic;o)