An automated way to create a text CSV file from a MS *.MDB file.

Hello guys.  I am asking this question for "Regialtip01".  He is a new expert here and will sign up in just a few minutes.  He will be the one responding to this question.  I will award the points when he accepts an answer.

From within an application a MS *.MDB will be created.  I will then copy the MS *.MDB to working directory and at that time I will need to find a way to automate a way to create a text CSV file for the MS*.MDB file in that working directory. I want to run a autoexec macro to execute the function.

I have four tables within this MDB file and I want to create this text CSV file to hold information from all four
tables, and save the CSV file to a certain directory.
LVL 2
WonHopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Regialtip01Commented:
I am now registered
0
JimMorganCommented:
I thought that EE frowned on creating a question just for one expert to answer.  I know that I've been chewed out about it before.

BTW, Regialtip01, welcome to EE.

Jim

0
Regialtip01Commented:
Hello Jim...This is WonHop.  It is not as you think it is.  Regialtip01 and I work together.  He needs an answer to this question from anyone.  I asked the question under my name to show him how it works.  Then he went and signed up with EE.  He will be the one responding to the comments and testing the answers given.  When he finds an answer that is acceptable, I will be the one to award the points or I will delete the question and he can put another question for the person who gives an acceptable answer.
Sorry for the misunderstanding.
:o)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrianWrenCommented:
If you are just creating the *.mdb, where did the data come from?

I think that I would think seriously about temporarily linking the tables to the current database, (the one from within which you indicate that you will create it), create the text file from within the current db, and then dissolve the link...

More details would be right helpful.

Brian
0
JimMorganCommented:
:o)  I totally misread the information in the question.  Now that you explain it better, I totally understand and it makes sense...

OK, so you have an MDB which you will be copying to a target directory.  When the MDB is opened (each time?), an autoexec macro will call a routine which some how joins the data from 4 tables into one large CSV file which will then be saved to another directory for archive purposes.  Is this right?

Create an Autoexec macro and call a procedure which will do what you want.  When I get back from running an errand, then I will give you some more details, if this is what you are trying to do.

Jim
0
WonHop1Commented:
Jim   Yes...the database will be copied to another directory every night at a certain time via a bat.file.
Then another bat file will open the database.  What he is looking for is the code to create the CSV file, put the information from the tables in it and Save it.

Thanks...BTW...this is WonHop from Home.
0
JimMorganCommented:
The one thing that is not clear is if the 4 tables all have the same layout.

I'm going to assume that they do not and that you want to put all 4 tables into one CSV file.  As delimiters between the 4 tables, I'm going to put out the field names first, then the data.

    DoCmd.TransferText acExportDelim, "Table1", "Output1.Txt", True
    DoCmd.TransferText acExportDelim, "Table2", "Output2.Txt", True
    DoCmd.TransferText acExportDelim, "Table3", "Output3.Txt", True
    DoCmd.TransferText acExportDelim, "Table4", "Output4.Txt", True
    Shell "Copy Output1.Txt+Output2.Txt+Output3.Txt+Output4.Txt FinalCSV.Txt"

That is all the code you need.  However, you would change the table names and the finalcsv filename to what ever you want.

Jim
   
   
       
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WonHopAuthor Commented:
Thanks Jim...we will give this a try.
0
JimMorganCommented:
I'm sure that you will let me know how it turns out.  It does work as I almost always test these things out before I post them.

Jim
0
WonHopAuthor Commented:
Yes it does. I am working on it now.  I am just now getting to work on it.  I was having problems saving the file with concantanation.  I thought I might have to increase the points for another question...but I got it working just as your post came thru.  

I have not yet tried the Shell but it looks simple enough.

Thanks for everything.
0
WonHopAuthor Commented:
I am accepting your answer now, BUT first I want to also say "CONGRATS" and thanks for all of the help and advice you have given me and to all of the other experts here.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.