Array Database

[Gav]

Hi,

Is it possible to link databases to avoid hitting the 2GB limit?
I figure if I could limit a database size to say 1.5GB and have the rest of the entries produced by the automated process spill in to the next linked database I could avoid the issues I'm having with the 2GB size limit.

The program that is creating the entries would need the name of the original file to access the results.



Alternatively, can entries in column b with a value < the top 100 entries be deleted while other entries are being created by the program?

[June7]

Yes, can use multiple databases to avoid the 2GB limit. Have data in 'backend' databases. Have a 'frontend' that links to the various backends. The frontend has queries, forms, reports, code.

What program is creating entries? It doesn't need to do anything except use the links same way as tables.

You can delete data if you really, really won't ever need it again.

[Gav]

So if I split the database how do I create the multiple back-end that will spill the entries for one table over say 2 databases. Will the front end recognise the multiple back end as the original table

[June7]

I've never had to do this. Not really sure the best approach to use.

Is one table causing the db to hit the 2MB limit?

I've never had to do this but I expect options are:

1. break data logically - maybe by calendar year - No, the frontend will not recognize the linked tables as the 'original' table.

2. have complete tables in separate database files

3. akin to option 1 - 'archive' old data and don't use the archive files - create records in a new file that summarize the archive data and continue with new data entry
This link talks about 'stock takes' for large inventory database http://allenbrowne.com/AppInventory.html

[ssanfu]

Is it possible to link databases to avoid hitting the 2GB limit?

You can have a FE link to two (or more) BEs where each can be 2 GB.
Lets say you have one table in BE1 and that BE is 1.5GB. BE2 has one table and is 1.5 GB in size. You can have a FE link to both BEs and have an effective BE size of 4 GB with 3 GB used.

There is no automatic method in Access of having data "spill over" to BE2 if BE1 reaches a limit. You *might* be able to write enough code to accomplish that, but I wouldn't want to try.

How much data do you have to import/store?
You might think about moving to SQL Express 2008 R2 SP2 or SQL Express 2012
The largest database supported by SQL Server Express has been increased from 4 GB to 10 GB.

You can download:
Microsoft® SQL Server® 2008 R2 SP2 - Express Edition
http://www.microsoft.com/en-us/downl....aspx?id=30438

Microsoft® SQL Server® 2012 Express
http://www.microsoft.com/en-us/downl....aspx?id=29062

Microsoft® SQL Server® 2008 Management Studio Express
http://www.microsoft.com/en-us/downl...s.aspx?id=7593


You would still link to tables in SQL Express the same as Access BE.

[Gav]

It is a part of a stock trading program. The formulas from within the program feed information to the access table. There could be 8-10 variables located within formulas with a range say between 1-20, there could be 20 stocks and data could be over a period of 12 months for example.

So you can imagine that there would be a lot of data all allocated to this one table.

Is it possible to have the Microsoft database sort and delete data while entries are still coming in from the program?
Or is there another option to consider such as SQL, if so where should I start?

[June7]

Import and delete actions would not happen simultaneously. Access can do only one thing at a time.

Did you ssanfu's post that mentioned MySQL?