June 5, 2015 at 8:07 am
I recently ran into quite the conundrum trying to change both the .Connect and .SourceTableName properties of a Linked Table in MS Access 2007. The Linked Table Manager just couldn't handle the type of change that was needed, as the objects to change to had a different name in the new database. I had found code all over the web that would demonstrate how to write the VBA to make the changes, but the SourceTableName just wouldn't change, and additionally, I had trouble even getting a good object reference to the TableDef object. Then I ran into a post that clearly indicated you can't actually make that latter change, and thus you have to take any needed properties from the old and store them in variables, then rename the old table def, then create a new one with the desired properties, and then delete the old one. Here's the VBA code, which is slightly modified from the original that was posted (link to the original is below the code):
Function ShowLinkedTables(ShowOnly As Integer) As Integer
'Returns all linked tables
Dim tdf As TableDef, db As Database
Dim strName As String, strConnect As String, strSourceTableName As String
Dim strConnectNew As String, strSourceTableNameNew As String
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
With tdf
If Len(.Connect) > 0 Then
If Left$(.Connect, 4) = "ODBC" Then
strName = .Name
strConnect = .Connect
strSourceTableName = .SourceTableName
Debug.Print ""
Debug.Print strName
Debug.Print strConnect
Debug.Print strSourceTableName
strConnectNew = Replace(strConnect, "OLDSERVERNAME", "NEWSERVERNAME")
strSourceTableNameNew = Replace(strSourceTableName, "oldschema.", "newschema.")
If ShowOnly = 0 Then
Call ChangeSourceTable(strName, strConnectNew, strSourceTableNameNew)
End If
End If
End If
End With
Next
Set tdf = Nothing
Set db = Nothing
ShowLinkedTables = Err.Number
End Function
Sub ChangeSourceTable(strLinkedTableName As String, strConnect As String, strSourceTableName As String)
Dim db As DAO.Database
Dim tdef As DAO.TableDef
Dim x As Integer
'Point to the current db
Set db = CurrentDb
'Rename the OLD tabledef
DoCmd.Rename strLinkedTableName & "_Delete", acTable, strLinkedTableName
db.TableDefs.Refresh
'Create a new tabledef that points to the new source table
Set tdef = New DAO.TableDef
tdef.Name = strLinkedTableName
tdef.Connect = strConnect
tdef.SourceTableName = strSourceTableName
'Add the new table to the tabledefs collection
db.TableDefs.Append tdef
db.TableDefs.Refresh
'Delete the OLD tabledef
DoCmd.DeleteObject acTable, strLinkedTableName & "_Delete"
'Refresh the db window
RefreshDatabaseWindow
'Clean Up
Set db = Nothing
Set tdef = Nothing
End Sub
The subroutine is what I changed, as I had already cobbled together the initial function from elsewhere. The subroutine's original code can be found here:
http://www.utteraccess.com/forum/index.php?showtopic=1370871
The function preserves values from the existing linked tables for use with the new. The only changes I needed were to schema and to server names, so if you need any other kinds of changes, you'll need to modify the code to handle them. Hopefully, this will save someone else the time I wasted yesterday going all over the web trying to find a solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 21, 2016 at 8:48 am
Hi!!
Running almost the same code gives me error that engine database did not find the object. This object is the SourceTableName that previously given the value I want to change.
Can you help me?
September 21, 2016 at 9:02 am
Unless you are very proficient in VBA, in the object model for Microsoft Access, and you have more than 50 tables you need to reconnect, I would suggest you either use the linked table manager, or manually connect each of the tables from the new database, and then rename them to the old Access names.
To revise the code from the original post would require a good deal more information about your situation, the SQL Server database you are connecting to, etc.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
September 21, 2016 at 4:52 pm
The moment you said "almost the same code", I had to conclude that there's no way to know if the changes might have caused your grief or not. If you don't have good VBA skills, this routine isn't just going to magically fix everything. You need to be able to apply it to your situation and make any needed changes on your own. As you're getting an error, at least take the time to debug the code and find out what statement is causing the error, and troubleshoot from there. There's no way for me to know what your situation is or what your objective is, as you've made no indication on either of those things.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2016 at 1:15 am
done!!! thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply