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

Connect property of linked ODBC table

P: n/a
Hi

I am trying to refresh table links at startup in an Access 2002
application. The following code is based on a number of articles
posted in this group but this does not seem to work:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs(i).Properties("Connect") =
constODBC_ConnectString
CurrentDb.TableDefs(i).Properties.Refresh
CurrentDb.TableDefs(i).RefreshLink

MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===

I also tried:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs(i).Connect = constODBC_ConnectString
CurrentDb.TableDefs(i).RefreshLink

MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===
constODBC_ConnectString is a global string variable containing a valid
connect string.

No errors are generated but the Connect property does not get updated
in either case. The original (manually linked) connect property is
maintained.

Please help.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi, try my tool : http://www.logicielappui.com/tips/Ac...ginMDB_SQL.zip

Robert Simard
Logipro
http://www.logicielappui.com/tips (French Only)
<co************@justice.vic.gov.au> a écrit dans le message de news:
11**********************@g47g2000cwa.googlegroups. com...
Hi

I am trying to refresh table links at startup in an Access 2002
application. The following code is based on a number of articles
posted in this group but this does not seem to work:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs(i).Properties("Connect") =
constODBC_ConnectString
CurrentDb.TableDefs(i).Properties.Refresh
CurrentDb.TableDefs(i).RefreshLink

MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===

I also tried:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs(i).Connect = constODBC_ConnectString
CurrentDb.TableDefs(i).RefreshLink

MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===
constODBC_ConnectString is a global string variable containing a valid
connect string.

No errors are generated but the Connect property does not get updated
in either case. The original (manually linked) connect property is
maintained.

Please help.

Nov 13 '05 #2

P: n/a
I use the following code, which deletes the existing tabledefs and
creates/appends new ones.

Bill E.
Hollywood, FL
-------------------------------------------------------------
Public Const strConnect= "Set your connection string here"

Function SetConnections()
On Error GoTo Trapper
Dim strTableName, strSourceTableName As String
Dim objTableDef As TableDef

'Reset the connection property for all pass through queries
For Each QueryDef In CurrentDb.QueryDefs
If QueryDef.Connect <> "" Then
QueryDef.Connect = "ODBC;" & strConnect
End If
Next

'Reset connections for linked tables
For Each TableDef In CurrentDb.TableDefs
If TableDef.Connect <> "" Then
'Get the table name and source table name
strTableName = TableDef.Name
strSourceTableName = TableDef.SourceTableName

'Recreate the tabledef
Set objTableDef = New TableDef
With objTableDef
.Name = strTableName & "Temp"
.SourceTableName = strSourceTableName
.Connect = "ODBC;" & strConnect
End With
CurrentDb.TableDefs.Append objTableDef

'If the new tabledef was successfully created, delete the
old tabledef
CurrentDb.TableDefs.Delete strTableName

'Change the name of the new tabledef to that of the old
tabledef
CurrentDb.TableDefs(strTableName & "Temp").Name =
strTableName

End If
Next
Set objTableDef = Nothing
Exit Function
Trapper:
MsgBox "Error setting connections to SQL Server Database"
Exit Function
End Function

Nov 13 '05 #3

P: n/a
It won't work because of your use of Currentdb.
===CODE SNIPPET START===
Dim loDb as DAO.Database

Set lodb = Currentdb

For i = 0 To lodb.TableDefs.Count - 1

If Left(lodb.TableDefs(i).NAME, 3) = "tbl" Then

lodb.TableDefs(i).Properties("Connect") = constODBC_ConnectString
lodb.TableDefs(i).Properties.Refresh
lodb.TableDefs(i).RefreshLink

MsgBox lodb.TableDefs(i).NAME & vbCrLf & vbCrLf &
lodb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===

Terry Kreft

<co************@justice.vic.gov.au> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Hi

I am trying to refresh table links at startup in an Access 2002
application. The following code is based on a number of articles
posted in this group but this does not seem to work:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs(i).Properties("Connect") =
constODBC_ConnectString
CurrentDb.TableDefs(i).Properties.Refresh
CurrentDb.TableDefs(i).RefreshLink

MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===

I also tried:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs(i).Connect = constODBC_ConnectString
CurrentDb.TableDefs(i).RefreshLink

MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect

End If

Next i
===CODE SNIPPET END===
constODBC_ConnectString is a global string variable containing a valid
connect string.

No errors are generated but the Connect property does not get updated
in either case. The original (manually linked) connect property is
maintained.

Please help.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.