How to export linked table data into another db/table ? (1 Viewer)

[amorosik]

How to export the data of a linked table, so that it can then be imported and restore the contents of the table itself to the original values?
I'm trying to use the various export/import modes that are available via vba code and I'm finding it difficult to restore the contents of the data table In the sense that, for example, if a text field of the original table contains a CR in the table after re-importing I get a CR+LF and things like this
Obviously we are talking about db/tables with the exact same structure
The ultimate goal is to be able to move the data contained in a table of the db into the table of another db
Without modifications
What do you think is the correct method?

 

[ebs17]

When it comes to data and databases, the first thing I think of is queries (SQL) because SQL is the native language in a database.
VBA is then only accompanying music.

 

[Minty]

You can use the TransferDatabase function to move entire objects around, have you tried the various options that presents?

DoCmd.TransferDatabase method (Access)

Office VBA reference topic

learn.microsoft.com

 

[amorosik]

You can use the TransferDatabase function to move entire objects around, have you tried the various options that presents?

DoCmd.TransferDatabase method (Access)

Office VBA reference topic

learn.microsoft.com

Yes, with no success
For example exported is different from imported value
It seems that Access doesn't like the CR, which inserts a CR+LF on its own even if it's not requested

 

[theDBguy]

Yes, with no success
For example exported is different from imported value
It seems that Access doesn't like the CR, which inserts a CR+LF on its own even if it's not requested

I could be wrong, but I believe the CR+LF is a Windows thing - not Access.

Here's a quote I found from StackOverflow:

This is a good summary I found:

The Carriage Return (CR) character (0x0D, \r) moves the cursor to the beginning of the line without advancing to the next line. This character is used as a new line character in Commodore and early Macintosh operating systems (Mac OS 9 and earlier).

The Line Feed (LF) character (0x0A, \n) moves the cursor down to the next line without returning to the beginning of the line. This character is used as a new line character in Unix-based systems (Linux, Mac OS X, etc.)

The End of Line (EOL) sequence (0x0D 0x0A, \r\n) is actually two ASCII characters, a combination of the CR and LF characters. It moves the cursor both down to the next line and to the beginning of that line. This character is used as a new line character in most other non-Unix operating systems including Microsoft Windows, Symbian and others.

Source

 

[amorosik]

I could be wrong, but I believe the CR+LF is a Windows thing - not Access.

Here's a quote I found from StackOverflow:

If TransferDatabase inserts a cr+lf in fields where it finds a cr then it's an Access problem not a Windows one

 

[ebs17]

Cr and LF do not occur in normalized data, and the highly estimated spaces are also rare.
The query is probably in vain and unwanted - but what exactly is such a thing used and needed for?
Is it about the principle or about a comprehensible meaning?

The provided standard methods for import/export are designed for simple standard cases, nothing more. If you want to have SPECIAL requirements implemented, you should turn larger circles and program them individually. There you would also have to specify from which database to export, in which format to export and into which database to import

 

[Minty]

To be fair when you consider, for example, that Access and Excel come out of the same supplier, they are notoriously rubbish at maintaining data in a consistent fashion when you import/export in either direction.

It would be nice for simplicity and repeatability if they played a bit nicer.

 

[The_Doc_Man]

I would think it possible, if dealing with two database files, to link the second DB as well, then use DELETE queries and INSERT INTO ....SELECT queries to copy from one place to another. So you have to "make up" an alias name for the 2nd copy. No biggie - or shouldn't be.

There is also the IN clause which would allow you to SELECT xxx..... FROM table IN database-path-and-name (i.e. a foreign DB file). Using a transfer command goes though some kind of intermediate, but direct SQL should not change anything.

EDIT: Here's a link.

IN clause (Microsoft Access SQL)

Office VBA reference topic

learn.microsoft.com

 

[amorosik]

I would think it possible, if dealing with two database files, to link the second DB as well, then use DELETE queries and INSERT INTO ....SELECT queries to copy from one place to another. So you have to "make up" an alias name for the 2nd copy. No biggie - or shouldn't be.

