Change reference to a linked table using VBA

[felixc36]

Hi,

I have question with regards to link tables.

I have a liked table and would like to change it's source while keeing the
same table name.

Is there a way to simply change a property of the linked table to point to the
new location of the file.

Example:

Linked table named "MylinkedTable" in Access.

Source is "C:\linkedTable\source1.dbf"

I want to change the source to "C:\linkedTable\source2.dbf"


I saw some properties in the MSysObjects table but am not sure if I can write
in this system table using VBA.

Oh and by the way I am using Access 97. (not my fault but the people asking me
this project are still with that version).

Thank you in advance.

 

[Naresh Nichani MVP]

Hi:

Some code for this --

Here --
strShadow is the current Database Path (Database you are working on)
strLinkedPath is the Linked Database Path - database you want to link to -
for you this is "C:\linkedTable\source2.dbf"
tblName is the Name of source table - this is table in linked database you
want to link to
newtblName is the Name of the table in current database which is linked to
tblName (this is normally same as tblName)
strLinkPwd is an Optional argument - specifiy if source database has a
password


Public Function LinkTable(strShadow As String, strLinkedPath As String,
tblName As String, newtblName As String, Optional strLinkPwd As String) As
Boolean
'NN - 7/27
'returns True if successfull

On Error GoTo errHandler
Dim db As DAO.Database
Dim tdfLinked As DAO.TableDef
Dim visPath As String

visPath = modGlobal.strLocalFolder
Set db = Workspaces(0).OpenDatabase(strShadow, False, False, ";PWD=" &
CONPWDE)
Set tdfLinked = db.CreateTableDef(newtblName)
tdfLinked.Connect = "MS Access;DATABASE=" & strLinkedPath & IIf(strLinkPwd
<> "", ";PWD=" & strLinkPwd, "")
tdfLinked.SourceTableName = tblName
On Error Resume Next
db.TableDefs.Delete newtblName
On Error GoTo errHandler
db.TableDefs.Append tdfLinked
LinkTable = True
db.Close
Set db = Nothing

Exit Function
errHandler:
Call modGlobal.GenericErrorHandler("LinkTable", Err.Description, Err.Number)
LinkTable = False
End Function


Regards,

Naresh Nichani
Microsoft Access MVP

 

[Marshall Barton]

I have question with regards to link tables.

I have a liked table and would like to change it's source while keeing the
same table name.

Is there a way to simply change a property of the linked table to point to the
new location of the file.

Example:

Linked table named "MylinkedTable" in Access.

Source is "C:\linkedTable\source1.dbf"

I want to change the source to "C:\linkedTable\source2.dbf"


I saw some properties in the MSysObjects table but am not sure if I can write
in this system table using VBA.

Oh and by the way I am using Access 97. (not my fault but the people asking me
this project are still with that version).

Hey, there's nothing wrong (and a lot that's right) about
A97.

Use DAO to change the TableDef's SourceTableName property.
You should also use the TableDef's RefreshLink method after
changing the TableDef object.

 

[felixc36]

I think I am close but still have problem changing the link to a link table.

Below is my code where the Sub Create_Connection creates a new linked table
and works fine.

The problem is that when i try to modify the SourceTableName property i get an
error message "Cannot set this propertyonce the object is part of a collection
(3268)"

Basically what I want to do is change the table "Linked_Table1" 's source to
read the rile AG151.dbf instead of AG102.dbf.



Here is my code and it's made in Access 97.


Regards

Sorry Marshal if I sent a previous message to your E-Mail I clicked on the
wrong button on my newsreader instead of posting it I accidentally sent it to
your E-Mail.....my mistake






Sub Create_Connection()

Dim db As Database
Dim t As TableDef

Set db = CurrentDb

Set t = db.CreateTableDef("Linked_Table1")

With t
.Connect = "dBase IV;DATABASE=C:\Access_Portfolio\test"
.SourceTableName = "ag102.dbf"
End With

db.TableDefs.Append t

End Sub



Sub change_connection()

Dim db As Database
Dim tbl As TableDef
Dim rst As Recordset

Set db = CurrentDb

Set tbl = db.TableDefs("Linked_Table1")

With tbl
.Connect = "dBase IV;DATABASE=C:\Access_Portfolio\test"
.SourceTableName = "ag151.dbf"
End With



End Sub

 

[Marshall Barton]

Sorry, I forgot that the SourceTableName could not be
changed once the tabledef was added to the tabledefs
collection.

You'll have to delete the old tabledef and create a new one.
Not too big a deal, since you can retrieve the relevant
properties from the old one, delete it, create a new one and
set t--
Marsh
MVP [MS Access]