Custom collection from TableDefs collection

[NauticalGent]

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:

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]

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]

Paul Kelly has a lot of good vba stuff. He's Excel oriented, but here's a link to his VBA Dictionary.


Update:
You'll find lots of his stuff at youtube using "youtube excelmastery dictionary collection"

You may also find this link from Bob Larson re Include/Exclude approach useful.

 

[NauticalGent]

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]

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:

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:

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

Just my 2 cents...

 

[NauticalGent]

Now you tell me...

 

[NauticalGent]

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...

 

[gemma-the-husky]

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.