Millions of records

[ekrocker]

So, I know how crazy this is going to sound but of course - higher ups want it and don't want to spent any money and basically won't take no for an answer.

I have 4 tables across 4 Access databases 5+ million records total. I have a 5th Access database where I am linking in those tables and I have a search form (frmSearch). I have a union query running a query across all four tables based on text box inputs and combo boxes on frmSearch. Its a real pain and I know its because of the amount of data.

This is basically what the union query looks like. Is there a better way with staying with Access front and back end?

SELECT * FROM A_G WHERE npi = [Forms]![frmSearch].[txtNPI] or nppes_provider_last_org_name = [Forms]![frmSearch].[txtLastName] or nppes_provider_zip = [Forms]![frmSearch].[txtZipCode];
Union
Select * FROM H_N WHERE npi = [Forms]![frmSearch].[txtNPI] or nppes_provider_last_org_name = [Forms]![frmSearch].[txtLastName] or nppes_provider_zip = [Forms]![frmSearch].[txtZipCode];
Union
Select * FROM O_S WHERE npi = [Forms]![frmSearch].[txtNPI] or nppes_provider_last_org_name = [Forms]![frmSearch].[txtLastName] or nppes_provider_zip = [Forms]![frmSearch].[txtZipCode];
UNION Select * FROM T_Z WHERE npi = [Forms]![frmSearch].[txtNPI] or nppes_provider_last_org_name = [Forms]![frmSearch].[txtLastName] or nppes_provider_zip = [Forms]![frmSearch].[txtZipCode];

 

[plog]

Build a UNION query first, then write a query on top of that to apply criteria:

subQ:

SELECT * FROM A
UNION ALL
SELECT * FROM B
UNION ALL
...


MainQuery:

SELECT * FROM subQ WHERE (Your Criteria Here)

Additionally, you might even run a MAKETABLE query on subQ and make it a local table. Then you have MainQuery built on top of that local table. The caveat with this is that you would have to run that MAKETABLE before you run queries so that you refresh the data when necessary.

 

[Galaxiom]

Why do you have the tables in separate databases? Five million records is not a reason in itself. I have had nearly 10 million records in an Access database and it worked fine.

If the data is approaching the Access 2GB limit then you should move it to a database server. The free version of MS SQL Server can hold 10 GB and still be connected to Access.

Also, what you describe sounds like a flat file structure. Generally this is not a optimal way to hold the data.

 

[ekrocker]

Its the 2gb database size that's causing issues. The data is a directory of providers - listed in about 14 Excel files - each have the same columns. As I started importing each Excel spreadsheet into Access - into one table, the file size grew rapidly. I'd be set with just one table. I even tried to save the Excel files in the binary format which dropped the size of each Excel file thinking that might help out when importing to Access but it did not.

 

[Galaxiom]

They must be big records to reach 2GB with only a million records. Could you show an example of a record?

Is there a lot of bulky repeating text data among the records? This can be restructured to reduce the storage requirements.

Are numbers being recorded as strings. This uses more storage.

If you have imported then deleted data the size will continue to grow because space is not recovered until the database is compacted.