473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Connect property of linked ODBC table

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
3 11737
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: James Bird | last post by:
Hello In order to perform a hetrogeneous join (I think that's what they're called) between MySQL and another database, I've created an Access database containing linked tables from each...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
20
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about...
3
by: Fabian Knopf | last post by:
Hi friends, i have a unix machine PC1 where a database is running ( IBM DB2 V8.1 ) . Then i have another machine PC2 i installed there also ( IBM DB2 V8.1 ). On PC2 i installed unixODBC. To...
2
by: jmev7 | last post by:
Any way to avoid having to manually enter my user name & password for the Oracle login box? I normally run a query on an attached Oracle table and have to enter the un/pw before the query will run....
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
7
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
0
by: Cunfshon | last post by:
I wrote about this one a while ago, and got a few responces, but nothing resolved the issue. Figured I'd give everyone one more crack at it. I have an established MS Access database using linked...
2
by: harrysdu | last post by:
May be someone has asked similar question here. If so, please point me to the link. one of our Access application has many linked tables. For testing purpose, I would like get it connected to the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.