-
Aug 2nd, 2017, 10:28 PM
#1
Thread Starter
Member
Relink Tables Programmatically
I'm working on an Access 365 app for a client. At their office their tables are linked to a back end database. When I copy the app to my PC I need to relink the tables to the DB on my PC.
I have written this code:
Code:
Public Sub LinkTablesLocal()
Dim computerName As String
computerName = Environ("computername")
Stop
If computerName = "MY_PC" Then
Dim databaseFile As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim tableName As String
Set dbs = CurrentDb()
databaseFile = CurrentProject.Path & "\MyApp.accdb"
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 1 Then
If tdf.Connect <> ";DATABASE=" & databaseFile Then
If Left(tdf.Connect, 4) <> "ODBC" Then
tableName = tdf.Name
dbs.TableDefs(tableName).Connect = ";DATABASE=" & databaseFile
dbs.TableDefs(tableName).RefreshLink
End If
End If
End If
Next tdf
End If
End Sub
The code fails on the RefreshLink command with "The Microsoft Access database engine cannot find the input table or query 'tblAudit'. Make sure it exists and that its name is spelled correctly."
The table 'tblAudit' does exist in the database.
First, is this the right way to do this? If so, what's wrong here?
Many thanks
-
Aug 3rd, 2017, 10:27 AM
#2
Re: Relink Tables Programmatically
My approach is to DoCmd.DeleteObject acTable, Table
first.
and then relink using CurrentDb.TableDefs.Append CurrentDb.CreateTableDef(TableName, 0, RemoteTableName, ODBC)
I don't use refreshlink *shrugs*
-
Aug 3rd, 2017, 03:14 PM
#3
Thread Starter
Member
Re: Relink Tables Programmatically
What does My approach is to DoCmd.DeleteObject acTable, Table do? Delete the table??
If so, why would I want to delete the table?
-
Aug 3rd, 2017, 05:10 PM
#4
Re: Relink Tables Programmatically
Originally Posted by Zephaneas
What does My approach is to DoCmd.DeleteObject acTable, Table do? Delete the table??
If so, why would I want to delete the table?
https://answers.microsoft.com/en-us/...c-68b599b31bf5
-
Aug 3rd, 2017, 05:18 PM
#5
Thread Starter
Member
Re: Relink Tables Programmatically
-
Aug 3rd, 2017, 09:34 PM
#6
Thread Starter
Member
Re: Relink Tables Programmatically
Can you please provide an example of relinking? I know very little about VBA programming (I'm a C#.Net guy) and I need to get this working. I tried plugging in the command you provided but it doesn't work. I really am coding blind here.
Thanks
-
Aug 4th, 2017, 09:41 AM
#7
Re: Relink Tables Programmatically
I think your inital code should work except that the path has to be the path the computer uses not the nice one that windows says.. ie if you have any spaces in the path I dont think it would work...
Easy to test though.. if you have a dev folder on your c drive, it should connect to that.
Use the break point debugging (on the column to the left of the code, click once, a ball should appear and the line highlights). When you execute the code pauses at that breakpoint and you can use the immediates window (ctrl+g) or hover over the variables to confirm that values are correct.
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Aug 4th, 2017, 11:13 AM
#8
Thread Starter
Member
Re: Relink Tables Programmatically
Originally Posted by Ecniv
I think your inital code should work except that the path has to be the path the computer uses not the nice one that windows says.. ie if you have any spaces in the path I dont think it would work...
Easy to test though.. if you have a dev folder on your c drive, it should connect to that.
Use the break point debugging (on the column to the left of the code, click once, a ball should appear and the line highlights). When you execute the code pauses at that breakpoint and you can use the immediates window (ctrl+g) or hover over the variables to confirm that values are correct.
The path I'm using is the actual path, including the database file - "E:\Projects\MyApp\MyDb.accdb"
-
Aug 4th, 2017, 01:46 PM
#9
Re: Relink Tables Programmatically
here's a couple routines from my Access Library.
Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function TableExists(ByVal TableName As String) As Boolean
' Does a Table exist?
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
TableExists = Not IsNull(DLookup("Name", "MSysObjects", "Name='" & TableName & "' AND Type IN(1,4,6)"))
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function LinkTable( _
ByVal ODBC As String, _
ByVal TableName As String, _
Optional RemoteTableName As String) As Boolean
' Create a linked Table
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If TableExists(TableName) Then Exit Function
If LenB(RemoteTableName) = 0 Then RemoteTableName = TableName
CurrentDb.TableDefs.Append CurrentDb.CreateTableDef(TableName, 0, RemoteTableName, ODBC)
LinkTable = TableExists(TableName)
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function UnLinkTable(ByVal Table As String)
' Drop a table link
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If TableExists(Table) Then
If LenB(CurrentDb.TableDefs(Table).Connect) Then
DoCmd.DeleteObject acTable, Table
End If
End If
End Function
ODBC is the connection string. (ODBC is because I typically link to SQL Server tables)
I'm not sure why your original function isn't working.
My best guess is that you're not providing a correct connection string pointing to your file,
and that's why it's really breaking on the RefreshLink.
-
Aug 4th, 2017, 01:47 PM
#10
Thread Starter
Member
Re: Relink Tables Programmatically
-
Aug 4th, 2017, 04:27 PM
#11
Re: Relink Tables Programmatically
i succeeded in replicating the error
have a database with a table say: 'table1'
create a link to that table (in the same database)
rename that link to 'table1'
the original 'table1' will now be deleted
so now there is a linked table 'table1' , that links to the no longer existing 'table1'
did this in access 2003
do not put off till tomorrow what you can put off forever
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|