Custom collection from TableDefs collection (1 Viewer)

[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!

 

[NauticalGent]

but I saw earlier you had mentioned a 5 year contract......

 

[NauticalGent]

Also you may want to look at a dictionary object instead of a collection. Depending on the things you want to do, it may provide some advantages. Easy to check if something exists. Easy to get a collection of the keys.

Not sure what you mean by this? What is a dictionary object?

 

[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??"

 

[NauticalGent]

Now you tell me...

 

[Pat Hartman]

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]

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.