REQ Macro Assistance

Topic

New Reply

My macros’ skill level is just a bit above “doesn’t that have something to do with knitting” so any assistance here would be, greatly, appreciated.

I’ve got a year’s worth of dBase IV dbf, weekly, backup files (same fields..different values) that I want to import into Access. A separate table for each file is, probably, not the best way to go but I think it may be the easiest. The file naming convention is alpha numeric with the alpha portion being the same for all and the numeric being a two digit week code and a two digit location code. Total number of files is 156.

I’d like a macro that will pull all the files into Access, name the table (can be the same as the filename), delete three or four extraneous fields in each file, add a field “WEEK”, auto-insert the two digit week code for every record and then prompt for a filename to save as.

I only have two questions.
1 – can all or any of this be done?
2 – how?

Reply | Quote

Replies

Now I have a question: do you want to import the files into Access tables and them save them in an Access database, or are you going to export them back out again? Your “…then prompt for a filename to save as” confused me.

The way I would do this would be to link each dbf file in turn, rather than importing it. Then you could run a query on each linked dbf to append just the fields you wanted to a table in Access, and you could add the week code at the same time. To make it simpler, you could use the same table alias for each linked table and build queries based on the alias. Then the same query could be run for all of the tables, one at a time. This will work as long as all the tables have the same field structure.

Does that answer your question?

Reply | Quote

Hi Charlotte…

Thanks for the response.

I’d prefer to import the tables into Access and save as an Access db. I don’t want the original dbf files on my local drive and, while there shouldn’t be any modifications to the original dbf files, I wouldn’t want the Access db affected, if there were. The Access db is for analysis and won’t be exported. As I said, the tables are dentical…the only differences are the record values and the filenames which is why I need to add the “WEEK” field.

It was late and the end of a rough week when I composed the original post. Now that I think of it:
1) the “…then prompt for a filename to save as” is unnecessary since the Access db has to be created and named before anything else can be done…silly moi. Should have been just “save”.
2) it’d be nice to add a “REGION” field, as well
3) the fields I’d like to delete are the same in all tables

To clarify further, I’d like it to go like this, once the Access db is created:
1) import all the files in a particular directory…they’d all be dbf files
2) create a table from each dbf file
3) automatically name each table, from each source dbf filename, if possible or prompt for the table name, if not 3) delete the same three or four fields from each table
4) add “WEEK” and “REGION” fields to each table
5) automatically insert the “WEEK” and “REGION” values in each table, if possible or prompt for the values, if not…the values are the same for each record in a particular file but different from file to file…a file named ‘aaaa0101.dbf’ would become a table where the “WEEK” value for each record would be “1” and the “REGION” value for each record would be “1”. A file named ‘aaaa2703.dbf’ would become a table where the “WEEK” value for each record would be “27” and the “REGION” value for each record would be “3”.
6) “Save”

Whew…I’m getting a headache. I hope this gives you a better idea of what I want…it has me 8-

Reply | Quote

My personal view:

I would avoid having the files in separate tables. It would make it much easier to manipulate the data if they were in one table with an extra field “fldWeek” added.

You will need to import each of your files into a holding table, use an update query to fill in fldWeek, and then an append query to add the records to the main table. You will then run each of these steps in turn from your code.

Reply | Quote

G’Day Mate…(I think that’s Aussie for “Hi”?)

Thanks for the response.

As I said, “A separate table for each file is, probably, not the best way to go…”. There’s also the matter of the REGION field.

If you can help me with a macro that would pull all the data into one table, append WEEK and REGION fields and automatically insert the correct value in those fields for each record; it’d be a real help.

Reply | Quote

why not simply import the dbase table into your access database
JerryC

Reply | Quote

As I said, there are 156 that need to be imported. They’re identical except for the filename and data values. A field to identify the week needs to be added and a data value entered. Optionally, a field to identify the region and the data value entered as well as the same three or four extraneous fields deleted from each table.

If you can tell me how to do that “simply” and quickly, I would be very greatful.

Actually, I’ve been working on this all weekend and have got it to the point where it’ll do pretty well everything except delete the fields but it has, definitely, not been “simple”…for me, anyway.

Reply | Quote

At the beginning these things are not simple. The only way to learn is to do them. I am afraid some of us who have been working with programs like access for years can forget how strange they were when we first started. (e.g. the change from WordPerfect to Word)

I do not have the time at the moment to produce an answer for you and you seem to have cracked it anyway. Well Done.

Don’t bother to delete the extra fields in the code. Import the whole lot into one table and then modify that table to delete the unwanted fields.

Moral: Only get macros to do the boring repetitive stuff

Reply | Quote

hmmm…I disagree that that the only way to learn something is to do it. Vicarious learning works just fine for me. I’ve used Access for quite awhile but usually have no need for macros and this is the first time I’ve had any use for one that required thought or effort to produce.

Deleting the extraneous fields was, as I said, optional but, since there are several hundred thousand records, desirable. As far as I know, a macro would only be able to do that once the tables have been created. Moot point, though. I’ve accomplished what was required and the fact that there are a few unnecessary fields is something I’m the only one likely to know or be bothered by. I’m pretty sure I can learn to live with it.

“Morals? We don’t need no stinkin’ morals.”

Reply | Quote