Access VBA object model

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm an advanced user of Excel VBA, but a newbie to Access VBA, and I'm still
trying to figure out the object model, method of selecting objects to perform
actions, etc. Can someone please either point me to a online resource with
explanations & code samples (other than the built-in Help files -- I've tried
those) or give me code samples for the following actions so I can try to
figure it out from the code? Thanks!

Opening a table
Running a predefined query against the table
Selecting all records displayed by the query
Deleting all records selected
Closing the query (if running the query above opens a new window on the
screen)
 
Matthew,

In Excel, macros are related to VBA code. In Access, macros have
nothing to do with VBA, they are a completely separate sort of object.
Nevertheless, below are some comments on your questions, relating to VBA
procedures in Access...

Matthew said:
Opening a table
Click to expand...

DoCmd.OpenTable "YourTable"
Normally tables in Access would be regarded as "background" storage
facilities, not for public consumption and not to be seen or used for
data entry/viewing/editing, so opening a table in code would be a very
rare occurrence.
Running a predefined query against the table
Click to expand...

DoCmd.OpenQuery "YourQuery"
or...
CurrentDb.Execute "YourQuery"
This would normally only ever be used in the case of an Action Query
(for example Append Query, Delete Query, Update Query, etc), and not
with a Select Query. The table does not need to be open at the time for
the query to be run.
Selecting all records displayed by the query
Click to expand...

Not applicable.
Deleting all records selected
Click to expand...

You would run a Delete Query for this. Either make and save a Delete
Query to do the job, and then run in code using DoCmd.OpenQuery, or else
like this...
CurrentDb.Execute "DELETE * FROM YourTable"
Closing the query (if running the query above opens a new window on the
screen)
Click to expand...

Running a query should never open a new window on the screen. If, for
some extremely obscure reason, you have a query datasheet displayed, it
can be closed using...
DoCmd.Close acQuery, "YourQuery"

As a final comment, I would guess un-learning your Excel stuff, and
resisting the temptation to relate to an Access database as if it is a
spreadsheet, will be a challenge for you :-)
 
Then let me ask two more questions:

1) Is this (the "Macros" forum") the place to ask Access VBA questions as
well as macros questions, or is there a better forum I should use?

2) I'm wanting to create automation to perform this series of actions:
Repeat for all text files in a folder:
a) import a text file in a certain folder into a table
b) run a delete query against the table to remove certain records
c) search for certain text in the table (that is, in the newly imported
data)
and replace it with the filename of the text file.
End loop

I know I can do step a) and probably step b) with macros, but I'm unfamiliar
enough with Access macros that I don't know whether I can do either c) or the
whole process with macros. Can I, or is VBA the best route?
 
Matthew,

Matthew said:
1) Is this (the "Macros" forum") the place to ask Access VBA questions as
well as macros questions, or is there a better forum I should use?
Click to expand...

No, your VBA questions are really more applicable elsewhere. For example..
microsoft.public.access.modulesdaovba
microsoft.public.access.formscoding
microsoft.public.access.modulescoding
and in the case of automation etc...
microsoft.public.access.externaldata
2) I'm wanting to create automation to perform this series of actions:
Repeat for all text files in a folder:
a) import a text file in a certain folder into a table
b) run a delete query against the table to remove certain records
c) search for certain text in the table (that is, in the newly imported
data)
and replace it with the filename of the text file.
End loop

I know I can do step a) and probably step b) with macros, but I'm unfamiliar
enough with Access macros that I don't know whether I can do either c) or the
whole process with macros. Can I, or is VBA the best route?
Click to expand...

You can do it all with macros. But VBA is the best route.
 
Back
Top