Advice on writing macros for newbie

[Ann Scharpf]

Hi, I am fairly new to Access. Am working on my third database in Access
2003. This one is used to analyze huge data files for error conditions. I
hve a group of users who are not very technical and they need to clear out
the data table and import new data on a daily basis.

I had created a switchboard for them and, thus far, had added the four
validation reports to the switchboard. Then I was going to add the delete
query that wipes out the data so they can start fresh. There is no way to
add a query to the switchboard so I thought, Well I'll create a macro. I was
dismayed to see you can't RECORD a macro, you have to WRITE a macro. I don't
know Visual Basic. All I need for this macro to do is to run a query called
"RemoveData."

The other macro I need to write will import a semicolon delimited file.
Skip the first column and import the second to an existing table called "tbl."

My question for you is, do you think a non-programmer has a prayer of
figuring out how to write these two macros? If yes, can you direct me to a
good resource for learning about this quickly? I've been poking around the
help files and they don't really seem to have a "getting started guide." I
am thoroughly flummoxed.

Thanks for any help you can give me.

Ann Scharpf

 

[Steve Schapel]

Ann,

In the Database Window, select the Macros tab and click New. In the
Action column, enter OpenQuery, and then in the Query Name argument at
the bottom, enter RemoveData. Save and name this macro. Then enter the
name of the macro in the On Click event property of the applicable
command button in your switchboard. You might want to also put a
SetWarnings/No action in the macro, before the OpenQuery, in order to
suppress the display of the action query confirmation messages.

 

[Ann Scharpf]

Steve:

Thanks for answering so quickly – and so EXPLICITLY! Your directions were
very easy to follow, and they worked like a gem. That interface is pretty
easy to use. I was afraid I'd have to start writing VBA directly.

I do have a couple of "follow up" questions.

1. This is not really a macro question but it follows on from my delete
query. When I added data the second time to the "tbl" table, the AutoNumber
ID started with 14623. Is it possible to reset the value back to 1 when you
delete the records? (Again, preferably using an automated method so my users
don't have to touch the actual table.)

2. I don't see any way to do the File > Get external data through this
interface. Aside from nitty-gritty VBA, is there any other way for me to
automate this process?

Thanks again!

Ann

 

[Ann Scharpf]

Steve:

Never mind about question #1 (resetting the autonumber value). I looked
some more in the help and found a method for doing this. So now I'm just
wondering whether there's a fairly straightforward way to automate the data
import.

Thanks again for your help.

Ann

 

[Ann Scharpf]

OK, now I'm looking like a dope. I found help on "change the starting value
of an AutoNumber field" and thought that would do the trick. But this is
apparently just to set a starting value to something other than 1. I
followed the directions and appended a record with ID "0" to my table. The
next record added was not "1" as I had hoped but 14626.

So, we're back to the original 2 questions. (Two posts above this in the
list.)

 

[Steve Schapel]

Ann,

... That interface is pretty
easy to use. I was afraid I'd have to start writing VBA directly.

Macros have nothing at all to do with VBA.

1. This is not really a macro question but it follows on from my delete
query. When I added data the second time to the "tbl" table, the AutoNumber
ID started with 14623. Is it possible to reset the value back to 1 when you
delete the records? (Again, preferably using an automated method so my users
don't have to touch the actual table.)

The database has to be compacted after the records are deleted.
Tools|Database Utilities menu. It is not really possible to do this
with a macro. On the General tab under the Tools|Options menu, you will
find a 'Compact On Close' option, which may be applicable, if you can
delete the records from the holding table before exiting the database.
Depends a bit whether this table is in the local database file, or in a
linked backend database. But the real question is "Why?" Normally the
value of an Autonumber is irrelevant, as long as it provides unique
identity.

2. I don't see any way to do the File > Get external data through this
interface.

Use the TransferText action. You will need to go through the proces
manually via File|Get External Data, and then in the final screen of the
import wizard, click the 'Advanced' button, save your set up as a
Specification, and then enter this into the Specification Name argument
of the TransferText action in the macro.

Thanks again!

You're very welcome.

 

[Ann Scharpf]

Originally, I was trying to get an accurate number because I felt it would
help my end-user to LOCATE the information in the huge text file that the
database is analyzing. I felt they would need to know that the text string
occurred at line 2,500 out of 14,800 or whatever. Turns out that the
software product they're using allows them to find the data by a whole
different method, so they don't need to know the location after all.

I will try the method you recommend for the data import.

Thanks again for your help. I learn so much on these community newsgroups
pages.

Ann