HEATHER CARTER-YOUNG wrote:
Please help. I have two databases - one I'm designing that will be our
in-house data mgmt system (db1) and another that is a federally-mandated
system (db2). We must submit data from db2 but don't want to use this db
for our data mgmt needs. Some of the fields in db1 are shared with db2
but the structure and field names are different. When I'm
updating/creating records in db1 I want it to see if the record exists
in db2 (shared ID number), if not, create a record, then populate those
fields that are shared between the two databases (so that I only have to
enter data in db1). I need it to update in real time because
occasionally I will use db2 to enter additional data not captured in
db1. Note that db2 is a national standard and while it can be
revised/edited for in-house use, it's best not to. All code I might
write to perform this task needs to "live" in db1.
I'm new to VBA and advanced Access techniques so I don't know where to
begin. Thanks!!!
Heather
An Access database consists of objects; tables, queries, forms, reports,
macros, and modules.
Oftentimes people store all of their queries, forms, reports, macros,
and modules in one mdb (Access database). This is often times called
the front end. Another database may exist and all tables (data) are
stored in that database. This is called the back end.
If you want, you can store all objects in one mdb.
Anyway, from your front end mdb (db1) you would link to the tables in
the back end for your db1 tables if you decide to split into front/back
ends. From the db1 frontend you would also link to tables in db2. How
do you link?
From the menu, select File/GetExternalData/Link. A FileOpen dialog is
presented. Select the back end to link. Then select all tables to be
linked and press OK or Link.
Let's say DB1 has a table called Orders and DB2 has a table called
Orders. If you link to Orders in DB1, when the link is created, the
name will be Orders. If you link to Orders in DB2, when the link is
created, the table name will be Orders1. I would recommend that YOUR
table names in DB1 be different than table names in DB2. In this
example, you would name your Orders table in DB1 something like
OrdersDB1. This way you'll be able to differentiate which database the
link is pointing to.
The first thing you need to master are queries, then forms. I would
play around with the query builder. Try out the wizards. (Query/New).
Click Help from the menu and read as much as you can on Queries.
The real power of databases comes from your ability to create queries.
You can create forms too using wizards.
If you are new to Access and not a programmer, many things can be done
with macros. They are straightforward and provides a lot of power to
non-programmers. Read up on them. If you are on a row when creating a
macro you can always press F1 to get more info.
If you hang out here and lurk and read posts, you'll get a tremendous
amount of information. Lots of code examples are provided in these
threads and sooner or later you'll be answering questions for others
instead of asking them.
You might want to have a handy book around. I suggest going to Amazon
and searching for books on MSAcess. Look for books that are for
beginners and intermediates. Read the reviews. Pick on that seems to
cover the subject well and have a high reviewer rating with lots of reviews.
Good luck.
