By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

ODBC Connect issue

P: 4
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?)
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Set db = CurrentDb
  3.  
  4. 'Reset the connection property for all pass through queries for DB1 database only
  5.  
  6. For Each QueryDef In db.QueryDefs
  7.   If QueryDef.Connect Like "*DATABASE=DB1*" Then
  8.     QueryDef.Properties("Connect") = gblConnectionString
  9.     QueryDef.Properties.Refresh
  10.   End If
  11. Next
May 9 '07 #1
Share this Question
Share on Google+
2 Replies


P: 4
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...
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Set db = CurrentDb
  3.  
  4. Dim strTableName, strSourceTableName As String
  5. Dim objTableDef As TableDef
  6.  
  7. 'Reset the connection property for all pass through queries for DB1 database only
  8.  
  9. For Each QueryDef In db.QueryDefs
  10.  
  11.  If QueryDef.Properties("Connect") Like "*DATABASE=DB1*" Then
  12.   QueryDef.Properties("Connect") = gblConnectionString
  13.   QueryDef.Properties.Refresh
  14.  End If
  15.  
  16. Next
  17.  
  18. 'Reset connections for linked tables for DB1 database only
  19. For Each TableDef In db.TableDefs
  20.  If TableDef.Connect Like "*DATABASE=DB1*" Then
  21.   TableDef.Properties("Connect") = gblConnectionString
  22.   TableDef.Properties.Refresh
  23.  End If
  24. Next
  25.  
  26. End Function
May 10 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Hmm, not sure or the LIKE will work as expected, why not try for your tables:
Expand|Select|Wrap|Line Numbers
  1. For Each TableDef In db.TableDefs
  2.   If TableDef.Connect <> "" Then
  3.     TableDef.Properties("Connect") = gblConnectionString
  4.     TableDef.Properties.Refresh
  5.   End If
  6. Next
  7.  
Thus all tables with a connection will be reconnected and the Msys (read-only) tables skipped.

Nic;o)
May 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.