Access table data transfer to file

[Saurabhk]

Hi Guys,

I was just trying to transfer data from access tables to a txt file and then back from file into a table using following code

Private Sub Command0_Click()
DoCmd.TransferText acExportDelim, "Db Export Specification", "AUTHENTICATION", "J:\Power Equipment\Common\SK\db.TXT"
End Sub

Private Sub Command1_Click()
strSQL = "delete * from AUTHENTICATION_TEST"
DoCmd.RunSQL strSQL
DoCmd.TransferText acImportDelim, "Db Import Authentication Specification", "AUTHENTICATION_TEST", "J:\Power Equipment\Common\SK\db.TXT"
End Sub


Is it possible to create and manipulate import/export specifications using the VBA code rather than the access manu so that I can make it work on different systems.

Thanks for your time

[OBP]

Saurabhk, I am not sure what question you are asking, Is it that your code is not working, if so which part the Export or the Import.
Or are you asking if the Text file can be manipulated by Visual Basic or VBA?

[stanl]

you might want to search the forum for "schema.ini" which permits using ADO w/out needing to even open an Instance of Access. .02 Stan

[Norie]

Why transfer to a text file then transfer back into Access?

[Saurabhk]

Hi Guys,

Thanks for the reply.

My question is, do I have to specify import and export specifications individually on each and every machine I use the Database on or are these specifocations saved within the DB?

The way I specified the import and export specification right now is by using the example on the website below

http://www.blueclaw-db.com/export-specifications.htm

My guess is by using this method I have to specify import export specification on all the machines that use this DB. As I don't have access to any of the other machines I'll have to come up with some coding to de define the specification within VBA.

[Saurabhk]

Why transfer to a text file then transfer back into Access?

Hi Norie,

I have to transfer the data from table to text file so that I can send the TXT file it to other users residing interstate. Then I have to come up with a code behind a command button that will update their table using this txt file.

[OBP]

It would be simpler to export/import it as an Excel file as it doesn't need any specifiactions. All the necessary data is in the Excel Worksheet.

[Norie]

There is another way to specify how a file is imported.

It uses a schema.ini file.

From help:


Specification NameThe specification name for the set of options that determines how a text file is imported, exported, or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini (javascript:HelpPopup('actip9.hlp','dvmsgSchema');) file, which must be stored in the same folder as the imported, linked, or exported text file.

[stanl]

I have to transfer the data from table to text file so that I can send the TXT file it to other users residing interstate. Then I have to come up with a code behind a command button that will update their table using this txt file.

This is perfect scenario for using Disconnected Recordsets (1) you can avoid issues with data types that might arise w/txt or Excel (2) by using ADO and updatebatch you can check for records affected and determine errors quickly ... I realize you can use ADO with txt or Excel, but then I'll circle back to #1 (3) Disconnected Recordsets can be stored in binary (.adt) format thus reducing filesize for transfer and offing a level of security. just .02 Stan