TransferDatabase Macro needed

[TB0NE]

This may require some VB work but I was hoping for a simple macro solution. However, any ideas are welcome.

I have 15 identical databases in 15 cites (structure is the same, obviously the data is different).

I need to import the data from each one of these databases into one master database.

I have the queries built and a working macro but I'm having a little problem with the 'TransferDatabase' macro. I'll try to explain.

Without using a macro, I can link to an external database, select all of the tables, and click OK. Since the table names are the same between each database, Access links to all of the tables and simply appends a '1' to the end of each table name. The process takes less than 5 seconds. I don't have to name each linked table - Access does it for me. This is perfect. However, I have to do this 15 times and I'd like a macro to do the import / link for me.

In the 'TransferDatabase' I have the option to do the very same thing HOWEVER, the macro requires that I identify the table name AND specify WHAT the newly linked table name will be. This put a major crimp in my plan because I would have to specify each table and a new table name 34 (there are 34 table) times AND repeat that for each database (15 X 34 = more than I will consider doing).

So my question is: How can I create a macro that will take all of the tables from one database and link it to the master and use the default name of 'tablename1' for each linked table. Access defaults to this name when you run it manually, I just want to repeat this same process in a macro.

Any and all macro and VBA solutions are welcome.

Thanks.

 

[DaltonVB]

here is what I have worked out.
this is just for one database. You would have to repeat the steps in cmdLink_Click() for each database you want to link the tables from.

Private Sub cmdLink_Click()
    Dim szDB As String
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim iTblCount As Integer
    Dim i As Integer

    Set rs = New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    'link the MSysObjects table from the first database
    szDB = "C:\yourpath\yourdatabase.mdb"
    
    f_link szDB, "MSysObjects", "DB1MSysObjects"
    
    'create a recordset of all the tables from that database that are not 'MSYS*'
    rs.Open "SELECT DB1MSysObjects.Name, DB1MSysObjects.Type " _
          & "FROM DB1MSysObjects " _
          & "WHERE (((DB1MSysObjects.Name) Not Like 'msys*') " _
          & "  AND ((DB1MSysObjects.Type)=1));" _
          , cnn, adOpenStatic, adLockBatchOptimistic
          
    'loop through all of the tables
    iTblCount = rs.RecordCount - 1
    For i = 0 To iTblCount
        f_link szDB, rs!Name, rs!Name & "_1"
        rs.MoveNext
    Next

End Sub

Sub f_link(szDB, szSource, szDestination)
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
                       szDB, acTable, szSource, szDestination
    
    
End Sub

 

[TB0NE]

Thanks DaltonVB!

Especially for laying it out for me with the comments.

Gimmie a chance to digest it and I'll try it tonight.

 

[bethabernathy]

Hi - I was wondering if this will work with a Microsoft Access Project or .adp file? I tried changing this line

DoCmd.TransferDatabase acLink, "Microsoft Access", _

to

DoCmd.TransferDatabase acLink, "Microsoft Access Project", _


and when I ran it I got the message:

Run-time error 2507
The Microsoft Access project type isn't an installed database type or does not support the operation you chose

beth@integratedresourcemgmt.com

 

[DaltonVB]

beth,

I don't have SQLServer running at my current location so... I'll have to take a look when I get back to my office tonight. Depending on what you need to do with the table, you could use the ADO objects.
Here is a link to MSDN code examples.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumdm.asp

 

[bethabernathy]

Hi - I tested the code written on 9/11/03 above and it works perfectly importing from an access database, but it links the tables rather than imports them. I just need to find a way to import the tables from an .adp. I am searching and found one article on the Microsoft knowledge base, but it didn't work. I don't want to link directly to the .adp as it is a live connection to the SQL database and I don't want to let the end user have any access to the real data, could cause problems. -Beth

beth@integratedresourcemgmt.com

 

[DaltonVB]

In that case... Importing the table will take just as long as running a query against the linked table to make a local table. Did that make sense?

DoCmd.RunSql "SELECT * INTO 'the new table' FROM 'the linked table'

 

[bethabernathy]

Hi There - I guess I am confused. I ran the code above and it won't work with an .adp as you can't link the .adp tables. But it does work perfectly with an .mdb. Imported all the tables very well. I am wondering is there any code that will import all the tables from an .adp. I tried to adjust the above code, but am not very good with VB. Any help is appreciated. Right now I am at:

http://support.microsoft.com/?kbid=210177

Thanks, Beth

beth@integratedresourcemgmt.com

 

[DaltonVB]

I'll just have to try some things when I get back to my office. Are you running SQLServer2000?

 

[bethabernathy]

Hi There - I am not running SQL server. It is just an .adp that was generated based on the data in the SQL server. If you open it via FTP with a connection to the web files it updates automatically on my hard drive. That is why I don't want the data disturbed and want to import the tables into a new .mdb. I can set up an import to import each table individually it is just going to take a little while, but may be the best solution. Let me know if you come up with any thing. Thanks so much, I really appreciate it. -Beth

beth@integratedresourcemgmt.com

 

[H1004]

Hi Beth,
You seem to know alot about this transferdatabase function. And so I was wondering if you could help me with this situation.

I have two databases…One is Personnel_be.mdb from the R drive, and another one Personnel_be.mdb at a Q drive.
This is my command line to export the table tblAssetCheckout from the R drive to the Q drive. So when I ran it, it gave me the following error message:

“You can’t delete the tblAssetCheckout; it is participating in one or more relationship.”

I guess I’m kinda confused in why that is the case when I’m just exporting them and not deleting it. Any suggestion on this is greatly appreciated.

My code from the Personnel.mdb (front end), which has a link to the table from the R drive. This is from onclick of an Update button.

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"Q:\\MRPersonnel_be.mdb", acTable, _
"tblAssetCheckout", "tblAssetCheckout", False