Unable to spilt database .. at 1.2 GB file size.

[edmscan]

Ok .. here is the deal.

I have an Access database .. 1.2 GB in size and I cannot do anything with it.



I am worried about hitting the 2 GB file size limit. One of my tables is big, the others are small.

I cannot split the database ... Access will not allow me and gives me a "System Resource Exceeded" error when it hits the big table.

It is being run on a single pc .. with 1 user. There will never be a requirement for network access or multiple users.

But this one table will continue to grow over time .. with about 60,000 records added per day. There are 13.8 Million records right now. And NO I am not going to another product.

The performance is still very good .. it is not a performance issue.

So .. I have not used linked tables in a long time, but I understand that they also have the same 2 GB limit. Which really does not help me much .. as I need to run queries against the data and it would not be practical to have multiple tables or files.

Anyone have any thoughts ?

[edmscan]

Ok .. well managed to split the database manually. It is working fine ... but still does not resolve the file size issue.

I would like to have the database grow .. until it is no longer needed in about 14 months from now. So .. that would mean a total size of about 4 GB max probably based on the data already collected assuming that the data will be at a constant rate, and from what I have seen this will remain constant. (an increase of about 2.4 GB).

The benefit .. I am seeing a performance boost now with it split. The same query runs in 26.5 sec on the single database and 18.8672 sec on the split database. (that is with all items being freshly compacted)

Looking for any thoughts on how I can do this ?

[June7]

Seems you are already aware of options, which are multiple backends or another db product like SQL Server Express.

[edmscan]

Seems you are already aware of options, which are multiple backends or another db product like SQL Server Express.

I am doing a test with the multiple backends right now. I have not worked with linked tables for a long time .. but it is coming back to me. Due to the fact I only need to limp along for another year, using another product is not a viable option.

[edmscan]

Well .. ok, did my test with 2 backends which will do the trick to keep this thing going for the required about 14 months.

The only thing .. looks like I will be using a ton of Union queries to combine the records from the 2 tables. (So separate backends .. one for each of the identical tables (one with older data til near the 2 GB limit and one with the newer data til the 2 GB limit). I will probably stop adding to the initial table in a while and switch to the 2nd table / backend file)

Are the any other options mostly when it comes to queries ? I should note .. I never alter the data (in Access), I just import data from comma delimited files .. and run queries against the data. I do however make the odd edit in the original application but that will not affect in any way how Access will work.

[orange]

You haven't told us much about the records and what they represent. There may be info there to help with options.
If you have data that is time/date stamped, you could separate data into different backend tables. If the data has some other identifier/segregator you could separate on that, possibly.
I'm not sure why you have to have a number of union queries, but the devil is always in the details. And so far we've been talking in abstract generalities.
More info might get you some more focused and practical results.

A picture of your tables and relationships, and/or a few sample queries could get some options with some better performance.

June has already mentioned SQL Server Express.

[edmscan]

You haven't told us much about the records and what they represent. There may be info there to help with options.
If you have data that is time/date stamped, you could separate data into different backend tables. If the data has some other identifier/segregator you could separate on that, possibly.
I'm not sure why you have to have a number of union queries, but the devil is always in the details. And so far we've been talking in abstract generalities.
More info might get you some more focused and practical results.

A picture of your tables and relationships, and/or a few sample queries could get some options with some better performance.

June has already mentioned SQL Server Express.

The database is for a Communications System.

A brief overview .. 3 main tables.

Kevin .. contains the data for the individual users of the radio system. Sample record: (6800 records in total). I know that LastUpdate / LastDate and LastTime are duplcated, but it makes the import so much easier.

Radio	User	LastDate	LastTime	LastUpdate	HitsHex	HitsDec	Identified	Confirmed
1311	UNIDENTIFIED RADIO	2013-10-22	10:46:34 AM	2013/10/22 10:46:34 AM	0	0	N	N

Kevin Group .. contains data for the groups that the radios belong to. Sample Record: (480 records in total)

Group	Name	LastDate	LastTime	HitsHex	HitsDec	LastUpdate
65	ETS TEST	2015/02/14	3:00:55 PM	d2f	3,375	2015/02/14 3:00:55 PM

Transmissions: Containing data when a radio Transmits or changes channels. This is the HUGE table. Delete is only used to delete duplicates if any exist. Radio and Group are the FK to the Kevin and KevinGroup tables respectively. (13.8 million records)

Id	TransDate	TransTime	Radio	Type	TransType	Group	Confirmed	Delete
14878162	2015-02-14	10:44:41 PM	3984	TR	G	1131	Y

Sample from CSV file .. (this is for the transmissions and this is the bare minimum data needed for a record)

