Connect property of linked ODBC table

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.

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

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.