SearchTwo or More Back-End Databases With One Front-End Query Inpu

[Guest]

I would like to see if anyone knows how to query two or more databases with
having to enter the result I'm search for only once. For example I have
back-end databases at maximum storage capacity and other with the remainder
of the data that didn't fit. Now I have an account that I need to find and
it could be in either back-end databases.

 

[Guest]

Hi Robert,

I suppose you could use a Union query if absolutely necessary. You can
create a this type of query in the SQL window, since it is not supported in
the query designer. Something like this:

SELECT Field1, Field2, Field3
FROM Table1
UNION <---or UNION ALL
SELECT Field1, Field2, Field3
FROM Table2
ORDER BY Field2; <---if, for example, you want to sort by field2

I'd be asking why the database is so large to start with. I'm sorry to ask
the obvious, but have you tried compacting the databases (Tools > Database
Utilities > Compact)?

Your job will be a lot easier if you can get all of the data to fit within a
single table. Do any tables include embedded OLE Objects (pictures,
documents, etc.). If so, there are more efficient mechanisms of storing such
data without using OLE Embedding technology.

Which version of Access are you using (Access 97 or 2000 or later)?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I would like to see if anyone knows how to query two or more databases with
having to enter the result I'm search for only once. For example I have
back-end databases at maximum storage capacity and other with the remainder
of the data that didn't fit. Now I have an account that I need to find and
it could be in either back-end databases.

 

[Guest]

Thanks Tom, I will try it...
To answer your questions, My clients needed a way to view accounts that had
a rebill done (which there were a large volume of accounts) so that in case
the customer called our phone center the were able to answer any questions
for our customer representives had from the customer (we doubt we will ever
have one call since they were all credits). This was the fastest and most
cost effictive way to get the information to clients since our IT department
would have to prioritize the job and who know when the client would have
recieved it. Yes, I have compacted the databases which one is 1.92 GB and
the other is 1.51 GB. Data is stored in one table in each database with the
same field names and no OLE objects. I used MS Access 2000 for these
databases. If we have any future large volume rebills again I will be using
MS Access 2002 since this is our most current version.

 

[Guest]

Hey Robert, I'm in no way an expert with dealing with this much data in
Access so there may be a better method.

With that said, have you tried using recordsets? If you haven't and you
need assistance with it, post back and I'll help you out where I can.

 

[Guest]

It sounds like you might be better off using MSDE to store your data. This is
a cut-down version of SQL Server, and is included in the Developer versions
of Office.

http://www.microsoft.com/downloads/...D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en

You might also try downloading the new SQL Express:

http://www.microsoft.com/downloads/...3A-9D85-4734-B1FD-318FB83B0D29&displaylang=en

Of course, either of these will require a learning curve, but I think given
the amount of data that you have, you will be better off to go this route.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thanks Tom, I will try it...
To answer your questions, My clients needed a way to view accounts that had
a rebill done (which there were a large volume of accounts) so that in case
the customer called our phone center the were able to answer any questions
for our customer representives had from the customer (we doubt we will ever
have one call since they were all credits). This was the fastest and most
cost effictive way to get the information to clients since our IT department
would have to prioritize the job and who know when the client would have
recieved it. Yes, I have compacted the databases which one is 1.92 GB and
the other is 1.51 GB. Data is stored in one table in each database with the
same field names and no OLE objects. I used MS Access 2000 for these
databases. If we have any future large volume rebills again I will be using
MS Access 2002 since this is our most current version.
__________________________________________

:

Hi Robert,

I suppose you could use a Union query if absolutely necessary. You can
create a this type of query in the SQL window, since it is not supported in
the query designer. Something like this:

SELECT Field1, Field2, Field3
FROM Table1
UNION <---or UNION ALL
SELECT Field1, Field2, Field3
FROM Table2
ORDER BY Field2; <---if, for example, you want to sort by field2

I'd be asking why the database is so large to start with. I'm sorry to ask
the obvious, but have you tried compacting the databases (Tools > Database
Utilities > Compact)?

Your job will be a lot easier if you can get all of the data to fit within a
single table. Do any tables include embedded OLE Objects (pictures,
documents, etc.). If so, there are more efficient mechanisms of storing such
data without using OLE Embedding technology.

Which version of Access are you using (Access 97 or 2000 or later)?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I would like to see if anyone knows how to query two or more databases with
having to enter the result I'm search for only once. For example I have
back-end databases at maximum storage capacity and other with the remainder
of the data that didn't fit. Now I have an account that I need to find and
it could be in either back-end databases.