How to specify objects/tables in Access?

  • 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 method of specifying objects to perform actions
against them, etc. (for instance, I've already learned that, unlike Excel, in
Access you don't "select" the object you're about to act on).

Can someone please either point me to a online resource with explanations &
code samples for the object model in Access VBA (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?

I'm wanting to create a script to perform this series of actions:
For each text file in a folder:
a) import a text file in a certain folder into a certain table
b) run a query against the table, and remove all records matched
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 it was
imported from.
Loop

I know how to do the looping and how to pull the filenames out. I've
already got a semblance of code working for step a), but not sure how to run
the query and delete records in step b), nor how to specify the table as the
target for the action in step c).

Any guidance much appreciated -- Thanks!
 
Matthew:

Access tables and queries are referenced in code through the TableDef and
QueryDef objects which are a part of the Microsoft DAO Object Library. You
will need a reference to this library, obviously, to use these objects in
code.

Once the Table and/or Query has been created you can reference the them with
code similar to the following:

Dim qdf as QueryDef
Dim tdf as TableDef
Dim db as Database

Set db = CurrentDb

Set qdf = db.QueryDefs("MyQueryDef")

The code for accessing a TableDef is the same except that you reference the
TableDefs collection. For example:

Set tdf = db.TableDefs("MyTableDef")

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm an advanced user of Excel VBA, but a newbie to Access VBA, and I'm still
trying to figure out the method of specifying objects to perform actions
against them, etc. (for instance, I've already learned that, unlike Excel,
in
Access you don't "select" the object you're about to act on).

Can someone please either point me to a online resource with explanations &
code samples for the object model in Access VBA (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?

I'm wanting to create a script to perform this series of actions:
For each text file in a folder:
a) import a text file in a certain folder into a certain table
b) run a query against the table, and remove all records matched
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 it was
imported from.
Loop

I know how to do the looping and how to pull the filenames out. I've
already got a semblance of code working for step a), but not sure how to run
the query and delete records in step b), nor how to specify the table as the
target for the action in step c).

Any guidance much appreciated -- Thanks!
 
Access development usually involves working with multiple object models,
Matthew. There's the Access object model, which is commonly used to work
with user interface objects such as forms and reports, and there are the DAO
and/or ADO object models, which are used to work with data objects such as
tables and queries.

To execute action queries, if using DAO investigate the Database and
QueryDef objects. If using ADO, investigate the Connection and Command
objects. For retrieving data, using either data access technology,
investigate the Recordset object.
 
Back
Top