Custom collection from TableDefs collection (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:38
Joined
Apr 27, 2015
Messages
6,286
Greetings everyone,

I have about 10 tables that I need to export to an external DB - Linking is not an option

I think if I made a collection of Tables then I could do something like:


Code:
For Each tdf In myCollectionOfTables
        DoCmd.TransferDatabase acExport, "Microsoft Access", "\\ExternalDb", _
                                   acTable, tdf.Name, tdf.Name, False
Next tdf

I assume this would be the best way to do this - I thought of making a table to hold the table's names and then loop through a recordset but thought this way would be better.

I have tried to add the tables but I keep getting syntax errors - is this even possible?

As always, I am open to suggestions and I thank you in advance!
 

June7

AWF VIP
Local time
Yesterday, 22:38
Joined
Mar 9, 2014
Messages
5,424
You might want to do research on arrays, collections, dictionaries, recordsets for their differences, advantages, disadvantages.

However, don't think you really need it for this procedure.
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:38
Joined
Jan 23, 2006
Messages
15,364
Last edited:

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:38
Joined
Apr 27, 2015
Messages
6,286
You might want to do research on arrays, collections, dictionaries, recordsets for their differences, advantages, disadvantages.
I started to do this with a Collection and then an Array but looping through a recordset was all that was needed. Things didn't become clear until I actually started writing the code and I asked myself "WHY are you making this so hard??"
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:38
Joined
Oct 29, 2018
Messages
21,358
Good point and once I took a step back, it made perfect sense.

Also, thanks MajP - don't know why I thought I could use an object variable without setting it to something first.

I made a small temp table that holds the table names - easier to add to it rather than add fields to existing tables.

For the record, here is the code I came up with:

Code:
Public Sub ExportEDATables()
On Error GoTo error_handler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strExtDB As String
    Dim tdef As DAO.TableDef

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from tblEDATables", dbOpenSnapshot)

    strExtDB = "\\MyPath\MyDB.accdb"

    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While Not .EOF
                  Set tdef = db.TableDefs(.Fields("TableName"))
                DoCmd.TransferDatabase acExport, "Microsoft Access", strExtDB, _
                                        acTable, tdef.Name, tdef.Name, False
                .MoveNext
            Wend
        End If
    End With
  
exit_handler:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set tdef = Nothing
    Set db = Nothing
    Exit Sub

error_handler:
    MsgBox Error$
    Resume exit_handler
    Resume

End Sub
Hi. Glad to hear you got it sorted out. But, if I may, I don't see the need to set tdef at all. You should be able to also do this:

Code:
            While Not .EOF
                DoCmd.TransferDatabase acExport, "Microsoft Access", strExtDB, _
                                        acTable, !TableName, !TableName, False
                .MoveNext
            Wend

Just my 2 cents...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:38
Joined
Feb 19, 2002
Messages
42,981
I thought of making a table to hold the table's names and then loop through a recordset but thought this way would be better.

I'm not sure why you think creating a new collection would be better than a table. Certainly it is proving to be more difficult but sometimes KISS is best. june's suggestion of a suffix is fine if that doesn't interfere with anything that already exists. Or, if the BE is linked, you can create additional links with a prefix or a suffix and use that in your criteria.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:38
Joined
Apr 27, 2015
Messages
6,286
I'm not sure why you think creating a new collection would be better than a table.
I don't anymore. Having never used the Transfer Database to export an object, I looked up some code and saw Daniel Pinault's example which used the TableDefs collection, I got wrapped around the axle on this was how you HAD to do it.

It has been a day...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:38
Joined
Feb 19, 2002
Messages
42,981
Dan's example was exporting ALL non-system tables. That isn't your situation. You need a way of identifying which tables you want to export. You can do that by name if there is some suffix or prefix you can use. If there is nothing already in the table name, you need some manual selection method to identify which tables you want to export.

You could use a multi-select listbox but that would be tedious and error prone if you always want to export the same set. I would just make a table and be done with it. You can make a form if you want to allow the user to add/delete tables from the export list so you don't have to manually maintain it unless you like doing that kind of stuff.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 06:38
Joined
Sep 12, 2006
Messages
15,614
I would have a table, and populate it with your table names.
Add a field called "export", and then just iterate the table for the fields set to export=true

Alternatively, you could run the process on all tables, and add a y/n msgbox before exporting each table.

Depends how many tables you have, and how frequently you need to do this, really.
 

Users who are viewing this thread

Top Bottom