I am trying to figure out the best way for the process below, might need to post in the Access forum though.
I have a database and need to export multiple different files and upload them on a FTP, should I export them into excel first then switch it over to an txt or can I go from Access to txt. Assuming there is a program that can automate the export straight to a txt.
This would be a daily task possibly more than once a day.
What is the format of the data in Access? What is the desired txt format? In particular, what is your Access database schema like? Is it many tables with foreign keys, etc., that have to be joined to generate the data?
I am not seeing any reason to add an extra step of going through Excel, but I'm pretty rusty on Access.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Not sure what you mean by the format of data in Access, teaching myself as I go. The txt format needs to be three comma-delimited ASCII Text files.
First file will have max of 4 tables. (details)
Second file should only have one table (this table is also in first file) (header)
Third file one table as well (will be just one field from same table in second file)
Each file will have to be saved with today's date then an incremental number marking the number of files exported each day followed by set wording. Example below.
First export 03-31-2021-1Header.txt 03-31-2021-1Details.txt 03-31-2021-1Approved.txt
Second export 03-31-2021-2Header.txt 03-31-2021-2Details.txt 03-31-2021-2Approved.txt
So each table in your Access schema is completely independent set of data, and does not link to the other tables? For example, typically a database table will have something like this:
Street Address Zip Code 123 Main Street 12345 99 Hollywood Blvd 90210
Zip City State 12345 Schenectady NY 90210 Beverly Hills CA
In the first table the zip code is a foreign key to the second table--it is used to look up the city and state. So if you wanted to dump a file with full addresses you would have to JOIN the two tables to get street, city, state, zip.
Access has a feature to export a table to csv format. So I think you just need to automate with VBA to do this daily.
Yes I understand the basic table structure. My first export will have 2-3 foreign keys that I have to link in order to get the correct data. So I need to read up on VBA's?
You will have to use SQL statements to extract this data. I have a lot experience with SQL but not much with Access so I'm probably not your guy. But it sounds to me like, yes, this will involve VBA, unless there is something built in to export a SQL dataset to a csv file.
I can move your question to the Access forum if you'd like.
Yes please, thanks.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
But you can’t schedule anything in access correct?
Access does support VBA and you could set up the export in VBA. I can explain how a timed event would work, but I don't know the code to extract the data. Also, Access would have to be running for the VBA to run. Another option is to schedule a Windows job to start Access and kick off the VBA, but again that is something I don't have much experience with.
Got my export in the correct format, through trail and error, couldn't tell you what was going on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks