Maximum Questions

[data808]

I have a simple Access database. It has a table, one form, a couple queries, and a macro. Nothing is linked to anything else. All the data is in this one Access file. I use this to enter records.



My questions are:

1. What happens if I use this for years and I begin to hit the maximum number of records this file can hold? What does someone do about it? Would you have to make another Access file from scratch and use the 2 databases as when referring back to old records? Or do you link the 2 databases together so you have access to the old records while being in the new database? Its confusing.

2. I understand that primary keys are never duplicated or used again by default. When I delete a record it will continue to use a primary key that has never been used before. What happens when this primary key gets longer and longer to the point where I want to go back and use a lot of the old primary keys that were deleted long ago so the number can stay at a lesser value for a longer period of time? I don't want the number to get ridiculously long if people keep making mistakes and I have to keep deleting records and wasting primary key numbers.

3. If I were to put this Access file in a network drive and have a couple of clerks at different computers use this same file simultaneously, would it work out ok? Or will I have to do something special to make it work this way? Would it be able to handle a group of 40 - 50 clerks simultaneously?

[June7]

1. Access has a maximum file size of 2GB. There is no limit on number of records. If file gets that large, options are to archive records to another file or to break up the tables into separate files or migrate to another product like Oracle or some flavor of SQL database.

2. Since you don't have any dependent tables, the autonumber primary key field isn't even needed. Some advanced queries could use it but otherwise not relevant. You could delete it and create a new autonumber field and regenerate the numbers.

3. Should split the database. Backend file (table) on file server and frontend (queries, forms, reports, code) copied to each user's computer or their user folder on the server.

[data808]

1. How does archiving records to another file work? If I broke up the tables in separate files, would these files be Excel files and I would then link it back to the original Access file?

2. What kind of example of an advanced query would make good use of primary keys?

3. For the backend and frontend to work, would I just link these two together? Would I be able to do this if I am already established as I stated above? Or do I have to start from scratch and build it to work that way? If I were to have 50 users entering data into this one table through a form, wouldn't it fill up the 2GB maximum fairly quickly? In that case does this happen often to other people where they would need to keep breaking up the tables into separate files or archive the records to another file? Oracle or SQL database is probably not an option as I have no knowledge of the two.

Sorry for the lack of knowledge on this. I'm still learning.

[data808]

Also forgot to ask that if I plan to use this for years, will it warn me that I'm nearing the 2GB file size? I imported an old database from 1989 and have 11,000 records. This made the file at around 5MB in size. I went in and added some missing data to thousands of the existing records. It was basically adding a 0 in some of the fields so my auto calculator could sum up the values. Anyway after doing this my file size jumped to 64MB. I'm worried that I may hit the 2GB size one day and am just preparing for it now. Please let me know what you think of this situation. Thanks for the help.

[June7]

1. Archive to another Access database. You have only the one table so there aren't any other tables to split off to another db.

2. Some nested queries need a unique ID and autonumber serves, review http://allenbrowne.com/subquery-01.html

3. Bing: How to split database
http://office.microsoft.com/en-us/ac...010342026.aspx
http://support.microsoft.com/kb/304932
or just make a copy and delete appropriate elements from each and set table links from frontend to backend.
How I manage split db: http://forums.aspfree.com/microsoft-...ue-323364.html

Run Compact & Repair periodically. That should reduce the file size after edits.

I doubt you will get a warning but probably an error message when attempt to add data that would exceed the size limit.