Large table

[tonydepo]

Hi, I could use some help. I have several databases with one table in each database that holds over 3-4 million records. I still enjoy the ability of using access for my forms. queries etc, but having multiple databases due to one large table in each is causing a headache. I have to constantly relink that one table every time a new month goes by. Does anyone have suggestions on something that can hold a lot of records for one given table? I tried SQLDB, however when I tried to append the next month's table it is slow as heck.

Also, I have a table called "maindata" which has over 3-4 million records which is the monthly records I need to query on. However, I have the same table name for each separate DB due to the 2 gig limit. Is there a way to link these databases by the same table name so I can run my frontend query to capture all of these months instead of performing separate queries in each database?



Thanks

[ranman256]

you may need to move up to SQL server to hold ALL your tables & data.
no limit.

[Welshgasman]

I would have thought you could link to the other dbs and perform a union query?
Not ideal, but......

[June7]

Can link to multiple db tables having same name, just assign each link a different name.

Methods to consider in your process:

1. modify query object manually or with VBA using DAO QueryDefs

2. build a new query for each table

3. VBA change RecordSource property of form/report to reference desired link table

4. UNION query (which would have to be modified for each new table link) and reference that query and apply filter criteria (no idea how a UNION of multiple tables with millions of records would perform)

5. have only one link and VBA to modify link connection string to change source database

If you don't need to use data from multiple db sources at same time, I would lean toward #5.


SQLServer Express is free but limited to 10GB.

I never heard of SQLDB - is that actually a database product? Who markets it?

[tonydepo]

How would a UNION of multiple tables with millions of records perform?

SQLServer Express is free but limited to 10GB.

I never heard of SQLDB - is that actually a database product? Who markets it?

Azure SQL Database – Managed Cloud Database Service | Microsoft Azure

[June7]

Okay, have heard of Azure, never dealt with. I modified my other post after you read it - might review again.

[Welshgasman]

I would have thought you could link to the other dbs and perform a union query?
Not ideal, but......
Edit: not sure what goes on here, but sometimes I come back to a thread and it looks like I have forgotten to post the reply, hence dupe posts.

[tonydepo]

Thank you. Tried a Union query, but I received the 2GB limitation. I really believe I have to find another platform to host the back end piece with this amount of transactional data. Thanks for the help!!

[Welshgasman]

Thank you. Tried a Union query, but I received the 2GB limitation. I really believe I have to find another platform to host the back end piece with this amount of transactional data. Thanks for the help!!

More than likely.
Look at MariaDB and MySQL as options?

Good luck with your project.

[Minty]

Azure SQL is something I am familiar with.

Bulk updates from Access to Azure can be slow if lots of records are involved.
What type of file is the source for the new data each month?

There are better more efficient ways of importing bulk data than doing an update to a linked table.

Out of interest do you have many indexes on your main table?