Compact Cmd Line
By Richard Rost
11 months ago
Compact/Repair Corrupted Database via Command Line
In this tutorial, I will show you how to compact and repair your Microsoft Access database from the command line, especially when you're unable to open it due to errors. We'll cover the steps to create a command line command using Notepad, how to run it directly from the command prompt, and how to automate the process by creating a batch file.
This approach can be a lifesaver when conventional methods fail, ensuring your Access databases stay in optimal condition. If you're facing exclusive use errors or encountering other common obstacles, this video will guide you through effective solutions.
Henry from Santa Rosa, California (a Platinum Member) asks: How do I fix my Access database if I can't open it because it's giving me an error? I've heard you can compact & repair it without opening, but I'm not sure how to do it.
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, compact and repair database error, command line compact repair, access shift key bypass, access database exclusive lock, access LACCDB file, corrupted database fix, access compact batch file, run batch file for database, access compactor template, compile decompile database, VBA compact, access backup strategy, access database troubleshooting tips
Intro In this video, we will talk about how to compact and repair your Microsoft Access database from the command line if you are unable to open it due to errors. I will show you step-by-step how to find the Access application path, copy your database file location, and use Notepad to create the correct command line syntax. We'll also cover how to save this as a batch file for easy reuse, and look at ways to organize your files and run these commands through Command Prompt.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today we're going to talk about how to compact and repair your database if you can't open your database. Sometimes you try to open the database, and it gives you an error. You're thinking, I want to compact and repair it, but it still gives me an error. What do I do? Well, today I'm going to show you how you can compact and repair it from the command line. Today's question comes from Henry in Santa Rosa, California, one of my platinum members. Henry says, how do I fix my Access database if I can't open it because it's giving me an error? I heard you can compact and repair it without opening it, but I'm not sure how you do it.
I had this problem with one of my databases a little while back. You go to open it, and it ran okay, but once in a while, it was a weird error. So I'm like, I'm going to compact and repair it. I tried to access compact and repair, and it says the database is open exclusively by another user. I shut it down.
Now, normally what you can do is you can use the shift key bypass to bypass your startup folder. Hold the shift key down, start the database, and it'll open up. It'll bypass your startup. Then you can usually compact and repair it, but it was still telling me it was open exclusively by another user. I'm the only user, so I knew it wasn't that. I tried all the basics: checked for an LACCDB log file that wasn't there, tried renaming the database—that didn't do it. Sometimes the database had just become corrupt, and once you open them and access it, sometimes you can't compact it.
Now, before we get into it, this video is for everyone. You don't have to have any particular skills. I'm going to show you what you have to do to be able to compact and repair your database. If you don't know what compacting and repairing your database is and why it's important to do it regularly, there's another video I can point you to where I explain exactly what compacting is, why you should do it, and how to do it from inside Access. Today's video is if we're having problems with this.
So, for this example, I'm going to create a folder on my desktop called My Database. That's the folder. This will be wherever your database is stored, and if I open this guy up, you'll see here's the folder and there's the name of the database file itself. This is all going to be very important in just a minute.
First up, I want you to open up Notepad. If you don't have Notepad on your system, then something's wrong with your system. Every Windows system should have Notepad on it. If you can't find it, click on your start button and type in Notepad, and there it is. Hit enter, and there's Notepad. Why are we going to use Notepad? Well, there are a couple of things we need before we can run this from the command line. It's easier than typing all this stuff out.
Alright, so find Microsoft Access. I have it down here on my taskbar. If you don't, you can browse to it from your start menu, find wherever your shortcut for Access is, right-click on it, then find Access again right here, not any of these recent things you got here, find Access itself, right-click on it again, and go to properties. This is what we're looking for right here, the target. This is exactly where Access is on my system at C:\\Program Files\\Microsoft Office\\whatever. We want to copy this whole thing, copy Ctrl+C, and then go to Notepad and paste it in. Make sure you got the quotes around it. That's the full path and file name to Access. Put a space there.
Now we're going to go back to our folder where our database is stored, click up here in the address bar, and copy that. Come back to Notepad, put an open quote, and then paste that in there and then a backslash and then the name of your database which, in my case, is right here. We're going to copy this, click on it to get the whole thing, including the file extension. If you don't have the file extensions on, turn them on; I've got a whole separate video on how to do that. Then we're going to paste that in there and then close the quotes. This should be one long line, but I've got a smaller window, so that's all one line.
So there's Access, where the Access application is, a space, and then again, in quotes, the full path to your database file, and then a space, and then we're going to go /compact. That's the command to tell Access when Access runs, open this database and then compact it.
Now, we're not done yet. I'm going to shrink this down just a bit so you can see the whole thing. We're going to copy this whole thing, copy it to the clipboard. Now, there are two ways you can do this: you can run this right from the command prompt, or you can make it a batch file. We're going to do both. I'll show you both. This way, you have it for the future and you don't have to keep doing this.
So, we've got this copied to the clipboard, right? Come over here. Now you can open up your command prompt by typing cmd and then hit enter, and that will open a command prompt right at that folder. Now paste in your command line that we just made and press enter. Access will run. In this particular case, it opens up and compacts and closes almost immediately because this TechHelp free template is tiny, but if I do one of my bigger databases, you can see it does its thing and takes a minute and compacts the whole thing.
That's how you run it right at the command prompt. If you don't want to keep having to do all this, we're going to take this file we just made in Notepad and save it. Hit Ctrl+S. I'm going to put it in my database folder. So, I'm going to go to where it was on my desktop and then My Database, and there it is right there. I'm going to come down here, call it compact.bat like that, save it as All Files, and then it's saved.
Now, you can close this guy. Look at that, you got a compact.bat. It's a batch file, the Windows batch file. What can you do with a batch file? Well, you can just double-click it here, and it will run. You can see Access popped up and disappeared. It's not showing because it's on the other screen. Let me do it again. Ready? Double-click, and there's the batch file. Access runs and should close itself. There it goes.
Now, you have your old compact.bat file you can keep there. You can add more lines to this if you want to right-click, edit. It opened on the other monitor again. This is the problem with having multiple monitors. So, you never know where stuff's going to open up. So, you could take this if you have another database you want to compact after that one, copy, and then paste and then just change the name of the file name. Maybe you got a different one, version two, and it'll compact that one and then the next one and then the next one.
I'm just going to close that and not save it, but there you go. That's how you do that. It's not that hard.
For those of you who are a little more advanced, I got this video where I show you how you can compact with VBA. You can have your database compact itself or other databases. You just click a button, and it compacts. I have this compactor template available where you can have this run as a nightly event. You give it your back-end folder, and it will go through and compact each of your databases by itself, kind of like what I just showed you with the batch file, but it handles it. It tracks how much space you've saved in each one. Like I get my report every Sunday. It tells me, okay, I saved 7% or whatever.
You can also run that with my backup template. Make sure you've got a good backup plan and strategy in place. This will back up all the files in your backup database or backup folder.
If you compact your database and it finishes but you're still having problems opening it, you might need to run through my troubleshooter. It could be something else. There are other tips on this page you can try to get it working. You can try things like creating a blank new database and importing all the objects. Sometimes even something like just restarting your computer will help fix it. I had that problem once; my database was acting weird. I went through compacting it; I did a bunch of other stuff. I was like, let me just restart Windows, restarted Windows, and everything was fine. Something else that was running in the background was messing with my database.
Another thing you might have to do is decompile your database. This video talks about compiling your database, and if you've watched any of my VBA videos, I always talk about debug compile once in a while. I make it catchy so you remember it. But yeah, debug compile. In this video, I talk about decompiling also. You have to do that from the command line. That's a decompile command. Sometimes your code gets messed up, and if compacting and repair don't fix the problem, you can decompile the code. Access compiles your VBA code into machine code, and it doesn't always do that perfectly all the time. Sometimes you have to decompile it, which reverses that, and then you recompile it again and it fixes the problem. It's not perfect, and these things happen, but this is what you do to fix it.
So there you go. Well, that's how you compact your database from a command line prompt or from a batch file. That's one more tool in your box to help keep your Access databases running smoothly. That's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time!
TOPICS:
Compacting and repairing a database from the command line
Using Notepad to create command line instructions
Locating the Access application path
Copying database file path for command line use
Creating a batch file for automatic compacting
Running a batch file to compact a database
Handling database errors with compact and repair methods
Creating a folder for organizing database files
Using command prompt for database operations
COMMERCIAL:
In today's video, we're tackling the issue of compacting and repairing your Access database when it gives you errors and won't open. You'll learn step-by-step how to use the command line to compact and repair without opening your database. I will guide you through creating a Notepad script to simplify this process and how to save it as a batch file so you can run it with a double click anytime you need. Plus, I'll explain additional advanced options using VBA for automated compacting and take you through some troubleshooting tips if you still encounter errors after compacting. 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 is the main topic of the video tutorial presented by Richard Rost?
A. Creating a new Access database
B. Compacting and repairing an Access database
C. Writing VBA code for Access
D. Installing Microsoft Access
Q2. Why might you need to compact and repair an Access database from the command line?
A. To open the database on multiple devices simultaneously
B. To recover data from a permanently deleted database
C. If the database cannot be opened due to an error
D. To improve the user interface of the database
Q3. What initial check did Richard suggest before attempting to compact and repair from the command line?
A. Ensuring the database version is up to date
B. Checking for an LACCDB log file
C. Verifying the database's compatibility with the operating system
D. Ensuring the computer has enough memory
Q4. Which tool did Richard use to prepare the command that would be run from the command line?
A. Excel
B. WordPad
C. Command Prompt
D. Notepad
Q5. What should be copied and pasted into Notepad to prepare the command for compacting a database?
A. The folder name where Access is installed
B. The full path to Access along with the database path and name
C. The file properties of the database
D. The name of the user currently accessing the database
Q6. How can the compact command be executed besides using the command prompt?
A. By creating and using a Word document
B. By pressing a function key in Access
C. By creating and running a batch file
D. Through a web browser interface
Q7. What does creating a batch file allow you to do?
A. Provides a graphical user interface for database management
B. Allows automated, scheduled running of database commands
C. Changes the database structure
D. Converts the database to another file format
Q8. What additional technique did Richard mention that can be helpful if compacting the database does not solve the problem?
A. Changing the database theme
B. Decompiling the database
C. Installing additional plugins for Access
D. Exporting data to an external drive
Q9. What does Richard suggest if you continue having problems after compacting and repairing the database?
A. Creating a brand new database
B. Contacting Microsoft support immediately
C. Running a troubleshooter and possibly restarting your computer
D. Deleting all existing records in the database
Q10. What important step does Richard emphasize that should be part of managing Access databases?
A. Installing the latest version of Microsoft Office
B. Regularly backing up the database
C. Customizing the Access interface
D. Using a database only on weekends
Answers: 1-B; 2-C; 3-B; 4-D; 5-B; 6-C; 7-B; 8-B; 9-C; 10-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 Today's TechHelp tutorial from Access Learning Zone focuses on how to compact and repair a database using the command line, particularly when you're unable to open the database normally. Sometimes, attempting to open a database can lead to errors, prompting you to compact and repair it. However, this error might persist, leaving you wondering about your next steps. Here, I'll guide you through the process of compacting and repairing your database without opening it, which is a query brought to me by Henry from Santa Rosa, California, one of my platinum members.
I encountered a similar issue with one of my databases recently. Sometimes it opened without a hitch, but occasionally, it presented a strange error. Naturally, I decided to compact and repair it. Yet, when I tried, I was informed that the database was open exclusively by another user. I closed the database, even though, being the only user, I knew no one else was accessing it at that time. I tried the usual preliminary checks, like looking for an LACCDB log file, which wasn't there, and attempting to rename the database, which also failed to resolve the issue. Sometimes, a database might get corrupted to a point where opening it doesn't allow for a compact operation.
This tutorial is designed for everyone, requiring no specific expertise. I'll demonstrate the steps required to compact and repair your database. For those unfamiliar with compacting and repairing, and why it's a crucial maintenance task, I can direct you to another video where I detail the compacting process within Access.
To illustrate, imagine creating a folder on your desktop named "My Database," where your database is stored. Open the folder, and you'll see the name of the database file, which is important for upcoming steps.
First, open Notepad. If Notepad isn't accessible, something might be amiss with your system since every Windows operating system includes Notepad. If it's elusive, use the start button, type "Notepad," and press enter to launch it. Notepad is a practical tool to tackle this task because writing into it is easier than typing everything directly into the command line.
Next, locate Microsoft Access. It may be pinned to your taskbar or found via the start menu. Once you find the shortcut for Access, right-click it, select properties, and note the "target" location - the path to Access on your system. Copy this full path, including quotes, using Ctrl+C, and paste it into Notepad. Add a space after pasting.
Find your database folder's path by clicking in the address bar to copy it. Return to Notepad and insert the path within quotes, followed by a backslash and your database's name, including its file extension. Ensure it appears as a singular long line.
Once this setup is complete, you have two options for executing it: running directly from the command prompt or creating a batch file. Copy the prepared command line to the clipboard, open your command prompt by typing "cmd," and press enter. Paste the command in the prompt and press enter. Access will launch to compact your database and then close.
To avoid repeating this setup, you can create a batch file. Save the Notepad file using Ctrl+S, name it "compact.bat," and save it as "All Files." This batch file can be double-clicked to execute the compact command automatically. If you maintain multiple databases, simply replicate the command within the batch file, adjusting the database names as needed.
For advanced users, I offer another video illustrating how compacting can be automated through VBA, allowing the database to compact itself or others. This video features a compactor template automating this task nightly and tracking the space saved.
If compacting your database doesn't resolve issues, consider additional troubleshooting steps. Creating a new blank database and importing objects can sometimes work, as can restarting your computer. Occasionally, interference from other running processes affects database operations.
Decompiling your database is another potential fix, detailed in a supplementary video. This involves using the decompile command from the command line to address potential code errors. Access and recompiling the code can occasionally rectify issues that compacting alone cannot.
That's how you handle compacting your database from both a command line prompt and a batch file, each being valuable tools to maintain your Access databases. For a complete video tutorial with detailed instructions, visit my website at the link below. Live long and prosper, my friends.Topic List Compacting and repairing a database from the command line
Using Notepad to create command line instructions
Locating the Access application path
Copying database file path for command line use
Creating a batch file for automatic compacting
Running a batch file to compact a database
Handling database errors with compact and repair methods
Creating a folder for organizing database files
Using command prompt for database operations |
