Why DoCmd.TransferDatabase not work in a for loop

[dhlao]

Below code works

DoCmd.TransferDatabase acLink, "Microsoft Access", "path_to_accdb", acTable, "table1", "table1"

In a for loop not work, show error 3011 "The Microsoft Access database engine could not find the object 'table1'.

tables = Array("table1", "table2", "table3")
For Each element In tables
   DoCmd.TransferDatabase acLink, "Microsoft Access", "path_to_accdb", acTable, element, element
Loop

 

[Ranman256]

Use a collection instead of array.

Dim col as new collection
Col.add "table1"
Col.add "table2"

For each itm in col
  Docmd.transfer...  Itm
Next

 

[dhlao]

Hello Ranman256
Now I move those tables name to a new created table. And loop through them by RecordSet. But it still show the error 3011.

I haven't try the collection method yet. But if I have thousands of tables need to link. Then I need to convert them all to collection. This doesn't make sense.

 

[sneuberg]

This doesn't make sense.

I agree. Going back to the code you had that should have been written with Next element rather than loop like:

tables = Array("table1", "table2", "table3")
For Each element In tables
   DoCmd.TransferDatabase acLink, "Microsoft Access", "path_to_accdb", acTable, element, element
Next element

but the fact it stopped with an error complaining about a missing object leads me to believe its not the loop that's the problem but the statement in it. I suggest getting this to work with one table and proceed from there, i.e., try

DoCmd.TransferDatabase acLink, "Microsoft Access", "path_to_accdb", acTable, "table1", "table1"

to use your example/

 

[Cronk]

Will you post your whole code routine. Your original post had a syntax error (For with Loop). It would help to see what the issue is if the whole context is available.

 

[dhlao]

Thanks all you guys who reading my post.

The For loop works now. I don't know why it fail last time (definitely not using the wrong "Loop", Otherwise I should got syntax error instead of error 3011)

Actually, I'm testing different method to link the backend table. So far, below methods works:
1. DoCmd.TransferDatabase loop through table name in an array
2. DAO append tables in an array by a for loop

Now I try to place the table name in a table instead of an array. And both method above will show error 3011.

I've attached the frontend (Database1.accdb) and backend (Database2.accdb) files. Please give it a shot.

 

[stopher]

The names in your table are:
company_name
currency_name

But the names of the tables in Database2 are:
company_tbl
currency_tbl

so your code won't find the tables it is looking for.
hth