jacksonpt

I've got 2 access tables - one is a template, the other is essentially a data file. Both have the same fields in the same order... the big difference is the template table has field lengths set for each field, the data file doesn't.

I need to find a way to get the data formatted. I don't care if I format the fields in the data file or copy the data into the formatted template table. I just need the easiest/quickest way possible.

Which brings me to the next question - or perhaps the more appropriate first question. Since I'm going to have to do this every day for seemingly the rest of my life... can I setup some type of macro to do all this for me? Any good tutorials that you know of about setting up/using macros?

edikp

I use Access quite extensively at work. If you right-click on the table and go to Design View then you will be presented with a page where you can do formatting of the data.

You could create a report so the table would look nicer and easily readable if you wish.

jacksonpt

ok... maybe I need to give you more info...

I've got a bunch of data stored in a MySQL database. Via ODBC, I import data from that DB into Access. That imported table is the data table I mentioned above. I need to find the easiest way to set the field length for every field in that data table, then export it as a fixed length text file so I can import it into another DB - access is only being use as a conversion tool at this point.

I know I can format the data manually in design view - but every day a new data table is going to be created, and each one is going to have to be formatted. Thus my desire to find a less tedious solution.

edikp

Seems to me that you are going to need to utilize VBA to create a make-table query from the data table and do the formatting for you.

Hopefully I can provide you with an idea through the following example. I have a similar situation where I needed to create a daily activity report of my technicians. The log is a multiple-tab Excel spreadsheet. Each tab represents one technician and shows his activities for the day (destination, travel duration, lunch duration, etc.). The report is in Access because it needs to show the daily efficiency of each technician and department as a whole in a form that's easily digest-able by the management. Easily digest-able by the management is the keyword here.

The data is pulled off of Excel through a form in Access. The form is coded in VBA to pull data of the Excel spreadsheet every day at a specified time. Data from each technician is dumped into one big table for the whole department through an append-data queries. This same form then uses this big table (also through VBA codes) to create a report through queries (to perform calculations, formatting, etc.).

Maybe I sound kinda vague here for trying to help you through giving this example. What I'm trying to provide you is steps I've taken for my solution to my needs and hopefully this triggers some ideas in your part to solve yours.

Some books that I've found helpful in learning VBA is Beginning Access 2000 VBA, published by Wrox.

jacksonpt

grrrr... I was hoping to avoid VB, but it looks like a necessary evil that will pay off in the long run.

Thanks.

tulsa_97SR5

You could switch from your make table query, to a delete query to empty your local table, then an append table to add the current data. That way you'd only need to setup the local table once. If you are worried about data in the remote table possibly being longer than allowed you can use the left(remote_field_name, 50) function to truncate the data.

ldivinag

dude...

quit killing yourself with mysql->access->???? conversion.

what is your final product?

state that and lemme see what you need...

ldivinag

you know, i'm reading your orignal post again.

and one thing to consider about staying all in mysql is the flexibility of it. granted access has as much.

but you are going through OBDC. then once again to access.

be as specific as to what you want. it could be as simple as one SQL statement.

mysql has tons of "features"... if you wanna call it that.

for example, mysql can store a date and time into one field. but lets say you just want the date out of that. no problem.

you want the 15 rightmost character of a 50 char string? no problem again.

if you can, do a DESCRIBE TABLE <tablename> on mysql to dump a schema:

http://dev.mysql.com/doc/refman/4.1/en/describe.html