Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2017, 01:50 PM   #1
Royce
Access Developer
 
Join Date: Nov 2012
Posts: 95
Thanks: 8
Thanked 9 Times in 9 Posts
Royce is on a distinguished road
Angry Strange problem refreshing ODBC Connect

I have some code for refreshing links that I thought was working. I did make some minor modification to handle the case where the front end and back end names were different. Now for some reason the ODBC Connect strings are not getting refreshed, even on the tables where the names are the same.

The code, see below, renames the old table, creates a new table link, then deletes the original link. The code works, in that the tables are correctly linked. But when I use the Connect string later in other code it is the old string!

When I trace through the code I see where it changes the .Connect property, and newConnectionString has the correct value. But even after the db.TableDefs.Refresh line the table still has the old connection string!

What am I missing?

Code:
Public Sub RefreshODBCLinks(strTableName As String, newConnectionString As String, strSourceName As String, lngAttributes As Long)
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim originalname As String
    Dim tempname As String
    Dim sourcename As String
    Dim cleanName As String
    
    Dim i As Integer

    On Error GoTo Proc_Err

    Set db = CurrentDb
    

    ' Create new tables using the given DSN after moving the old ones '
   
    originalname = strTableName
    tempname = "~" & originalname & "~"
        
    ' Create the replacement table '
    db.TableDefs(originalname).Name = tempname
    cleanName = Replace(originalname, "~", vbNullString)
    Set tb = db.CreateTableDef(cleanName)
    
    If lngAttributes <> 0 Then
        tb.Attributes = lngAttributes
    End If
    tb.SourceTableName = strSourceName
    tb.Connect = newConnectionString
    
    db.TableDefs.Append tb
    
      
        
      Err = 0
      On Error Resume Next
      tb.RefreshLink         ' Relink the table.
      If Err <> 0 Then
        MsgBox "Unable to Relink table " & tb.Name & vbCrLf & vbCrLf & _
          "Some tables may have been relinked.  Database link condition is unknown."
        mbolRelink = False
        GoTo Proc_Exit
      End If
      
    ' delete the old table '
    DoCmd.DeleteObject acTable, tempname
    db.TableDefs.Refresh
    'Debug.Print "Refreshed ODBC table " & originalname
    
    

Proc_Exit:
   On Error Resume Next
   Set db = Nothing
   Exit Sub

Proc_Err:
  mbolRelink = False
  MsgBox "Error frmRelink function 'Relink' " & vbCrLf & vbCrLf & Error$
  Resume Proc_Exit

End Sub

Royce is offline   Reply With Quote
Old 05-16-2017, 10:50 PM   #2
Orthodox Dave
Home Developer
 
Orthodox Dave's Avatar
 
Join Date: Apr 2017
Location: London UK
Posts: 218
Thanks: 25
Thanked 50 Times in 49 Posts
Orthodox Dave will become famous soon enough
Re: Strange problem refreshing ODBC Connect

Hi Royce,

I have been successfully using code to relink my databases for years, without knowing how it worked (it was a copy and paste job!)

But now I know more about VBA I can understand it, and looking through it carefully, nowhere does it create a new table or delete the old one.

The crucial part of the code that makes the change is:

Code:
If [TableDef].Connect <> "" Then
[TableDef].Connect = ";DATABASE=" & NewBackPath
[TableDef].RefreshLink
End If
It has never let me down. There is a lot more in it (such as progress meters, and it loops through all the tables etc) but the crucial bit is above.
Orthodox Dave is offline   Reply With Quote
Old 05-18-2017, 09:34 AM   #3
Royce
Access Developer
 
Join Date: Nov 2012
Posts: 95
Thanks: 8
Thanked 9 Times in 9 Posts
Royce is on a distinguished road
Re: Strange problem refreshing ODBC Connect

In most cases what you show is all that is needed, and I've used that for years also.

But I have a development situation where we need to frequently relink between a backend Access database using Jet and a backend SQL Server via ODBC. Because of a quirks in how Jet handles the switch, and I don't remember the details, simply resetting the connection string does not work.

In my code, which has also been in use for years elsewhere, the essential lines are:

Code:
' Rename the old table
db.TableDefs(originalname).Name = tempname

' create a new table
Set tb = db.CreateTableDef(cleanName)
...
' Set the new connection string
tb.Connect = newConnectionStringdb.TableDefs.Append tb

..
' Delete the old table
DoCmd.DeleteObject acTable, tempname
I found the problem.

It turns out I'm hitting the Access Cached connection problem that I thought the above code fixed. The new connection string is to the same server and database, but other parameters are changed. As a result the db.Append tb line throws away the new connection string and reuses the original. To work around it, I have to open the relinking form BEFORE any of the existing connections are used. Then it does what is expected. From what I can tell, there is not a good solution.


Last edited by Royce; 05-18-2017 at 11:04 AM.
Royce is offline   Reply With Quote
The Following User Says Thank You to Royce For This Useful Post:
Orthodox Dave (05-19-2017)
Old 05-18-2017, 02:05 PM   #4
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Strange problem refreshing ODBC Connect

https://support.microsoft.com/en-gb/...uery-in-access
static is offline   Reply With Quote
The Following User Says Thank You to static For This Useful Post:
Orthodox Dave (05-19-2017)
Old 05-19-2017, 01:31 AM   #5
Orthodox Dave
Home Developer
 
Orthodox Dave's Avatar
 
Join Date: Apr 2017
Location: London UK
Posts: 218
Thanks: 25
Thanked 50 Times in 49 Posts
Orthodox Dave will become famous soon enough
Re: Strange problem refreshing ODBC Connect

Thanks Royce and Static. As I have only ever linked to other Access databases as the back end, this problem is new to me. If in future I need to link to SQL Server, I can revisit this thread, knowing there's a solution.

__________________
Dave

The simplest solutions
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
can be the hardest to find
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Orthodox Dave is offline   Reply With Quote
Reply

Tags
access , connect , odbc

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
odbc mysql won't connect - help! ellenr Modules & VBA 12 01-20-2015 10:11 AM
Sample access connect odbc accesspm2012 Modules & VBA 1 09-27-2014 07:04 AM
Connect to MySQL via ODBC marlan Modules & VBA 6 01-30-2014 12:53 AM
Cannot connect with ODBC colkas PHP & MySQL 2 06-07-2011 01:50 AM
connect using ODBC? qwertyjjj Modules & VBA 6 09-19-2007 09:10 AM




All times are GMT -8. The time now is 03:07 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World