Automating a process to export an Access table as a .txt or .csv file

[annmariebette]

Help! I have this database which contains a table of data that I need to export so our Guidance counselors can utilize an online website to track student information, college application data and SAT scores. Ideally, I would like to streamline the process so that our enduser(s) can click a button which runs a process (VBA?) that makes a copy of the table, inserts a new field (Graduation_Year because it's currently not on our table however, the website needs it), make some property changes to some fields already on the table, run a query of some sort that populates the Graduation_Year based on the Grade-Level and then exports the table to a .txt or .csv for format. Now I already added macros to do this however, I would like to streamline this because the user(s) utlizing this do NOT understand Access. It's OK to use the procedures I came up with but a non-Access person might have difficulities. Any ideas or suggestions would be greatly appreciated. If you think you can help, I could send you a zipped database file attachment to review.
Thanks so much!!
Ann Marie Bette

 

[chadrj]

I think the best way to do this would be to create a query that 1) adds the needed graduation year column based on the grade level, 2) formats the columns as you need, and 3) uses the sendObject action in a macro to export the query to a text file in the desired location.

Let me know if you need any help with any of this

 

[robctech]

Not sure if this is what you are looking for. But my first suggestion would be to create a disconnected recordset of the access database. Add any fields you want to the disconnected recordset. Manipulate the data in anyway you wish then intialize the export in the format you desire.

Dim dbConn as ADODB.Connection
Dim dbRecs as ADODB.Recordset

dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={your access database"
dbrecs.Open "{Your request string}", dbConn, 1, 3
dbRecs.ActiveConnection = Nothing

dbRecs.Fields.Append "Graduation_Year", adDate

let me know if this is what you are looking for or if i'm way off.

 

[annmariebette]

Chadrj and Robctech,
I think I should email you a zipped test version of the database. I stripped out the real data and updated with some fake info. My email address is bettea@newegypt.us. Let me know your email addresses.
I think both of you have ideas that I could use. I appreciate your suggestions and prompt replies!
ANn Marie