TransferDatabase: Export REAL data from linked table 1

[waldemar]

And here is another tricky one:

I would like to export table data of an external linked table into a new database (using TransferDatabase). However the new database contains not the DATA, but the connection to origin table. How do I transfer the REAL data?

Regards
waldemar

 

[KyleS]

waldemar,

I am assuming this is something you plan on automating. If it's not, just create a make table query and make a duplicate of the linked one, then delete the linked table and rename your new one.

If you plan on automating this, my first guess (I don't know that this is the best way, very little code and very easy to troubleshoot, but also very low-tech. Perhaps one of our colleagues could offer a different solution?) would be to use the make table query in you original dB, and that will transfer real data. If you decide to go this route, I would suggest, once the other table has been created to change your make table query to an append query and create a delete query to clear out the old data before appending.
You could automate the table appending in the same module as your transfer database so you always get good data.

Hope this helps,

Kyle

 

[waldemar]

thanks for your help but waitwaitwait, im getting puzzled here... i am not working with queries in this case at all; i think we're talking of something slightly different...

i am using vba to create a new "remote" database and i just want to "copy" a table into it. the table itself doesnt really "exist" in the current applicationdb but is available as a LINKED table. Whenever I use TransferDatabase (which seems to be exactly the right function) access exports the LINK to the table - not the real content. That looks like right behavior of the function, but (the new created database has to run standalone) what i need in the new database is the actual table (structure and data), not just a link. The question is: how to tell TransferDatabase not to copy link, but the complete table.... any ideas?

regards
waldemar

 

[KyleS]

OK, well I tried to respond earlier, but my PC went down, so I have to re-type my explanation.

I kow you didn't intend to use queries here, but since (as you know) the table isn't in the database, it won't transfer the table, just the information in the current dB (which is the linking info) the easiest way I can think of to get it there is to make a duplicate table and Append the data into it. This is what the queries would be for. You run the queries which will make a copy of your linked table inside your dB, and then run your "Transfer Database" module which will transfer all the real data to your new database from the copy.

Let's call your linked table "TableA". Let's Call the table that you create withing the dB "TableB". Delete all info in "TableB", then append the info from "TableA" into "TableB". That way there is a version of the table within your dB. This one will work with "Transfer Database". Just put the code to run your Delete and Append queries right in front of you current module.

If I still haven't explained this right, let me know.

Kyle :

 

[BryanF]

All you have to do is make one make table query... include all of the fields into it...when you choose make table from the query menu it will ask you what database to put it in... tell it which one and you are done.... then run transferdatabase to send the table it made.... you can do this anytime...it automatically overwrites the old table.

 

[KyleS]

Yes Bryan, it does automatically overwrite the old table. I told him to use the make table and then change to Append and use the Delete/Append method of table manipulation to keep from bloating the database (it'll run faster too)

The second entry was an explanation of why I brought queries into this equation when he was thinking VBA only.

 

[waldemar]

ahhh i got you... the key element is that INTO statement in the SQL query.... now i'm using

DoCmd.RunSQL ("SELECT [a].* INTO FROM [a];&quot

into my code and it works - thanks guys!
One minor beauty issue... is there were a way to get around all the requesters ("are you sure to insert blablab....&quot...?

regards
waldemar

 

[KyleS]

DoCmd.SetWarnings = False

will disable all those "are you sure..." remarks, but just be sure to put

DoCmd.SetWarnings = True at the very end of the code to re-enable them.

 

[waldemar]

thanks, it works fine now.