How to export linked table data into another db/table ? (2 Viewers)

[amorosik]

Rather than trying to export your linked table data, i would suggest you do the transfer from the destination file.
You then have far more control over the data transfer including the use of an intermediary or staging table to process your data before final import to the destination table.

"..i would suggest you do the transfer from the destination file.."
I don't understand exactly what you mean
If I have to pass data from a source table to a destination table, using an intermediate file, I have to start pulling data from the SOURCE table

 

[Minty]

If you use TransferDatabase on the whole table does it not preserve the data correctly?
That would seem very strange to me.

What happens if you copy the table using right-click and paste it in the destination database?
Or are we not talking two Access databases here?

 

[amorosik]

You are thinking perhaps a little bit narrowly here. If you want to carry the data physically, "sneakernet" will work fine. Put the back-end file itself on a thumb drive and do your linking from the target DB so you can manually pull the data over. A 4 GB thumb drive (these days) is dirt cheap and an Access BE can't exceed 2 GB so it should all fit. Creating non-Access intermediate files may well be the cause of some of those unexpected text line-breaks. We have a phrase that may apply here. "If you can't bring Muhammad to the mountain, bring the mountain to Muhammad."

".. Put the back-end file .."
I wrote about 'linked tables' but we are not talking about Access file as a data container
The data is contained in a db server, which Access accesses via odbc

The focal point is not how to transfer data from one db to another
I can already do this now using the classic administration tools of the used db server

My question is how to create, if it is possible to do so, a procedure in vba that allows me to put all the data contained in a db table into a file, and then a procedure that allows me to use this file to restore the data table on other db, without losing characters

Having already tried various native Access procedures such as Docmd.TransferText and similar to export the contents of a source table to an intermediate file, and having found that after importing the data, the destination table is not identical to the source table, so I set the question hoping that there was some particular option in the native Access data export/import procedures that would allow to overcome these problems

But I understand that native procedures only work for the simplest cases, and therefore to be sure that the destination table is identical to the source table, the only possibility is to create customized code

 

[Pat Hartman]

If you are seeing both (dec 13) and (dec 13 + 10) in the output, then what is the problem? It isn't Access that is messing with the output, it is the user being inconsistant with what they type.

 

[Pat Hartman]

If you want someone else to export to see what happens, upload a table with the suspect data.

 

[isladogs]

"..i would suggest you do the transfer from the destination file.."
I don't understand exactly what you mean
If I have to pass data from a source table to a destination table, using an intermediate file, I have to start pulling data from the SOURCE table

You have been talking about exporting to a destination file throughout this thread and are having problems.
My suggestion is to do the opposite. Open the destination file and import or link the table(s) instead. It may solve your problem?

 

[The_Doc_Man]

My question is how to create, if it is possible to do so, a procedure in vba that allows me to put all the data contained in a db table into a file, and then a procedure that allows me to use this file to restore the data table on other db, without losing characters

My question in response is, what is the actual "flavor" of database for (a) the DB that is the source of this table to be transferred and (b) the DB that is the destination for this table?

IF they are both ACCESS databases, my original suggestion stands. Copy the DB as your container and use a direct query.

IF at least one DB is NOT a native ACCESS DB but you can still use ODBC methods, you still should be able to get the data you want with minimal changes by doing a direct INSERT INTO .... SELECT .... FROM query using a native ACCESS file as the thing being transported.

Your desire to use some other format for transfers will be a thing to trip you up badly, because when you have to do a transfer of text AND you allow a conversion from the table to whatever you use as a container, you are going to run into text conversions, and ACCESS is going to convert according to its own internal rules.

If you have inconsistent sources of data input (specifically with respect to line-ending characters), your problem is to first get rid of the inconsistencies. If you cannot do that, your insistence on a non-ACCESS data container is one likely source of the problems you have noted regarding end-of-line characters. I.e. you are doing this to yourself by insisting on a stringent method of data transfer.