Question working with excessive data sets

[CoffeeGuru]

Hi
I have been working with Access for about 2 months now and using sales data have created a nice simple working database.

My problem is that I have just started to role this out for more customers data and my database just went BANG.

My initial database had a table with 390,00 rows of data and the size of the database when repaired and compacted is 129,324KB

When I added the next customers data, the data set was 1,187,629 rows of data and on input the database said it maxed out, I'm guessing of the 2Gb size restriction, although my temp table before appending it to my master table made the whole db 833,604KB.
On append it adds 2 cells to the query before appending to the table, the country and the store name.

So my question is:
If I created a seperate database for each store, that purely held the data and each database was identical in table names and field names can I have a Master database that holds only small common data sets, queries and forms and have it query the data from the various databases as necessary.

I am now totally out of my depth as I thought I had this all worked out and my internal demo went so well last week.

 

[DavidAtWork]

have you considered splitting the database and having a SQL server back end, this will certianly help with the increase in records and will also improve performance, aid with security and add easy back up functionality
If you go down the route of a separate DB for each customer, it could become a nightmare in terms of maintenance

David

 

[CoffeeGuru]

Hi David,

No I havent, But I can ask if there is the possibility of doing so.

In my ignorance can you describe what you mean by "Splitting the Database"
and when you say SQL Server backend are we talking about where the sales data is stored or the whole database.

Martin

 

[DavidAtWork]

Splitting the DB means what it says on the tin, splitting it inot FE/BE, the front-end will look the same as it does at the moment, but the back-end will just consist of your data tables. In this setup you will usually have the BE located on a server which usually uses a back up schedule to safeguard the data. Using a SQL server database for your BE will cope better with large numbers of records.

David