Link Tables & Rename

[jo15765]

I want to open one database, and from this database open additional databases & delete 3 tables, then link in 3 tables, rename the newly linked tables. Close the database, then open a 2nd databse do the same thing, 3rd database, 4th database etc etc

How would you do this with VBA? I know how to simply link in a table with VBA, but I haven't been able to find or figure out how to do this from a different database entirely.

For example, this is what I would want to do
1) Create an array of databases that need to be opened
2) Iterate thro those databases and open the database
3) Delete 3 linked tables (I would need to specify the names)
4) Link in from SQL 3 tables
5) Rename those 3 tables (each with there own unique name)
6) Close the Database
7) Open the next database in the array

EDIT ---
Code is for Access 2000 or Access 2007 (I Have both versions)

[HiTechCoach]

I think you needing to replace three Access linked tables with SQL Server linked tables. Retaining the original table names when done Is that correct?

If yes:
Do you have to follows these step?
Would you be open to a simpler/less step solution?

[jo15765]

I think you needing to replace three Access linked tables with SQL Server linked tables. Retaining the original table names when done Is that correct?

If yes:
Do you have to follows these step?
Would you be open to a simpler/less step solution?
I need to replace 3 Access Linked tables from SQL Server, correct. I need to retain the names of them in Access not the SQL Server Names.
No I do not have to follow those steps.
Yes I am open for any and all solutions :)

[HiTechCoach]

Here is how I do this:

1) create a new blank database
2) create the three linked tables to the SQL Server

Note: Now that you have the linked tables created , you can export them to the other databases

3) I create a table with a list of all the database I want to update named tblDatabases. It has a field DBPath that holds the full path to the database


4) create a code module hold the Sub to export.

Example:



Option Compare Database
Option Explicit


Public Sub UpdateTables()

Dim rsDB As DAO.Recordset

Dim strDBtoUpdate As String

Set rsDB = CurrentDb.OpenRecordset("tblDatabases")


Do Until rsDB.EOF
strDBtoUpdate = rsDB!DBPath

DoCmd.TransferDatabase acExport, "Microsoft Access", strDBtoUpdate, acTable, "Invoices", "Invoices", , True
DoCmd.TransferDatabase acExport, "Microsoft Access", strDBtoUpdate, acTable, "InvoicesDetail", "InvoicesDetail", , True
DoCmd.TransferDatabase acExport, "Microsoft Access", strDBtoUpdate, acTable, "InvoicesPayments", "InvoicesPayments", , True


rsDB.MoveNext
Loop

' close recordset
rsDB.Close
Set rsDB = Nothing

MsgBox "Export of SQL tables completed."

End Sub





Note: This code will automatically replace any table with the name same without any warning.


I have used this method to replace or add other objects to database like Forms, Reports, etc.

[jonh]

To connect to another Access database you do something like this...


Set ws = DBEngine(0)
Set db = ws.OpenDatabase(filename)

To delete a table you can use


db.TableDefs.Delete "TABLENAME"
To create a linked table you use something like this...


Const MSACC_NAME = "My_Linked_SQL_Table"
Const SQL_TBL As String = "SQL_TABL_NAME_HERE"
Const DSN = "YOUR_DATABASE_DSN"
Const TBLCONN As String = "ODBC;DSN=" & DSN & ";DATABASE=" & DSN & ";Trusted_Connection=Yes;"

Dim td As New DAO.TableDef
td.Name = MSACC_NAME
td.Connect = TBLCONN
td.SourceTableName = SQL_TBL
db.TableDefs.Append td

The hardest part is to get the connection string (TBLCONN above) right.
An easy way to do that is to link a table manually and copy the string Access creates for you...


debug.Print db.TableDefs("YOURLINKEDTABLE").Connect