Automation of table export to text

[marcchampagne]

Hi Folks,

Every Week I have a client who sends me a .MDB containing
3 tables, the table name/structures are the same each week, only the records change.

Each time I have to open the .MDB (with Access of course) and FILE-EXPORT-TEXTFILES-FIXEDWIDTH-ETC... so that I can use these 3 tables within other applications under UNIX.

Is there a way to have a VB module in a local .MDB (that I keep here) export tables from my client's .MDB?

I'm a 20 year veteran to top-down programming (C,BASIC,COBOL,ASSEMBLY,ETC...) and have a hard time making any sense of VB,FORMS,ETC...

A general example would help me get on the right track.

Thanks in advance.

 

[THoey]

You could create a new Access DB that linked to the tables in the Users database (name and directory would have to be the same each time or you would have to rename/move it).

Create a Macro named "autoexec" that contains three calls to the TransferText action, one for each table that you wanted to export. You would have to create the Specifications for each of these so that you could set your preferences (fixed width).

Finally, your macro would need the Quit Action.

Then, whenever you get a new version of this MDB in, all you would have to do is place it in the appropriate directory, make sure it is named correctly and then run your MDB. It would open, run the three exports based on the other MDB, and then close.

I know this is kind of high level design, but if you have questions, just ask. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[jimmythegeek]

This will work, have done similar things several times.

1) Create a database with a single form that has a single button (this will be YOUR database).
2) Make sure you always put the other (HIS) database in the same place.
3) On the button, you will need several lines of code, the first few will be a TransferDatabase Method (see help). This will pull the data from HIS db into YOUR db.
4) This step only needs to be done once: Manually export each table, but save the Export Specifications (part of the export wizard). After you have saved them all, then you can finish the code.
5) You will need one TransferText Method line for each table using the export specs you have saved(see help).
6) Next you delete the tables as to have a clean db for next time.
6) Close the form and save it. Now all you have to do is open YOUR db, and hit the button. It will suck in HIS data, and then export it how and where you want it.

Example:
----------
This is on a button I named cmdProcess: (Red values need to be appropriately changed)

Private Sub cmdProcess_Click()
Const strDBPath As String = "C:\HisDB.mdb"
Const strExportPath As String = "\\Server1\Data\"

DoCmd.TransferDatabase acImport, "Microsoft Access", strDBPath, acTable, "Table1", "Table1"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDBPath, acTable, "Table2", "Table2"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDBPath, acTable, "Table3", "Table3"

DoCmd.TransferText acExportFixed, "SavedSpecName", "Table1", strExportPath & "Table1.txt"
DoCmd.TransferText acExportFixed, "SavedSpecName", "Table2", strExportPath & "Table2.txt"
DoCmd.TransferText acExportFixed, "SavedSpecName", "Table3", strExportPath & "Table3.txt"

DoCmd.DeleteObject acTable, "Table1"
DoCmd.DeleteObject acTable, "Table2"
DoCmd.DeleteObject acTable, "Table3"
End Sub
Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[THoey]

Same idea, except he uses VB instead of a macro and gives more detail. Looks prettier too... ;-)

Nice job Jim... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[marcchampagne]

I like both your ideas guys!

I have 1 more hitch to deal with...

How do I create an export specification that only exports
certain fields (not all)?

no matter what I try, the damned export wizard tells me that the number of fields in the specification does not match number of fields in table. (I say so-what! that's what I want!)

Arggghhhhh!

Thanks a million

 

[jimmythegeek]

Well there is always the following option. Drop the fields before you export the tables. If you notice the AlterTable methods below, you can drop as many fields from as many tables as you would like.

I would suggest:

1) Comment out the TransferText and Delete lines, and run the process. This will pull in the tables, and drop the fields you want.
2) Create an Export Spec with the trimmed down tables.
3) Uncomment the lines, and run the whole thing.

This will suck in the tables, drop the fields you don't want, and export the trimmed down tables.

=====
Private Sub cmdProcess_Click()
Dim db As Database
Const strdbPath As String = "C:\HisDB.mdb"
Const strExportPath As String = "\\Server1\Data\"

DoCmd.TransferDatabase acImport, "Microsoft Access", strdbPath, acTable, "Table1", "Table1"
DoCmd.TransferDatabase acImport, "Microsoft Access", strdbPath, acTable, "Table2", "Table2"
DoCmd.TransferDatabase acImport, "Microsoft Access", strdbPath, acTable, "Table3", "Table3"

Set db = CurrentDb
db.Execute "ALTER TABLE Table1 DROP COLUMN FieldName;"
db.Execute "ALTER TABLE Table1 DROP COLUMN FieldName;"
db.Execute "ALTER TABLE Table2 DROP COLUMN FieldName;"
db.Execute "ALTER TABLE Table2 DROP COLUMN FieldName;"
db.Execute "ALTER TABLE Table3 DROP COLUMN FieldName;"
db.Close

DoCmd.TransferText acExportFixed, "SavedSpecName", "Table1", strExportPath & "Table1.txt"
DoCmd.TransferText acExportFixed, "SavedSpecName", "Table2", strExportPath & "Table2.txt"
DoCmd.TransferText acExportFixed, "SavedSpecName", "Table3", strExportPath & "Table3.txt"

DoCmd.DeleteObject acTable, "Table1"
DoCmd.DeleteObject acTable, "Table2"
DoCmd.DeleteObject acTable, "Table3"
End Sub
Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[THoey]

To keep up with Jimmy, I will also suggest that instead of dropping the columns, you could run a make table query that just selects the certain fields that you want and then export that new table...

Seems like there is always 100 different ways to solve a problem. Until it is my problem... ;-) Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[marcchampagne]

Terry, Jim,

I created a new MDB,
linked in the tables from other MDB,
created 3 queries (to select wanted fields for export)
created 3 specifications
created "autoexec" macro with 3 TransferText

All works well, the only hitch being that I put a Quit in the AutoExec macro and can no longer open the MDB to make any changes.

Is there a god-given trick to prevent the autoexec from running?

You guys managed to Bend my mind!

ciao

Marc Champagne
XYZmarc@champagne.org
just drop the XYZ

 

[THoey]

Press the shift key as you open the MDB. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[marcchampagne]

Folks,

All is fine

Thank you both for you help! Marc Champagne
XYZmarc@champagne.org
just drop the XYZ