Select Specific table to relink

[Exodus]

I'm trying to use code to relink the the table but that is not the only table that is linked in the DB. The other linked tables are in other databases so I want to select specific tables to relink as the others may not need it. I alway will want to select the path.

here is an example of the code I'm using I got it from one of the other users here.

 

[Bat17]

try this:-

Function RefreshTableLink(strTable As String, strFileName As String) As Boolean
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Err = 0
On Error Resume Next
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

If Err = 3265 Then
MsgBox "Invalid Table Name", vbCritical
RefreshTableLink = False
GoTo CleanUpAndGo
End If
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName

tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshTableLink = False
GoTo CleanUpAndGo
End If
Else
MsgBox "not a linked table", vbCritical
RefreshTableLink = False
GoTo CleanUpAndGo
End If
RefreshTableLink = True ' Relinking complete.

CleanUpAndGo:
Set dbs = Nothing
Set tdf = Nothing

End Function

HTH

Peter

 

[Exodus]

I get a compile error when Icall that code. Where exactly do I put that.
I tried it in a new module.

 

[Bat17]

In a new module is good but make sure you dont give it the same name as the function.
What was highlit to go with the compile msg?


Peter

 

[Exodus]

It highlights the Call RefreshTableLink

 

[Bat17]

when you call it you need to add the name of the table and the path to the be db.

RefreshTableLink("mytable", "\\SomeDrive\Somedirectory\SomeFile.mdb")

Peter

 

[Exodus]

Ok,

I don't want to hard code the name of the table or the path because iether or can change. So what do I need to do.

Currently to change the path there is the open dialog which enable to browse to the desired db. I think I might know how to use that to change the path.
I'll try

Is there a way to get a list of all the linked tables to choose from?

 

[Exodus]

Ok so I don't know how to change the path on the fly

Here is the code I used

Call RefreshTableLink("AM_Asset", Me.FilePath)

Here is what is in the FilePath Text box
N:\Shared\VotingEquipmentTracking\041102 - PresidentialElection\041102 - Presidential General Election_BE.mdb

The Error I get is
The expression you entered referes to an object that is closed or doen't exist

 

[Bat17]

where does it highlit in code?

Could you try putting it in hard codded to see if it gets the same error

Call RefreshTableLink("AM_Asset", "N:\Shared\VotingEquipmentTracking\041102 - PresidentialElection\041102 - Presidential General Election_BE.mdb")

 

[Bat17]

I have tweaked the code to improve error trapping, see if that helps pin down the problem

Function RefreshTableLink(strTable As String, strFileName As String) As Boolean
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
On Error GoTo Error_Handler
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
tdf.RefreshLink ' Relink the table.
Else
MsgBox "not a linked table", vbCritical
RefreshTableLink = False
GoTo CleanUpAndGo
End If
RefreshTableLink = True ' Relinking complete.

CleanUpAndGo:
Set dbs = Nothing
Set tdf = Nothing
Exit Function
Error_Handler:
If Err = 3265 Then
MsgBox "Invalid Table Name", vbCritical
RefreshTableLink = False
GoTo CleanUpAndGo
ElseIf Err = 3004 Then
MsgBox "invalid Path", vbCritical
RefreshTableLink = False
GoTo CleanUpAndGo
Else
MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical
RefreshTableLink = False
GoTo CleanUpAndGo
End If

End Function

Peter

 

[Exodus]

Works hard coded. Tried the new code the same thing. There is no highlight in the code just the error message box.

It seems no to like the reference to the text box

 

[Bat17]

Me.FilePath is for use from a form module, if you are calling from a general one use
Forms![YourFormName]![FilePath]

Peter

 

[Exodus]

Peter,

Just figured it out there was a close form code befor the call code wich was producing the error.

And it works like a charm know.

Just noticed something else though when after I select the path it doesn't update the text box right away but it does refresh to the right db. I'm confused.

How about a list of linked tables is that possible?

 

[Exodus]

Ok implemented in teh original form and it refreshes the text box right away don't know what it was.

My next task is the list of linked tables

 

[Bat17]

Create a combo box, set its "Row Source Type" to Value List and leave its "Row Source" blank.

add this code to the open event of the form to load up the combo for you.

Dim tDef As TableDef
Dim str As String
For Each tDef In CurrentDb.TableDefs
If Len(tDef.Connect) > 0 Then
str = str & tDef.Name & "; "
End If
Next
str = Left(str, Len(str) - 2)
Me.MyCombo.RowSource = str
Me.MyCombo.Requery


HTH

Peter

 

[Exodus]

In the mean time I came up with this for the row source

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));


It works what do you think about using the sytem objects though

 

[Bat17]

better than my solution especially if you hale lots of tables

Peter

 

[Exodus]

Well I think we killed this one.
Thanks for your help I couldn't have done it with out you.
Much appericiation.

 

[RichardH]

Hi, I've been reading this as a potential fix to my problem. I've tried it and I get the error "User Defined Type Not Defined" on the line "Dim dbs As DAO.Database"
I'm probably doing something stupid but would appreciate the help

 

[Bat17]

you need to set a reference to DAO.
In any module go Tools>References... scroll down the list and find "Microsoft DAO xxx Object Libary" xxx will vary depending on version.

HTH

Peter