Delete Linked Tables then Link again in VBA

[Jimgarry]

Hi, thanks in advance,

I have have a mdb that get its data files from another mdb. no problem so far. what I want to do is delete all teh linked tables from the main mdb and then link all the tables from another mdb.

1. How can I get a list of all linked tabels in an mdb so I can programitlcy(sorry) delete the links

2. I want to attach to the new mdb and link all tabels in that MDB to the main .mdb again.

My thought would be to put the list in a tabel in the main mdb and use this to delete each tabel link then attach to the new mdb and some how get a list of all tabel and and link them?


Thanks for any assistance

Jim

 

[hymn]

try doing a search on references BillPower did something on what you are after

Hope this helps
Hymn

 

[perrymans]

Why are you deleting the tables? Would relinking them to a different DB be fine? Like switching from Live to Archived tables in two seperate DB's?

If this is what you want, let me know and I'll provide it to you.

Sean.

 

[Jimgarry]

Perry That may work but, I came up with a solution to what Im trying to do. the reason is becase I will not be using the current db I will be switching to the new db at clients sites and I do not want the client to go through relinking tables. Thanks

Jim

Private Sub forcechange()

Dim dbs As Database
Dim tdf As TableDef
Dim dbsnew As Database
Dim tdfnew As TableDef
Dim dbHRTC As Database
Dim dbNewDatabase As Database
Dim rsPreferences As Recordset
Dim lsDatabasePath As String
Dim lsSQL As String
Dim strInstallPath As String
Dim strTableName As String



Set dbHRTC = CurrentDb()

Set rsPreferences = dbHRTC.OpenRecordset("Select * from tblVersion", dbOpenDynaset)
strInstallPath = rsPreferences!instDataPath
Data_Path = strInstallPath
Set dbNewDatabase = OpenDatabase(strInstallPath)

For Each tdf In dbHRTC.TableDefs
If Len(tdf.Connect) > 0 Then
strTableName = tdf.SourceTableName

' DoCmd.DeleteObject acTable, strTableName

Set tdf = dbNewDatabase.CreateTableDef(strTableName)
tdf.Connect = ";Database=" & Data_Path
tdf.SourceTableName = strTableName
'db.TableDefs.Append tdf
'DoCmd.TransferDatabase acLink, "Microsoft Access", Datafile, acTable, "tblAccruals", "tblAccruals", False
DoCmd.TransferDatabase acLink, "Microsoft Access", strInstallPath, acTable, strTableName, strTableName, False
End If


Next
Set dbHRTC = Nothing

End Sub

 

[perrymans]

How are you using this? Are you taking a troubleshooting or development DB to the customer sites and linking into their system while there, but taking the same DB back home again (or to another site)?

Sean.

 

[Jimgarry]

Sean,
We have an hr product that was written by an outside company. their install requires the user to install to a spacific location. thats fine if you are not on a network. If you are on a network you have to copy the data file to the local system to do the upgrade becase of the program files linking. to change this I am going into their application and changing the linking of all data files from the location they have to the location the client wants. that way the data dosent have to be moved around and the upgrades can take place on the local computers as needed. The vendor changed some of the table layouts and then I added some more of my own. the original upgrade applies these changes to the data - db at the local install area only. (and in their special directory).

I have written a complete install package with vb 6 and access for their install and the additions I have added.