There is also the IN clause which would allow you to SELECT xxx..... FROM table IN database-path-and-name (i.e. a foreign DB file). Using a transfer command goes though some kind of intermediate, but direct SQL should not change anything.

EDIT: Here's a link.

IN clause (Microsoft Access SQL)

Office VBA reference topic

learn.microsoft.com

Yes of course, but I would like to carry out a data transfer using an intermediate file, preferably a csv or xml text type, as a means of transporting the data
The goal is to exchange information between the same tables of different software procedure, geographically distant, and not accessible from the outside

 

[amorosik]

Cr and LF do not occur in normalized data, and the highly estimated spaces are also rare.
The query is probably in vain and unwanted - but what exactly is such a thing used and needed for?
Is it about the principle or about a comprehensible meaning?

The provided standard methods for import/export are designed for simple standard cases, nothing more. If you want to have SPECIAL requirements implemented, you should turn larger circles and program them individually. There you would also have to specify from which database to export, in which format to export and into which database to import

My fields contain both cr, cr+lf, and lf, and space

"..but what exactly is such a thing used and needed for?.."
To transfer the data contained in a table in db A to db B

"..If you want to have SPECIAL requirements implemented, you should turn larger circles and program them individually.."
Yes sure, that's what I'm trying to do

 

[ebs17]

To transfer the data contained in a table in db A to db B

Are there line breaks in various forms to transfer them from A to B? Does the transfer cause problems then? The dog runs in circles and bites its tail.

In my mind, there are line breaks in texts, not in normalized data. There is an exchange between word processors?

 

[Pat Hartman]

@ebs17 If you have a field with text data, you are quite likely to have CR+LF in those fields. It is not uncommon at all.

@amorosik Are you 100% certain that the source data contains ONLY the CR character? Can you look at it with a hex viewer and see just the single character? Was the source data created with a Windows app? If there is a conversion on export, why does it matter? If there is some negative impact, you can convert the CR character to something like "##@@##" which is unlikely to ever occur naturally and then convert it back on import.

 

[amorosik]

Are there line breaks in various forms to transfer them from A to B? Does the transfer cause problems then? The dog runs in circles and bites its tail.

In my mind, there are line breaks in texts, not in normalized data. There is an exchange between word processors?

My fields contain cr, cr+lf, lf, space, and space sequence
What's not clear?

 

[amorosik]

@ebs17 If you have a field with text data, you are quite likely to have CR+LF in those fields. It is not uncommon at all.

@amorosik Are you 100% certain that the source data contains ONLY the CR character? Can you look at it with a hex viewer and see just the single character? Was the source data created with a Windows app? If there is a conversion on export, why does it matter? If there is some negative impact, you can convert the CR character to something like "##@@##" which is unlikely to ever occur naturally and then convert it back on import.

Yes, the generated text file was created both with the Access TransfertText procedure and with ExportXml
And the generated files have been inspected both with Notepad++ and with Ultraedit and the 'strange' characters (cr, lf, cr+lf, space, space sequence) are present in text fields present in the db

 

[Pat Hartman]

Have you looked at the source data? Where did that come from? How do you KNOW that only CR exists if you didn't examine it wth a hex editor? If the file is a table, you can't use a hex editor. What happens if you copy the text and paste it into notepad?

 

[isladogs]

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.

 

[ebs17]

What's not clear?

Before I would concern myself with the preservation and promotion of this hodgepodge, the meaning and the precise use should be clear to me. An academic "I want it that way" is not convincing.

 

[The_Doc_Man]

Yes of course, but I would like to carry out a data transfer using an intermediate file, preferably a csv or xml text type, as a means of transporting the data
The goal is to exchange information between the same tables of different software procedure, geographically distant, and not accessible from the outside

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

 

[amorosik]

Have you looked at the source data? Where did that come from? How do you KNOW that only CR exists if you didn't examine it wth a hex editor? If the file is a table, you can't use a hex editor. What happens if you copy the text and paste it into notepad?

If the source is a table, and after exported on csv file some Char fields indicate CR characters (dec 13), other Char fields indicate CR+LF characters (dec 13+10), other fields always type Char indicate space characters (dec 32), what do you think is inside those fields?