Quick Queries #40
By Richard Rost
7 months ago
Compacting Back-End, Recycle Bin, UNION 2 GB Limit
In today's Quick Queries video: compact and repair your databases, handle front end and back end files in multi-user setups, and answer common questions about the Recycle Bin series including archiving, auto number management, union queries, dealing with the two gigabyte file size limit, and tips for beginners moving between YouTube and the website.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, compact and repair backend, compact and repair from command line, batch file to compact backend, VBA compact backend, auto number restore, archive table strategy, Recycle Bin implementation, delete child records recycle bin, orphaned records handling, IsActive field for deletion, archive emails in database, external XML recycle bin, SQL Server database migration, union query archive data, two gigabyte database limit, search archived and live data, temporary tables for large search, form filters IsDeleted field, restoring deleted records, Archive vs Recycle Bin, compact backend schedule, AccessLearningZone Quick Queries
Transcript
Well, it's Friday again, folks. You know what that means; it's time for another Quick Queries video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
It's hard to believe we've done 40 of these already, but let's see what we've got today.
First off, we have Ronald commenting on something from Quick Queries 39. He said, "I thought there was an option in the current database to compact and repair on close every time the user closes the database?" There is, but this only compacts and repairs the front end, not the back end database.
So, if you're running in a multi-user setup where you've got a server and a bunch of people using that server, whether it's just a PC running a backend Access database or an actual server, then you're only compacting your front end. Usually, there's no data in the front end, or very little data. There might be some temp data. All your actual data is on the server in the backend file, and that's the one that you want to compact on a regular basis.
I don't recommend compacting it every day unless you have really heavy database use. Once a week is usually fine. I do mine once a week. I run it in an event; I think it's like Sunday at 3 a.m. so I'm guaranteed not to be in it. Make sure you run it at a time when no one's using the database.
Compacting the front end is usually something you don't have to bother doing, especially if you update your database regularly. If you're pushing updates, whenever you push a new update to everybody, they get a new front end, so it doesn't matter if their old one had a lot of bloated space from not being compacted.
If you want to learn more, I have a bunch of videos on compacting and repairing. This is the beginner one, the first one you should watch if you're not sure what compacting and repairing is. This one will teach you how to compact and repair your database from the command line, so you could make a batch file to run it once a week and compact your backend with this if you want to.
Although, I recommend running the actual compact on the server machine. If not, if you compact across the network, all that data has to be transmitted across the network. If you can, go to the server machine, whichever machine actually has your Access backend file on it, and run the compact and repair there if you can, or set up an event on that machine.
This video teaches you how to actually do the compact with VBA, and yes, you can compact your backend. Again, it's best to run it from the backend machine. I actually have a template available for sale that does all that for you. You just set it up on the server machine and you can integrate it with your current database if you want to, or just let it run by itself. Then, once a week, you set up a schedule, a little compact when no one's in the database. So check that out, too. I'll put a link to all this stuff down below.
Next up, I've got a bunch of comments on my Recycle Bin series that I did this week. Let's take a look at some of those.
Lots of people sent me messages like, "I did what you said, but I get an error when I press the button." What's the error? I can't see what you have going on there. What you pasted looks okay, but there might be more to it. I need to know exactly what's happening. What's the error message you're getting? Did you try anything else? That's nowhere near enough information to be able to help you, sorry.
JC Wynn thinks we should name it the "Archive Table." Sure, great. You can name it Granny's Peach Tea Table if you want to. I'm not married to the name. I just called it the Recycle Bin. Name it whatever you want.
I've seen this one a few times: how to delete the record and rearrange the auto number as soon as it gets deleted. I see what you're saying: you delete record number six and you want to rearrange all the rest of the numbers. Don't worry about it. Leave them alone.
Auto numbers are not for you. You shouldn't care what they are. It could be 5640, 2632, or three - it doesn't matter. Just ignore it. You shouldn't be using those auto numbers for anything. They're used internally for making sure records are unique and for relationships to other tables. If you want to make your own counter, I have several other videos on how to do that, but don't mess with the auto numbers.
Yes, there is a method to restore an auto number, and I show this in the extended cut when I go over doing the restore button to take it out of the Recycle Bin and put it back in the table. You have to use an insert query, not an update query, to insert the number back in the table, which is a trick, and then you can restore the record. But renumbering all the rest of the records is not something you should bother doing.
If you need your own separate counter, make a separate field and you can do whatever you want with it.
I explained to people in my last job why I don't delete data. Generally, I orphaned the record and kept the table until I'm aware of how long it needs to be restored. You could do that too. That's another option. In fact, one of the things that people are commenting on and want to see is how to delete the child records, like if you delete an order, you want to delete all the details that go with it and put that in the Recycle Bin. That's definitely a possibility. It's not hard to delete the data and save it. Restoring it is a bit of a challenge. I might do a separate video on that, though.
You could literally just orphan the record; in other words, just delete the order entry and leave those order details in the table as orphans. They're not pointing to anything. They're not going to waste any space. This way, in the future, if you do have to restore that order, all of its children are right there in the table still. If space is not a concern, that's certainly valid. There are a million ways to handle this, and that's certainly an option.
Nick says, "Nice one. I hope you're going to show us how to recover a record too." Yes, absolutely. I do show it in the extended cut after part three. So click that blue join button down below and become a member, and you'll get code that will show you how to restore the deleted record. If not, you've got the information - the important part of the series was you've got the data. It's in your Recycle Bin. So if you really, really, really need to restore it, it's in there. You can just go and copy and paste the fields back where they belong. If you want a nice, fast, easy way to do it, that's in the extended cut.
Aiki says, "Why not just have a Boolean field 'IsDeleted' in every table to mark deleted records, and a RecycleBinT to store only the table name and key ID fields, while the form filters 'IsDeleted' also?" I get where you're coming from, and yes, if you're building a database from the ground up, that is definitely the best way to do it.
In fact, in my database, I've got an "IsActive" field here because I put it there when we started the database. You could do the same thing with orders; you could have an "IsActive" here, or "Cancelled" or something. That's the perfect scenario.
The Recycle Bin really is for when you've got a database you've been using for years. It would be a lot of work to go through. It's not just adding that field to the table. Remember, you've got to make all of your queries aware of that field. You've got to make all of your forms and reports aware of that field. You've got to make all your VBA code aware of that field, and if you've got hundreds of different objects in your database that all just look at the customer list and assume they're all valid customers, you go to run your month-end and it's pulling in data that it shouldn't be.
That's where the problem is. That's why I came up with the Recycle Bin idea. You've got an existing database, and it would be a lot of work to go through and add the idea of "This is not deleted, this is not an active record, it should be deleted, but I don't want to delete it." Send it to the Recycle Bin so it's there if you need to get it back, but all of your other objects in your database won't see that record.
Good thinking, and that's how you should design the database from the beginning. But a lot of people don't, including myself. I started my current database that I still use for my company way back in 2002, I think, and so I did not design all my objects and my tables to handle that. So I came up with a Recycle Bin because I wanted to delete some stuff, but I still wanted that data. Send it to an archive table, Recycle Bin table, whatever you want to call it, and you've still got the data if you need it.
In fact, I have an archive table for all of my old customer service emails and contacts that all gets imported into my database. I reply from the database, but I don't need to save stuff from two years ago or five years ago, but in case a customer says, "Do you remember back two years ago when I asked you about..." I can still go to my archive table and find it. It's not slowing my main database down, but it's there if I need it. That's the point of an archive table or a Recycle Bin or whatever you want to call it.
I often get the comments off YouTube, but I often forget to go to my own website to find the comments on there.
Sam Domino says, "Given that Access has a two gigabyte limit, would it be better to use an external text or XML file for your Recycle Bin?" You could, sure. It all depends on your usage and how much data you're recycling. If it's lots and lots and tons and tons of stuff, sure, put them in external text files, XML if you want to. Basically, what I did in the extended cut is kind of like an XML file.
Here's what we built in the extended cut, and if you look in the Recycle Bin here's the record data. It starts with a "begin" - the field name, and then the data, and then an "end" tag, and that's very similar to what XML does.
The reason why I did this was because if you look at something like a "notes" field, this could have multiple paragraphs of data, so you have to know where that ends, and I just chose to look for an end tag. You could do this however you want. If you want to save it as an XML file, sure. If you want to save it in a different database file, yes, if you have a lot of stuff put this in another database file and link to it. If you get close to two gigabytes, move it up to SQL Server or save these as text files. It's just how much work do you want to go through when it comes time to restore that. Totally up to you, but great question.
A few of you want to see a fourth video on how to do child records. A couple of people posted comments asking me to do another video on recycling child records.
Then I got these clowns in my forums. I'm going to tell you, come on. No, that's actually pretty cool. Like that. And debug-compile on some of you guys.
All right, I guess now I have to make another video, so look for a part four soon. I can promise I will show you how to recycle the child records. Restoring them as a whole is another thing. That's going to take some work, so I'll see. I'll show you how to recycle them, at least get the text in there. But restoring them, maybe in a couple more questions today.
How do I contact you by Messenger? You don't. I do accept email, but my inbox is always full. The best way to reach me is to use the contact page on my website. This is it here.I get tons and tons of email, and if you have a technical question about how to do something in Access, the best thing to do is to post it in the forums on my website. You'll find links to this stuff down below. I can't answer everyone's emails personally. I'm sorry.
I know in some of my older videos when I first started doing this a few years ago, I said feel free to email me with your questions, but I just get too many of them now. I have a form letter I send back saying I just can't, there are not enough hours in the day.
I wish I could answer everyone's emails personally. I really do. I love talking to all of you; I love helping you out. There are just not enough hours in the day. I'd never have time to make any videos.
As far as messenger or any kind of live way to contact me, now you get the customer service forum, email, forums, mail, text, phones way down here after Vulcan mind melds and Starfleet subspace radio and a Gondor signal fire. Tried all those, maybe phone's an option. I hate talking on the phone with a passion. I can't stand it.
I don't even order a pizza on the phone. If you're a pizzeria and you don't accept Door Dash or online orders, forget it. I'm not going to call.
Here's a question about my Search Multiple Fields video. Callie Layton says this is great except there's only nine rows for criteria. I would need many more rows to search all my fields. I see no option to insert rows.
What he's talking about is the query by example grid. That's this thing when you go into a query, your "or" criteria rows down here - one, two, three, four, five, six, seven, eight, nine. I think there's nine.
The point is they only give you a couple down here, but you can make more. If you go into SQL view and know how to write the SQL, you could say WHERE so and so, AND so and so, OR so and so.
This is the default design view here for beginners, for a graphical way to build SQL statements. What this is really doing behind the scenes is writing that SQL.
If you want more options than that, you have to learn SQL. Fortunately, I have a lot of videos on SQL to teach you how to do it. Start here.
Next up is a question for beginners. Jeremy's a new member. He says, "Good day, Rich, and I'm brand new to Access. Would you recommend this video playlist or your website? Oh, and someone's beaming in too."
Jeremy, this is completely up to you. It's all about your preference and how you like to watch the videos. If you're happy with the YouTube interface and maybe want to watch it on your phone or on your tablet, stick with YouTube.
If you want to watch it on my website, a lot of people say the website interface is better. I've tried to make the interface on the website very easy. Just click on the next video here, and it plays over there.
There's also a four hour long single video with all of these lessons in it on YouTube. So it's really just your preference. If you're going to be doing it on your laptop or your PC, I personally think my website's better, but if you like YouTube, watch it on YouTube. That's fine too.
Either way, the videos are the same. You might find more responses to comments on my website. If you have questions, I'm the only one that goes through the YouTube comments.
But on my website, I've got a team of six or seven moderators, and they go through the questions too. They're much better at answering questions than I am. They get to them faster, and they usually help a lot more than I do. There are just not enough hours in the day.
We were just talking about not deleting data earlier. Well, here's a question on my Don't Delete Data video. There's a question that comes from Jay Drag, and not so much a question, but more of a statement. He's saying that having the backup or archive table with archived data as opposed to live data could help you get around the two gigabyte limit.
For those of you who don't know, a Microsoft Access database, a single database file, has a two gigabyte limit.
I remember trying this years ago - we're probably going back 10 years or so. The union query, where you can union results from two tables together into a single query, does not let you get around that two gigabyte limit.
Unless they've changed things in recent years (and I just googled it, and it doesn't appear that they have), you can't use that as a workaround.
I just double-checked again, I just googled it again, and I asked ChatGPT. It has to do with the way that Access actually addresses the records in memory too. There's a certain set size, and two gigabytes is it. So if you have two gigabytes in one table or even, let's say, 1.5 gigabytes in one table and 1.5 gigabytes in an archive table and you union those together, you're going to get an error saying that the query is too complex or that the resulting recordset is too large.
That is a good use for a union query if you want to search a customer's complete history. Maybe have it as an optional search, and that union query will then look through all the live data and the archived data. But that resulting data set cannot be more than two gigabytes.
If you need more than that, you could try to pare that down with some filtering and criteria ahead of time. You can create temporary tables.
What I would do in this case is, if you know the customer you're searching for (maybe you have his customer ID, or in your case, the patient ID), you could run a query on this table, on the live table, run a query on the backend table, the archive table, and create two temporary tables with just this customer's data.
Then you could union those two temporary tables together and search that. There are always ways around stuff.
You could also upgrade to SQL Server and put your tables there. SQL Server Express, I believe, has a 10 gigabyte limit, which is a lot. It's five times the size of Access.
I've got SQL Server Express running here in my office, and I've never hit a limit on that. I think my database is up to four gigabytes and it's 20 years old.
You could also chunk it by date. There are lots of options, but I see where you're going with that and it's a good idea. But no, you can't use that to get over the two gigabyte limit.
That's a great question that's never come up before, so I figured I'd address it.
All right folks, that's going to do it for another Quick Queries. Hope you enjoyed, hope you learned something. Enjoy your weekend!
Live long and prosper, my friends. I'll see you next time.
Oh yeah, and don't forget, stop by my store. All kinds of swag: you get hats, cups, teddy bears, sweaters, mouse pads. Check it out - links down below.
TOPICS:
Compacting and repairing the front end database
Compacting and repairing the back end database
Scheduling automated compact and repair events
Running compact and repair from the command line
Using batch files to compact and repair databases
Best times to run compact and repair for multi-user setups
Why compacting the front end is usually unnecessary
Importance of compacting the back end on the server
Using VBA to run compact and repair
Storing deleted records in a Recycle Bin table
Differences between "Recycle Bin" and "Archive Table"
Why you should not renumber AutoNumber fields
Reasons not to use AutoNumber as a meaningful value
Using a separate counter field instead of AutoNumber
Handling orphaned child records after deletion
Options for archiving related child records
Using an "IsDeleted" Boolean field to mark deleted records
Why retrofitting "IsDeleted" can be a challenge in old databases
Differences between live data and archived data tables
Using union queries to combine archived and live data
Limitations of Access database two gigabyte limit
Why union queries cannot exceed the two gigabyte limit
Creating temporary tables to filter large datasets
Upgrading to SQL Server for larger database sizes
Searching multiple fields using SQL view
Editing query criteria rows beyond the design grid limit
Chunking data by date for large database management
COMMERCIAL:
In today's video, we're discussing a variety of Microsoft Access questions, like how compact and repair works for front-end and back-end databases in multi-user setups, options for managing deleted data using a Recycle Bin or archive table, and why you should not worry about rearranging auto numbers. We'll also talk about storing recycled data to avoid the two gigabyte limit, the limitations of union queries, advice about using SQL Server if you need more storage, and when to watch videos on YouTube versus the website. If you are curious about database cleanup, archiving data, troubleshooting errors, or beginner tips for searching multiple fields, this video is for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What happens when you enable "compact and repair on close" in Microsoft Access for a multi-user database?
A. Only the front end database is compacted and repaired
B. Both the front end and back end databases are compacted and repaired
C. Only the back end database is compacted and repaired
D. Neither the front nor back end databases are compacted and repaired
Q2. When is it generally recommended to run compact and repair on the Access back end database?
A. Every time a user closes the database
B. Once a week, during a scheduled maintenance window
C. Only when the database reaches its maximum size
D. Never, because it's unnecessary
Q3. Why is it better to run compact and repair directly on the server machine rather than across the network?
A. Network compacts are faster
B. Running it from the server avoids excessive data transfer over the network
C. Running across the network ensures better database integrity
D. It makes no difference where you run it from
Q4. What is the main purpose of the "Recycle Bin" table or archive table in Access?
A. To permanently delete records from the database
B. To store deleted records so they can be restored later if needed
C. To automatically compact the backend database
D. To rearrange auto numbers after deletions
Q5. Why is it not recommended to renumber auto number fields after deleting records in Access?
A. Auto numbers are meant for user-friendly numbering
B. Renumbering auto numbers is necessary for data integrity
C. Auto numbers are used internally for record uniqueness and relationships, their specific values do not matter
D. Auto numbers automatically renumber themselves
Q6. If you need a user-visible incremental counter for records, what should you do?
A. Use the auto number field directly
B. Create a separate field for your custom counter
C. Modify the auto number field with a query
D. Ignore the need for a visible counter
Q7. What is a common challenge with implementing a Boolean "IsDeleted" (or "IsActive") field in an existing Access database?
A. It's impossible to add new fields to an existing table
B. You have to update queries, forms, reports, and code to recognize the new field
C. The field type cannot be a Boolean
D. Queries will ignore Boolean fields by default
Q8. Which of the following is a benefit of using a Recycle Bin table instead of an "IsDeleted" flag, especially in an existing database?
A. All queries automatically ignore deleted records
B. No need to modify all queries, forms, and code to filter on "IsDeleted"
C. Deleted data is permanently lost
D. You can recover auto numbers automatically
Q9. What should you do if your Access database size approaches the 2 gigabyte file limit and you need to preserve more data?
A. Nothing, Access can handle more than 2 gigabytes
B. Use external text or XML files for archiving deleted records
C. Expect union queries to bypass the 2 gigabyte limit
D. Ignore the warning, data will not be lost
Q10. What is the limitation of using union queries to join main and archive tables in Access?
A. They increase performance beyond 2 gigabytes
B. They allow you to bypass the Access 2 gigabyte limit
C. The resulting recordset cannot exceed the 2 gigabyte Access file size limit
D. Union queries can only be used on identical tables
Q11. If you want to compact and repair your Access backend manually and on schedule, what is a good method?
A. Rely only on Access's automatic features
B. Make a batch file or scheduled event to run the compact at a specified time
C. Delete records periodically to reduce file size
D. Rename your database file weekly
Q12. According to the video, what is the best way to reach the instructor with technical questions?
A. Send a direct message via Messenger
B. Call on the phone
C. Post the question in the website's forums
D. Use Starfleet subspace radio
Q13. In Access query design view, if you need more than nine OR criteria rows, what should you do?
A. Right-click and add more rows in design view
B. Switch to SQL view and write the query manually
C. Using more than nine is not possible in Access
D. Create a separate query for each criteria row
Q14. Why might you choose to use the instructor's website over YouTube to watch tutorials?
A. The website has fewer videos
B. The website interface may be easier and has more moderator support for questions
C. YouTube is not compatible with Access videos
D. The content is different between the website and YouTube
Q15. If you need to search a customer's complete history across live and archived tables, which method is recommended for large datasets close to the Access size limit?
A. Use a single union query regardless of database size
B. Upgrade to SQL Server for larger dataset support
C. Export all data to Excel
D. Split queries are not possible in Access
Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-C; 13-B; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
In today's Quick Queries video from Access Learning Zone, I am here to tackle another round of Microsoft Access questions and comments. We've reached the 40th installment of Quick Queries, and as always, I look forward to sharing tips and addressing your Access challenges.
Let's start with a question about compacting and repairing databases, a common maintenance topic. One viewer pointed out that Access allows you to set your front end database to compact and repair automatically when closed. That is correct, but keep in mind this only affects the front end, not your back end database where your main data is stored in a split setup. If your team accesses a shared backend on a server, automatically compacting on close only helps the user's front end, which typically stores very little data.
For most environments, compacting the backend weekly is enough unless your database experiences heavy use. I usually schedule mine for early Sunday morning to avoid user conflicts. Always make sure no one is using the backend during compacting, and try to run this task directly on the server machine rather than over a network, which avoids possible data transmission issues.
As for the front end, you often do not need to worry about compacting it, especially if users get a fresh copy during updates since any bloat they might accumulate goes away with each new version. If you'd like more guidance on compacting and repairing databases, I've created several tutorial videos, including how to automate this using the command line and schedule regular jobs. I also have a more advanced guide on compacting with VBA and even a template you can set up for routine maintenance.
Switching gears, I received many comments about my recent Recycle Bin series, which teaches how to archive deleted records instead of removing them permanently. A common message I get is from users who encounter errors after following along—if you ever need help, always include a full error message and additional context so I can better assist.
There are some creative naming suggestions like "Archive Table" for the table used to store recycled records. You can call it whatever suits your needs. The important thing is understanding the principle behind the method.
Another frequently asked question is about renumbering auto numbers after deleting records. This is a common misconception. Auto numbers in Access are meant to serve as unique identifiers for records and not as continuous row numbers you manage by hand. You should never worry about reordering them after deletions. If you need sequential numbering that resets or changes, create your own counter field for that purpose.
A related question is whether you can restore an auto number when pulling a record from the Recycle Bin back into the primary table. This is possible, and in some of my extended videos, I demonstrate how to accomplish this using an insert query rather than an update. However, again, renumbering all records after deletions is not something you should attempt.
Some viewers, and it's an approach I used in previous workplaces, suggest not deleting records but instead "orphaning" them—removing parent records while leaving related child records intact. This ensures you can later restore data if needed, although you need to consider whether orphaned data will clutter your table and impact performance.
Questions also arise about the best way to implement a Recycle Bin. For databases you are designing from scratch, I recommend adding an "IsDeleted" or "IsActive" field to each table, making queries, forms, and reports aware of this status for filtering purposes. However, most people seeking the Recycle Bin solution already have a fully developed database and would face an enormous task retrofitting this logic everywhere. That is precisely why the Recycle Bin approach exists, to safely set aside deleted data in a separate table without the hassle.
Some people ask about moving archived data into a text or XML file due to Access's two-gigabyte file size limit. If you're storing vast amounts of recycled data, yes, you could use external files or even a separate Access database. In the extended cut of my series, I show you a format similar to XML to encapsulate the data for restored records. How you choose to store archived data depends on your usage needs and how much effort you are willing to invest in future restoration.
A few users requested future tutorials on handling recycling of child records—that is, ensuring when you delete a parent record, all related children are archived together. This is possible and efficient; restoring linked data is a bit more involved, and I plan to cover this in more detail in an upcoming part.
There are always questions about how to contact me directly. I do my best to respond to emails, but with the sheer volume I receive, it is no longer realistic to answer everyone personally. The best way to get help is to post your question on the Access Learning Zone forums, where a team of moderators and I can provide answers. If you have a technical question, the forums give you the greatest chance of getting a timely and detailed response.
I also get questions about searching across many fields in Access. The query-by-example grid in Access's designer provides a limited number of "or" rows for criteria, but you can always switch to SQL view to write more advanced statements and add as many conditions as you need. If you're interested in expanding your SQL skills, I have plenty of tutorial videos to help you get started.
For beginners, I am often asked whether learning through my website or YouTube channel is better. Ultimately, it's your choice. Both platforms have the same videos, but my website's interface makes it easy to follow lessons in sequence and has a team of moderators ready to answer questions more quickly than I can. YouTube is convenient if you prefer watching on your phone or tablet, but the content is identical, so go with whatever works best for you.
I'm frequently asked about Access's two-gigabyte database size limitation and workarounds for storing historical or archived data. While some wonder if using UNION queries to combine live and archived tables can bypass the limitation, unfortunately, Access still restricts the resulting recordset to the two-gigabyte ceiling. If you need more room, consider moving to SQL Server Express which offers greater capacity or trying alternate strategies, such as partitioning by dates or working with temporary tables filtered to contain only relevant records.
That covers today's questions. I appreciate all of your comments and feedback, and I hope this discussion provided you with some helpful solutions and ideas for managing your Access databases.
For a complete video tutorial with thorough, step-by-step instructions on everything discussed here, visit my website using the link below.
Live long and prosper, my friends.
Topic List
Compacting and repairing the front end database
Compacting and repairing the back end database
Scheduling automated compact and repair events
Running compact and repair from the command line
Using batch files to compact and repair databases
Best times to run compact and repair for multi-user setups
Why compacting the front end is usually unnecessary
Importance of compacting the back end on the server
Using VBA to run compact and repair
Storing deleted records in a Recycle Bin table
Differences between "Recycle Bin" and "Archive Table"
Why you should not renumber AutoNumber fields
Reasons not to use AutoNumber as a meaningful value
Using a separate counter field instead of AutoNumber
Handling orphaned child records after deletion
Options for archiving related child records
Using an "IsDeleted" Boolean field to mark deleted records
Why retrofitting "IsDeleted" can be a challenge in old databases
Differences between live data and archived data tables
Using union queries to combine archived and live data
Limitations of Access database two gigabyte limit
Why union queries cannot exceed the two gigabyte limit
Creating temporary tables to filter large datasets
Upgrading to SQL Server for larger database sizes
Searching multiple fields using SQL view
Editing query criteria rows beyond the design grid limit
Chunking data by date for large database management
|
