Accessing another mdb file in form of an access file form...

[Guest]

Hi folks,

I have a problem. I am new to coding in access forms and I have two tables
in two seperate databases of same schema.

Let me name the tables and databases. Let the database be DB1 and DB2, and
the tables be TBL1 and TBL2.

TBL1 and TBl2 have identical schema and TBL1 reside in DB1 and TBL2 exists
in DB2. I have search forms in both DB1 and DB2, I want to access TBL2 from
the search form in TBL1 so that if a record is not found in TBL1 it searches
in TBL2.

This problem arrised because the database file that I dealt with grew and an
access table can not handle more than 3 or 4 million records. And an access
file can not be more than 2 Gb in size. Hence I had to distribute the
database into two seperate database files. Can anyone recommend a better
solution as this may lead to a catastrophe as the database will grow very
rapidly in the future.

Thanks in advance.
Regards,
--Talha.

 

[Marshall Barton]

I have a problem. I am new to coding in access forms and I have two tables
in two seperate databases of same schema.

Let me name the tables and databases. Let the database be DB1 and DB2, and
the tables be TBL1 and TBL2.

TBL1 and TBl2 have identical schema and TBL1 reside in DB1 and TBL2 exists
in DB2. I have search forms in both DB1 and DB2, I want to access TBL2 from
the search form in TBL1 so that if a record is not found in TBL1 it searches
in TBL2.

This problem arrised because the database file that I dealt with grew and an
access table can not handle more than 3 or 4 million records. And an access
file can not be more than 2 Gb in size. Hence I had to distribute the
database into two seperate database files. Can anyone recommend a better
solution as this may lead to a catastrophe as the database will grow very
rapidly in the future.

Normally, you would not have the search code in either db1
or db2. Instead, the search mechanism would be in a front
end database (with no real tables) that has Linked Tables to
both db1 and db2. In this arrangement, searching either one
is no more difficult than searching the other. If you have
more than one user of your database, splitting it into front
end and back end databases is far and away the easiest,
safest and most managable way to go.

Even with your current situation, db1 can contain a Linked
Table to tbl2 in db2 so your search doesn't have to care if
tbl2 is in another database.