Design Question

[Guest]

I have just inherited an Access 2003 database at work. Once a month I am to
open the database and run a macro. The macro deletes a table, then imports a
2Meg text file to 'recreate' the deleted table. The table has ~100 columns,
of which we only really want/need about 12. I then do a bit of analysis,
print several reports, and close the file until next month.

I'm pretty new to Access, but there must be a better way. I don't mind
manually running the macro. But what else shoudl be changed? Can Access
query a text file, or am I really forced to import the entire thing? Should
I just import the entire thing and then delete the columns I don't want?

Looking for an opportunity to learn here. Thanks.

 

[Larry Daugherty]

You sound like an enthusiastic neophyte Access developer. Welcome to
the world of Access.

Your question is kind of open ended. I'll try to give some useful
suggestions and start you with some resources to pursue.

When you say "macro", you need to be specific so we know that you
really mean *Macro* in Access terminology. The first hurdle in
terminology is that the VBA code that's called "macro: in all other
Access platforms is referred to in Access as VBA or "code" or
"procedures. In Access there is a kind of interpretive facility
provide under the name "Macro". If you use the term "macro" in Access
it is understood that you are referring to Access's "Macro".
Professional Access developers rarely make much or any use of Macros.
There are good and sufficient reasons that you should learn VBA from
the outset. As you are learning you might try something as a macro
just to see if it's possible. You probably won't get a lot of help
debugging macros if you get into troubles with them.

That being said, there is a particularly useful convention provided in
a specially named macro. If there is something you want done as soon
as your application comes alive, put it into a macro named Autoexec.

The reason I mentioned that is that you have some very specific steps
you go through every time you open your application: Delete a table
in your application, transfer spreadsheet from an Excel workbook of a
given name to a table of a given name. You could have all of that
happen for you by placing either one or several lines in Autoexec.
One of the things a macro can do is "RunCode" if procedures have
already been written. By doing the above you would have the first
part of the operation complete before you have the application
completely open. Enough about Access macros.

If the analysis and subsequent decisions you make can be reduced to
algorithmic statements then you can write code to reduce the work to
just a little data entry and button clicks. I haven't seen what you
do but know it's so. That includes printing the results.

There are some great resources for newcomers to Access:

microsoft.public.access.getting started
microsoft.public.access.tablesdesign

www.mvps.org/access

There are lots more resources available when you want them.

Post back with issues.

HTH

 

[Guest]

Larry offered some good advice. Let me extend that a bit.
As he said, most professionals don't use macros. There are some rare
exceptions.
For example, using a RunCode action in a Macro named AutoExec will run the
code identified in the Macro as soon as the application is opened. Note it
has to be a function. It will not run a sub. However, I would only use an
Autoexec macro if there were no form to present on opening the app. This
appears to be one of those cases.

And before we continue, to help you out, you will need to know that if you
want to open an application that has either a startup form or an Autoexec
Macro without the code running, hold down the shift key when you open the app.

Now about your particular situation. You can actually set it up so that you
don't have to do anything. Use the Windows Task Scheduler to run it when you
want.

Deleting tables and using Make Table queries is a horrible thing. It is
sloppy and is a major contributor to database bloat. Here is a better way
and you will learn a lot doing it.

Now to get only the fields you want and have them formatted correctly, you
will want to create an Import Specification. To do this, you will need to
run the import manually once. From the main menu, Files, Get External Data,
Import. In the File Type dropdown, select Text Files. Now navigate to your
file. Once you get the Import dialog, click on the Advanced command button.
You will get another dialog that will show all the field in the file. Now
you can give them names, select to import or skip, and what the format should
be. Once you have all the parameters set like you want, click on Save As and
give it a name. You will use this name when you do the import.

Now, create the table you will use with the fields you want. The field
names should match the field names in your Import Spec.

Now we have the pieces and are ready to do the actual work.

First thing is rather than delete the table, we just remove the data from
it. This is how:

Currentdb.Execute("DELETE * FROM MyTable;"), dbFailOnError

Now we import the data from the text file. We do that with the TransferText
method. You will need to read the VBA help topic on TransferText and select
the Method version. Some of the options you select will be based on the type
of file. But to suffice it to say you will import the text into the table.

Best of Luck.