By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,298 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,298 IT Pros & Developers. It's quick & easy.

Want updates in .mdb file to update other .mdb file too

P: n/a

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
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You can obtain an ODBC driver for DB2, and link the tables. You can create a
Query that will join the corresponding tables, set the join parameters so
that you return "all the records from the DB2 table and only those that
match from the MDB table", with criteria of Null on some critical field
(such as the primary key) of the MDB table. Then use that Query as the data
source of an Append Query to add the records to the MDB table. If you have
also retrieved the pertinent shared fields, they will be set when the record
is added.

You can execute the queries from code that runs at startup, so it will be
"semi-automatic". I suppose if you wanted it "fully automatic", you could
create an Access database with startup code, and schedule it to be run by
the Windows Scheduler -- that's a question for someone more knowledgeable
about the Windows Scheduler than I am.

Larry Linson
Microsoft Access MVP
HEATHER CARTER-YOUNG" <he******************@nps.gov> wrote in message
news:40*********************@news.newsgroups.ws...

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
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

P: n/a
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.


As someone else already addressed your main question, I justed wanted to
note this as an aside. I'm assuming that you named them db1 and db2
just for illustrative purposes in your post - if they're both Access
databases and actually named db1 and db2, I would recommend that you
select different (and more descriptive) names for them. The reason for
this is that by default, Access names new databases you create db1, then
db2, then db3, etc. We had a post not long ago from someone who put
hundreds of hours of work into a database, but he had not remembered to
rename it from db8. After some time away, he came back and deleted all
his "temporary" Access databases and accidently deleted the one he had
put all that time into.

Nov 13 '05 #3

P: n/a
Oops. I think I misread... I assumed you were talking about the IBM DB2
product, but perhaps you just meant a "second Access database"?

If, by "db2", you did mean "second Access database", then you need no ODBC
driver to link the tables... you can do that directly from Access.

I agree with John... it is much better to have descriptive names for your
databases... but I am not sure your post implied that they were actually
named "db1.mdb" and "db2.mdb" on your machine.

Larry Linson
Microsoft Access MVP
"Larry Linson" <bo*****@localhost.not> wrote in message
news:bQ****************@nwrddc03.gnilink.net...
You can obtain an ODBC driver for DB2, and link the tables. You can create a Query that will join the corresponding tables, set the join parameters so
that you return "all the records from the DB2 table and only those that
match from the MDB table", with criteria of Null on some critical field
(such as the primary key) of the MDB table. Then use that Query as the data source of an Append Query to add the records to the MDB table. If you have
also retrieved the pertinent shared fields, they will be set when the record is added.

You can execute the queries from code that runs at startup, so it will be
"semi-automatic". I suppose if you wanted it "fully automatic", you could
create an Access database with startup code, and schedule it to be run by
the Windows Scheduler -- that's a question for someone more knowledgeable
about the Windows Scheduler than I am.

Larry Linson
Microsoft Access MVP
HEATHER CARTER-YOUNG" <he******************@nps.gov> wrote in message
news:40*********************@news.newsgroups.ws...

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
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.