Export table data using FreeFile (1 Viewer)

calvinle

Registered User.
Local time
Today, 14:34
Joined
Sep 26, 2014
Messages
332
Hi,

I have a front end with local table data and back-end table data.
The user will make changes in local table then push the data to the back-end table.
However, sometime, the data pushed to the back-end was not saved for x reason. It could be disconnection from SERVER or anything so I am trying to figure that out.

Now what I want is to be able to save all data of multiple table, but in a single unique text file before it even push to the back-end table.
For that, I am using FreeFile, but my concern is:

Is there a faster way in coding to save all data of multiple table APPEND to the text file so it won't create 1 text file per table.
I want all data of each table in the same text file including the header of the table.

Thanks
 

llkhoutx

Registered User.
Local time
Today, 16:34
Joined
Feb 26, 2001
Messages
4,018
Add a datestamp field to each table so a to be able to determine when a table has been updated. You may also want another datestamp field of when existing rows are updated.

Cycle through the tables container and query each table for rows which have been created, or updated, as the case may be, withing a certain time interval and act on that (queried) data accordingly.
 

static

Registered User.
Local time
Today, 21:34
Joined
Nov 2, 2015
Messages
823
There are several built-in methods of saving data to text files.
They will be much faster than anything you write yourself.

Export your files, create a new container file, open each file, read its contents and append them to the container.

ADO allows you to save recordsets as text with datatypes preserved.
You can either save as ADTG or XML.
XML is human readable but creates bigger files.

Code:
With CurrentDb
    For Each t In .TableDefs
        If InStr(t.Name, "msys") = 0 Then
            With New ADODB.Recordset
                .ActiveConnection = CurrentProject.Connection
                .Open "[" & t.Name & "]"
                .Save t.Name & "_output.txt", adPersistXML
                .Close
            End With
        End If
    Next
End With

Extract files from your container at the other end back into individual files and read them back into a recordset.

Code:
Table = "table1"
With New ADODB.Recordset
    .Open Table & "_output.txt"
    Do Until .EOF
        For Each f In .Fields
            Debug.Print f.Value,
        Next
        .MoveNext
        Debug.Print
    Loop
    .Close
End With

You would need to cycle the recordset to update your tables.
 

isladogs

MVP / VIP
Local time
Today, 21:34
Joined
Jan 14, 2017
Messages
18,186
You can either save as ADTG or XML.

What does ADTG mean in this context? Or is it a typo?

Just checked on acronym finder website ....

 

Attachments

  • Capture.PNG
    Capture.PNG
    11.9 KB · Views: 231

isladogs

MVP / VIP
Local time
Today, 21:34
Joined
Jan 14, 2017
Messages
18,186
Well I guessed that it if it was any, it would be that one ..

However, I'm none the wiser!
What creates or uses that?
 

static

Registered User.
Local time
Today, 21:34
Joined
Nov 2, 2015
Messages
823
Are you bored? :rolleyes: :p

ADO creates it. ADO reads it.
 

isladogs

MVP / VIP
Local time
Today, 21:34
Joined
Jan 14, 2017
Messages
18,186
No - just ignorant :rolleyes:
This just shows yet another gap in my knowledge :)
 

Users who are viewing this thread

Top Bottom