2015/02/12,11:32:58, 6598,TR,GD, 613,N
2015/02/12,11:32:59,16003,AF,G , 37,X
2015/02/12,11:33:00,10275,AF,G ,1645,X
2015/02/12,11:33:00, 6333,TR,GD, 590,Y

There are several small tables .. mainly 2 that are used. And one big table .. which is the one that is causing this, as it is constantly growing.

I have gotten around the size issue (2 backend files) ... so that will work just fine. The size issue is really in regards to ONE table. It is very big .. right now 13.8 million records (about 60,000 added per day).

So .. right now I am using 1 FrontEnd file and 1 Backend File. But I added the 2nd Backend file last night for future use for when I hit the filesize limit.

I never edit / alter / delete any of the data in Access (except deleting bad records on import in the Kevin and Kevin Group Tables) .. only run a report or queries on it, though the data may be altered in the original application where the input csv files are generated.

Just as an example .. here are the 4 tables I usually query on. The 2 "Transmission Tables" have the IDENTICAL data in them. with Transmissions having OLDER data and Transmissions2 having NEW data when I hit the file limit. The Kevin and Kevin Group tables are not going to grow and are in the 1 backend file.

So the ? .... how to make a query where I can get data from the 2 transmission tables combines .. without making a Union Query ? I can do it .. and will have to alter all my queries, unless a fix is found when the day comes to introduce the 2nd backend file.

I could not upload a image of my tables as the image upload is not working.

But here is a sample query ? One how it is now, and one as a Union Query ?

NORMAL QUERY:

SELECT Transmissions.Radio, Kevin.User, Transmissions.Type, Transmissions.TransType, Transmissions.Group, KevinGroup.Name, Format(Count(Transmissions.Group),'#,###') AS [Count] FROM Kevin INNER JOIN (KevinGroup INNER JOIN Transmissions ON KevinGroup.Group = Transmissions.Group) ON Kevin.Radio = Transmissions.Radio GROUP BY Transmissions.Radio, Kevin.User, Transmissions.Type, Transmissions.TransType, Transmissions.Group, KevinGroup.Name HAVING (((Transmissions.Type) = 'TR') And ((Transmissions.Group) = [Forms]![frmMainSwitchboard].[subQueries].[Form].[txtFilter])) ORDER BY Count(Transmissions.Group) DESC;"

UNION QUERY: (I did not test this one .. just an example)

SELECT Transmissions.Radio, Kevin.User, Transmissions.Type, Transmissions.TransType, Transmissions.Group, KevinGroup.Name, Format(Count(Transmissions.Group),'#,###') AS [Count] FROM Kevin INNER JOIN (KevinGroup INNER JOIN Transmissions ON KevinGroup.Group = Transmissions.Group) ON Kevin.Radio = Transmissions.Radio GROUP BY Transmissions.Radio, Kevin.User, Transmissions.Type, Transmissions.TransType, Transmissions.Group, KevinGroup.Name HAVING (((Transmissions.Type) = 'TR') And ((Transmissions.Group) = [Forms]![frmMainSwitchboard].[subQueries].[Form].[txtFilter])) ORDER BY Count(Transmissions.Group) DESC;"
UNION ALL
SELECT Transmissions2.Radio, Kevin.User, Transmissions2.Type, Transmissions2.TransType, Transmissions2.Group, KevinGroup2.Name, Format(Count(Transmissions2.Group),'#,###') AS [Count] FROM Kevin INNER JOIN (KevinGroup INNER JOIN Transmissions2 ON KevinGroup.Group = Transmissions2.Group) ON Kevin.Radio = Transmissions2.Radio GROUP BY Transmissions2.Radio, Kevin.User, Transmissions2.Type, Transmissions2.TransType, Transmissions2.Group, KevinGroup.Name HAVING (((Transmissions2.Type) = 'TR') And ((Transmissions2.Group) = [Forms]![frmMainSwitchboard].[subQueries].[Form].[txtFilter])) ORDER BY Count(Transmissions2.Group) DESC;"

I am looking to do something else so I don't have to use union queries, but be able to get data from both the Transmissions (old data until file size limit) and Transmissions2 tables (new data when I hit the file size limit)

Thanks ...
- Kevin - (Yes I did name a table after me .. cause it is my data. It is cause I also import identical data from one of my friends for comparison purposes (only the equivalent of the Kevin and KevinGroup tables) .. and it was just easy to name them with our names to keep them apart).

[John_G]

You will have to careful with union queries, too. If the total size of the union query would exceed the 2GB limit, it won't work, because the results of the query have to go somewhere, and that is the front end where queries run.

1.5GB union 1.5 GB is still > 2GB.

And, even though you are creating summary data (group by and Count), Access might still need to (behind the scenes) create a recordset of all the data anyway.