Connecting internal MS Access Tables

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

Guest

I have a db w/two main tables that that need to be related "one to one." The
1st table gets it's data imported entirely from another db. The 2nd gets its
data manually input when this db is opened and updated. This means that each
record in "Table 2" needs to have the SAME record ID as that which has been
imported into "Table 1." I could use some help in figuring out how to
develop code, modules, or whatever, to have the software automatically READ
the "widow" record ID in Table 1 and insert it Table 2, and then refresh the
link if possible. Thanks
 
Dave,

How do you know, and how is Access supposed to "know", which record in
Table 1 should be related to the newly entered record in Table 2? Maybe
an example would help?
 
Currently what happens is that in db#1 (Booking Record) I complete the new or
updated entry and then click on the export button which sends this data to
db#2 (Prisoner Records) where it creates a new record in the main table.
However this new record cannot yet be viewed in the main form as its data
source is a query that joins the main table with a second table, and of
course the new record in the Table#2 has not yet been created. The second
table allows additional data to be added to an individual record once the
initial data has been imported. Currently, in order to complete the process
I must manually open the main table, read the Table ID#, then open the second
table and insert this number in the Table#2ID column of a new row. Now the
form will "see" the record and allow it to be updated and added to. I could
have simply added the additional fields I needed to the main table but would
likely run out the number of fields allowed. Besides, I would like to
preserve the initial data imported (sort of an archive) seperate from the
data added later. Hope this makes more sense.
 
Dave,

I don't understand why the ID field from the first table is not included
in the data that is exported to the table in the second database?

Your statement "would likely run out the number of fields allowed" would
indicate that you need to review your table design. Can you give some
examples of the fields in this second table in the second database?
 
Steve,
Sorry, I guess I am not explaining this very well.

The first database collects initial data. This data is then exported to a
2nd database for archiving and deposited into its primary table (including
the ID field).

Then later the user opens the 2nd database to add additional data to the
archived record. This second database resembles the first one except that
its main form has many more fields. All these additional fields contained in
the form are actually from a "second" table in this 2nd (archive) database.
This form can see all these fields because of a query that joins both tables
(The primary one that the original data was improrted into and the one that
will be used to collect additional data later).

The problem stems from the fact that these two joined or linked tables
obviously need to have the same recordID number and presently only the
primary table has this number that it gets upon importing this record.

What I think I need is some kind of code and/or macro/module that performs
the task of automatically creating a new blank record in this second table
with the same record ID# as the newly imported record in the primary table.
Id guess that I would also need some code to cause the db to requery/refresh
the data so that this new record would become immediately visible and
updatable.

Currently, after importing into this second database I have to manually open
the primary table, identify the newly imported record ID#, then open the 2nd
table and create a new record and enter the previously identified record ID#.
Then close the database and reopen it to be able to get the form to view the
record and then be able to add data in the additional fields (which of course
are actually in the 2nd table).

It has been suggested that I simply combine these two tables in my second
(archive) database. However, there appears to be a limit to the number of
fields one can have in a given table, thus I built a second table for the
additional fields. But as you can see, the records of each of these two
table need to be joined "1 to 1."

If I were only entering data through the form itself and was using the
autonumber feature in both tables, again I probably would not have a problem.
But since table #1 only gets its data upon importing and table #2 must be
created at the same time and with the same record ID#, I cannot figure out
how to make this happen.

Thanks.
 
Dave,

There is a maximum of 255 fields in a table. As I mentioned in my
earlier reply, if you are coming up against this limit, it is almost
certain there is a problem with your table design. I would strongly
encourage you to post back with some more information, with examples, of
what some of these fields are, so that someone can asssit with this aspect.

As regards the problem of adding data to the "second table" related to
the newly imported records in the "first table", this is not a job for a
macro or code. All you need is a form based on a query which joins the
two tables with a Left Join on the primary key field. This will then
show all of the "first table" records, and will create a related "second
table" record as soon as you start to enter data in any of the "second
table" fields. Hope that makes sense.
 
Back
Top