Computer tutorials, tips, and tricks to help you learn Microsoft Excel, Access, Word, and lots more. Visit our full site at www.ComputerLearningZone.com.
Figuring out how many days two date ranges have in common comes up constantly in Access databases. Reservations, rentals, employee leave, utility bills, warranties, subscriptions, and just about anything else involving a start date and an end date can need this calculation. It sounds simple until you run into ranges that do not overlap, only partially overlap, or sit completely inside one another.
The good news is that you do not need a dozen different cases and a pile of spaghetti VBA to handle it. There is one simple rule: find the later start date, find the earlier end date, and see whether there is any time left between them. That is the overlap.
Suppose your first date range runs from January 13 through January 17. A second range might run from January 9 through January 11, which does not overlap at all. Or it might run from January 11 through January 19, which completely covers the first range. It could start before the first range and end in the middle, or start in the middle and end afterward. It could even fit entirely inside the first range.
All of those situations are handled with the same basic logic.
First, determine where the overlap starts. Look at both start dates and choose the later one. The two ranges cannot both be active until the later of the two ranges has begun.
Next, determine where the overlap ends. Look at both end dates and choose the earlier one. As soon as either range ends, the shared time is over. That is the part people often get backward, especially after copying and pasting code. The overlap end is the earlier ending date, not the later one. Tiny difference, huge consequences. Ask me how I know.
For example, if Range 1 is January 13 through January 17 and Range 2 is January 11 through January 14, the overlap begins on January 13 and ends on January 14. That gives you two shared calendar days if you are counting both dates.
On the other hand, if Range 1 ends on January 10 and Range 2 begins on January 15, your calculated overlap start would be January 15 while your overlap end would be January 10. Since the end comes before the start, there is no overlap. The answer is zero.
In VBA, this makes a nice small reusable public function. It receives four date values: the start and end of the first range, plus the start and end of the second range. Inside the function, you use a couple of local Date variables for the calculated overlap start and overlap end.
The basic comparison for the starting dates is simply: if Start1 is later than Start2, use Start1; otherwise, use Start2. For the ending dates, do the opposite: if End1 is earlier than End2, use End1; otherwise, use End2.
Once you have those two calculated dates, the safety check is easy. If the overlap end is less than the overlap start, return zero. Otherwise, subtract the overlap start from the overlap end to get the number of days between them.
Access date values are numbers behind the scenes, and one whole number represents one day. So for a day-based calculation, ordinary date math works perfectly well. You do not necessarily need DateDiff for this. DateDiff is useful when you need to work with things like months, years, or specific date boundaries, but plain subtraction is nice and clean for this particular job.
There is one business-rule detail you must decide for yourself: are your date ranges inclusive? In other words, do you count both the starting day and the ending day?
If someone is responsible for utility costs from January 8 through January 10, that is generally three calendar days: the 8th, 9th, and 10th. Basic subtraction gives you two, so you add one to include both endpoints.
But if you are calculating hotel nights, somebody checking in Monday and leaving Wednesday is usually charged for Monday night and Tuesday night, not Wednesday night. In that case, you would not add one. Neither approach is universally right. The important thing is to decide what your database means by a date range and apply that rule consistently.
Once the function is stored in a standard global module, you can call it from forms, reports, queries, other VBA procedures, and anywhere else in the database that can use a public VBA function. You might use it to check reservation conflicts, determine how many rental days fall inside a billing period, compare employee leave against a payroll cycle, or calculate whether a repair occurred during a warranty period.
For a tenant utility-bill situation, for example, you can compare each tenant's move-in and move-out dates against the utility billing period. The overlap calculation tells you how many days that tenant was responsible for during that bill. From there, you can divide costs however your particular rules require. The math is the easy part. Explaining the utility bill to three roommates is where the real programming challenge begins.
The full embedded video walks through building and testing the VBA function in Access, including the common copy-and-paste mistake of choosing the wrong ending date. It is a small function, but it is one of those handy little tools you will probably wind up using all over your database.
Before you compact a shared back end, restore a backup, replace a file, or make a table design change, it helps to know whether anyone is still using the database. Making changes while somebody is halfway through entering invoices is a fantastic way to turn an ordinary Tuesday into a much more interesting Tuesday.
Fortunately, Access leaves behind a pretty useful clue when a shared database is in use. It is not a perfect user-tracking system, but it is usually enough to tell you whether you should proceed with maintenance or stop and investigate first.
When Access opens a database for normal multi-user use, it generally creates a small companion file in the same folder as the database. If your shared file is an ACCDB or ACCDE database, the companion file uses the .laccdb extension. Older MDB databases use the older .ldb extension.
For example, if your shared back-end file is named CustomerData.accdb, Access will normally create a file named CustomerData.laccdb while someone is connected to it.
Think of the lock file as the occupied sign on a restroom door. If the sign is there, someone may be inside. That does not necessarily mean someone is actively doing anything at that exact moment, but it is enough of a warning that you probably should not start tearing out plumbing.
The lock file is part of the behind-the-scenes machinery Access uses to coordinate multiple users. It helps the database engine manage record locks and prevent users from stepping on each other's changes. You do not normally need to do anything with it. Do not rename it, move it, or edit it just because you see it sitting in the folder. It is not clutter. It is Access doing its job.
The simplest check is to browse to the folder containing the shared back end and see whether the LACCDB file exists. Be sure you are looking at the back end on the server or shared network folder, not at a user's front-end file. Each user should normally have a separate front end on their own computer, and that front-end lock file is not what matters for back-end maintenance.
If the back-end LACCDB file is not there, you can be reasonably confident that nobody is currently using that back end. That is usually a good time to perform your maintenance. If you are going to make design changes, compact the file, or restore a backup, opening the database exclusively is still a smart extra precaution. That prevents someone from wandering in halfway through the job.
If the LACCDB file does exist, treat the database as potentially occupied. Someone may be entering orders, running reports, editing records, or simply sitting at lunch with Access open. Either way, do not assume the back end is available just because nobody immediately admits to using it.
There is one important wrinkle: lock files can become stale. If Access crashes, Windows crashes, the computer loses power, Wi-Fi disappears, or the network connection drops unexpectedly, Access may not get the chance to remove the LACCDB file properly. The lock file remains behind even though nobody is actually connected.
That means the presence of a lock file is a warning, not absolute proof of a live user. The safest procedure is still to confirm that everyone is out of the application before deleting a stale lock file. And please make absolutely certain you are deleting the tiny LACCDB file, not the ACCDB database itself. It is surprisingly easy to click the wrong file when you are tired, in a hurry, or operating before coffee has had a chance to do its work.
If you have confirmed that everyone is out, you can usually delete the stale lock file. Access will create a new one automatically the next time someone opens the shared back end. If you are unsure whether the file is stale, do not make assumptions during a busy workday. Ask your users, check their workstations, or wait until after hours.
There is also a small but important detail with split databases. A user can have the front end open without immediately creating a lock file for the back end. This often happens when the startup form is an unbound main menu that has not yet opened a linked table, run a query, or displayed any back-end data.
In that situation, the user may technically have Access open, but they have not yet connected to the shared data file. Once they open a customer form, process an order, run a report, or otherwise touch a linked table, Access will normally open the back end and create the LACCDB file. For maintenance purposes, that is what matters: whether anyone is actually using the shared back-end file.
You can also check for the lock file programmatically with a small VBA function. The basic idea is simple: give the function the full path and file name of the back-end ACCDB file, remove the ACCDB extension, add .laccdb, and use the VBA Dir function to see whether that file exists.
The function returns a Boolean value. If the LACCDB file is found, it returns True. If the file is not found, it returns False. You can then call that function from a button on your main menu, from an automated maintenance routine, or before code attempts to compact, copy, replace, or otherwise work with the back end.
This is especially handy if you have an overnight process scheduled for midnight or 3:00 AM. Before the process starts manipulating the data file, it can check for the lock file and decide not to continue if users may still be connected. It is a simple safety check that can prevent a lot of trouble.
If your database uses more than one back-end file, you can perform the same check for each one. Some larger Access applications split tables across multiple back ends for archival data, email history, reporting tables, or other purposes. In that case, a more advanced routine can loop through the linked tables, identify the unique back-end files, and check each one without forcing you to maintain a long list by hand.
Sometimes you may want to know more than just whether the file exists. The contents of a lock file can often reveal the workstation names of computers connected to the database. If you open the LACCDB file in a text editor, you may see names such as the computer name and an Access user entry. The Access user name is often just Admin these days, so the workstation name is generally the more useful piece of information.
This can help you narrow down who might still be connected. If the lock file shows a workstation named ACCOUNTING-PC-3, you at least know where to start looking. It is not a complete security or audit system, but it can be enough to answer the practical question: "Who has this thing open?"
For more reliable tracking, consider building a login and logout system into your application. A good system can record the Windows user name, computer name, login time, and logout time. Then you can look for a login that does not have a corresponding logout. That is much more useful in a larger environment where walking around and asking everyone whether Access is open becomes a daily scavenger hunt.
Another useful approach is an administrative maintenance flag. Before you begin maintenance, place a small text file in the shared folder indicating that the database is offline. When users open their front end, the startup code checks for that file before touching any linked tables. If the maintenance flag exists, the application displays a message telling them the database is temporarily offline and then closes.
That gives you a controlled way to keep users from reconnecting while you compact the back end, restore a backup, or make design changes. It is much better than hoping nobody decides to open the database at exactly the wrong moment.
So the rule of thumb is simple. No lock file usually means you are clear. A lock file means someone may be connected, so verify before doing maintenance. And if a lock file remains after everyone is definitely out, it is probably stale and can be removed carefully.
The embedded video includes the complete VBA walkthrough, including how to create the lock-file check function and place it on a main menu button. It is a small tool, but it can save you from some very large headaches.
Time for a quick Microsoft Access quiz. This one covers a few important macro basics: what macros are for, where they can live, how to run one from a button, and how macro arguments affect the actions they perform. Keep score as you go, and no peeking at the answers until you have made your choice.
These are advanced-level questions, but they focus on concepts that every serious Access user should understand. Give yourself a moment on each one. If you need more time, pause the embedded video and think it through before reading the answer.
Question 1: What is a Microsoft Access macro primarily used for?
Is it used for creating actions without VBA, storing records in a table, designing printed reports, or creating table relationships?
Answer: A macro is primarily used for automating actions without writing VBA code. Macros can open forms, run queries, apply filters, display messages, export data, and perform plenty of other tasks. They are a great way to automate common user actions without immediately diving into Visual Basic for Applications.
Question 2: Where can an Access macro be stored?
Only in a table? As a standalone database object or attached to an event? Only in a report? Or only in a VBA module?
Answer: A macro can be stored as a standalone database object or attached to an event. For example, you can create a macro in the Navigation Pane just like you create a query or form. You can also attach a macro directly to an event property on a form, report, or control.
This flexibility is one of the handy things about Access macros. A standalone macro can be reused in different places, while an embedded macro can be created specifically for one form button or one event.
Question 3: Which event property is normally used to run a macro when a user selects a command button?
On Click, Unload, On Current, or On Print?
Answer: The correct property is On Click. When a user clicks a command button, Access fires that button's On Click event. You can attach a macro to that event to open another form, run a report, search for a record, close the current form, or do whatever task the button is supposed to perform.
The other events have their own purposes. On Current runs when a form moves to a different record, Unload occurs when an object is closing, and On Print is used with reports. For a normal command button, On Click is the one you want.
Question 4: What does a Where Condition in the Open Form macro action do?
Does it change the form's design, limit the records shown when the form opens, save the current record before opening the form, or prevent the form from being opened?
Answer: A Where Condition limits the records shown when the form opens. Suppose you have a Customer form and want a button that opens only the orders for the currently selected customer. The Where Condition lets you tell Access exactly which records should appear.
Think of it as applying a filter at the moment the form opens. The form itself does not need to be redesigned, and you do not need to create a separate form for every possible customer, employee, product, or other record. That would get ridiculous pretty fast.
Question 5: In a macro action, what are arguments?
Are they additional settings that control how the action runs, errors that stop the macro, names of all tables used by the macro, or security permissions for database users?
Answer: Arguments are additional settings that control how the action runs. Each macro action can have different arguments. For example, the Open Form action has arguments for the form name, view, filter name, Where Condition, data mode, and window mode.
The action tells Access what to do. The arguments tell Access how to do it. If you leave out an important argument, the macro may still run, but it may not behave the way you intended. That is usually when the fun begins.
So, how did you do? If you got all five, congratulations, you have slain the macro dragon and kept all the loot. If you missed a couple, no worries. Macros are one of those Access topics that become much easier once you start building a few practical examples.
You can watch the embedded video for the quiz and a quick review of each answer. These macro topics are also covered in more detail in Microsoft Access Advanced Level 1, Lesson 2.
When an Access database starts doing something weird, it is easy to fall down a search-engine rabbit hole and come back with ten different answers that all contradict each other. Some problems are simple, some are technical, and some involve a database you inherited from someone who left behind a button labeled "Do Not Touch." Knowing where to look for help, and when it is time to bring in a professional, can save a lot of frustration.
The good news is that Access is very much alive. It may not be the shiny new app featured in every Microsoft commercial, but businesses, schools, nonprofits, government offices, and plenty of small companies still rely on Access databases every day. That also means there is a large community of experienced developers, MVPs, consultants, instructors, and regular users who are willing to help.
Before posting a question anywhere, take a minute to search for it first. This is not just because people on forums get grumpy when the same basic question appears for the 800th time, although they do. It is because you can often solve the problem immediately.
If Access gives you an exact error message, search for the message word for word. If there is no error message, describe the symptom in plain English. Searches such as "recordset not updateable", "combo box not updating", or "Access query returning duplicate records" are much more useful than searching for something vague like "my database is broken."
There are decades of Access questions and answers online. Microsoft may have documented the issue, another developer may have explained it in a forum post ten years ago, or someone may have made a video walking through the exact problem. Searching first often gets you a faster answer, and it helps you ask a better question if you still need help afterward.
For questions about what Access is supposed to do, Microsoft's own documentation is a good starting point. Microsoft provides reference material for built-in features, commands, keyboard shortcuts, functions, properties, and other parts of Access. If you are wondering what a button does or how a feature was designed to work, think of the documentation as the owner's manual.
Also remember that Access has built-in Help. Press F1 while you are working in Access, and it will often open help related to what you are currently doing. It may not always explain things in the friendliest beginner language, but it can point you toward the correct feature, property, or command.
Once the problem becomes specific to your database, forums are usually where the real magic happens. Maybe your query returns the wrong records, your form is not updating properly, your VBA code produces an error, or you are trying to figure out how to structure a database correctly. That is where experienced Access people can look at your particular situation and help you work through it.
Access World, also known as AccessProgrammers.co.uk, is one of the largest dedicated Access forums around. It is an excellent place for more advanced topics involving SQL, VBA, database design, automation, recordsets, and those strange problems that make you wonder whether your database is haunted. There are long-time developers and Microsoft MVPs there, including people who have been building Access applications for decades.
AccessForums.net is another excellent resource, especially for beginner and intermediate users. If you are learning about tables, relationships, queries, forms, reports, and the basic building blocks of an Access application, you can usually get helpful explanations without being made to feel like you should have been born knowing how to write a parameter query.
For Microsoft 365 issues, current Access changes, update problems, and discussions involving newer features, the Microsoft Tech Community is worth checking. It can be particularly useful when something that used to work suddenly stops working after an Office update. Sometimes the problem is not your database at all. Sometimes an update changed behavior, introduced a bug, or affected a feature that had been working perfectly yesterday.
Reddit also has an active r/MSAccess community. It is generally more informal and moves quickly. It can be useful for quick questions, design opinions, career discussions, or seeing how other developers approach a particular problem. Just remember that any public forum can contain a mixture of excellent advice, questionable advice, and advice that sounds confident enough to be dangerous.
The trick is to match your question to the right place. A basic question about creating a form may be better suited to a beginner-friendly Access forum. A complicated VBA issue involving Windows API declarations, SQL Server connections, or automation with Outlook may get better attention in a more technical community. Asking the right crowd saves everyone time.
You can also visit the Access Learning Zone forums. There is an area for visitors, plus a helpful community of students, moderators, and experienced developers. It is a good place to ask questions, learn from other people's problems, and get pointed toward lessons that may cover what you are trying to do.
Getting good answers depends heavily on asking a good question. "My database doesn't work. Help." is not really a question. It is the database equivalent of calling a mechanic and saying, "My car makes a noise." What kind of noise? When does it happen? Is the car on fire? Details matter.
Start by explaining what you are trying to accomplish. Then explain what happens instead. For example: "I am trying to show only customers with unpaid invoices, but my query returns every customer." That gives someone both the goal and the symptom. They now have something useful to investigate.
Include the exact error message whenever possible. Copy and paste it word for word. "I got an error" does not tell anyone much. An actual error number and message can immediately point an experienced developer toward the likely cause.
Screenshots can be very helpful for query design grids, form properties, relationship windows, error messages, and layout issues. Just make sure you remove or hide private information first. Do not post customer names, passwords, account numbers, email addresses, or anything else that should not be floating around on the Internet.
If the problem involves SQL or VBA, include the relevant SQL statement or code as actual text whenever possible. Screenshots of code are harder to read, copy, test, and correct. At the same time, do not paste all 6,000 lines of your database's code module when the problem is probably in a three-line section. Give people the smallest useful example that demonstrates the problem.
A descriptive subject line helps, too. "Need Help" tells people nothing. "Access combo box does not update after adding new vendor" is much more likely to get the attention of someone who knows the answer.
It is also helpful to explain what you have already tried. If you tested a particular approach, changed a property, rebuilt a query, or searched for an error message, say so. That keeps people from spending their time suggesting things you already know do not solve the problem.
And when someone helps you, come back and say whether the solution worked. A simple "That fixed it, thank you" goes a long way. It lets the person who helped know they were successful, and it helps the next person who finds the thread through a search engine.
Sometimes, of course, you do not need a quick answer. You need someone to build something. Maybe you are on a deadline. Maybe the project is beyond your current skill level. Maybe you inherited an old database with 47 forms, six mystery tables, and code that looks like it was written during the Clinton administration.
That is when it may be time to hire a tutor or consultant. A tutor helps you learn how to make changes yourself. They can review your database with you, perhaps over Zoom, explain what is happening, and help you build your own skills. A consultant is usually hired to design, repair, or build part or all of the database application for you.
Microsoft MVPs can be a good starting point when you are looking for experienced professionals. MVP is a recognition from Microsoft for people with established expertise and community contributions. It is not a magical guarantee that every MVP is the perfect fit for every project, but it is certainly a positive sign.
Whether you hire someone or not, make sure they listen to what you actually need. Be wary of anyone who promises to build the Death Star in three days, especially if they have not asked a single question about your tables, users, reports, security, or business process.
Training is valuable even if you eventually plan to hire someone else to do the work. The more you understand Access, the better questions you can ask, the better decisions you can make, and the less dependent you will be on a single developer for every little change. You do not need to know how to rebuild an engine to take your car to a mechanic, but knowing the difference between an oil change and a transmission problem is useful.
Try to learn sound database principles, not just which button to click. Buttons move around. Ribbon layouts change. Good concepts like normalization, relationships, query design, and proper form structure will stay useful no matter how Access changes over the years.
If you want to keep up with Access news, sites like Access Forever and No Longer Set are useful resources. They cover Access updates, bug reports, product announcements, community news, and other developments that may affect developers. You do not need to become an Access news junkie who wakes up each morning wondering what happened overnight in the exciting world of combo boxes, but it is good to know when a major update affects something you use.
The important thing is to remember that help is out there. Search first, use official documentation for built-in features, find a community that matches your question, provide useful details, and know when a problem has crossed the line from "I need an answer" to "I need professional help."
For a fuller walkthrough of these resources and some additional advice on getting better answers from the Access community, watch the embedded video.
"How do I pull data in Microsoft Access?" sounds like a simple question, but it can mean several completely different things. You might want to display a customer's name from another table, copy an address into an order, import an Excel spreadsheet, retrieve one phone number, or export records to another program. Access has tools for all of those jobs, but they are not the same tool.
The key is to stop thinking in terms of "pulling data" for a moment and describe exactly what needs to happen. Where is the data now? Where does it need to go? Are you only displaying it, or do you need to save a permanent copy? Once you answer those questions, the right Access technique is usually pretty obvious.
The most common situation is displaying information from a related table. For example, suppose you have a Customer table and an Order table. Each order stores a CustomerID, but when you look at an order, you also want to see the customer's name, address, phone number, and email address.
The beginner instinct is often to copy all of the customer information into every order record. Usually, do not do that. Store the customer information once in the Customer table, store the order information in the Order table, and connect them using the CustomerID.
Then create a query that joins the two tables on CustomerID. The query can display fields from both tables together, even though the data remains stored separately. This is one of the main reasons relational databases exist in the first place.
For example, an order query might show the OrderDate, OrderTotal, CustomerName, Phone, and Email. The order information comes from the Order table, while the customer details come from the Customer table. Nothing has to be copied just so you can see it on a form or report.
This is also why relationships matter. The Customer table is the parent table, and the Order table is the child table. One customer can have many orders, so the Order table stores the CustomerID as its foreign key. When you build the query, Access uses that matching ID to bring the records together.
Be careful with join types, too. A normal inner join only shows records where Access finds a match in both tables. That is usually fine for valid orders with valid customers. But in some situations, you may need an outer join so that Access still shows all records from one table even if a matching record is missing in the other. This can be useful for finding orphaned records, incomplete imports, or customers who have not placed any orders yet.
Sometimes, however, you really do need to copy data from one table into another. This is not automatically bad database design. The question is whether the copied value needs to remain historically accurate or be independently editable.
A shipping address is the classic example. A customer places an order today, and you ship it to their current address. Six months later, the customer moves and you update their address in the Customer table. If your old order simply displays the customer's current address, it will now look like you shipped that package to the new address. That is historically wrong.
In that case, copying the shipping address into the order record is exactly the right thing to do. The order needs its own snapshot of the shipping address as it existed when the order was placed. The same idea applies to product prices, tax rates, commissions, discounts, shipping methods, and other transaction-specific values.
This kind of duplication is intentional. It is not the same as blindly storing the same customer phone number in fifty different places because someone did not want to learn how joins work. One is a historical snapshot. The other is a future maintenance nightmare.
You can copy values with an append query, update query, macro, or VBA code. Which method is best depends on when the copy needs to happen. If the values should be copied automatically when a new order is created, a form event or VBA routine is often appropriate. If you are fixing or updating many existing records at once, an action query may be the better choice.
Another common meaning of "pull data" is bringing information in from outside Access. Maybe you have an Excel spreadsheet, a CSV file from a bank, a text file from a vendor, or data stored in another Access database. In that case, you are usually talking about importing or linking data.
Importing creates a copy of the outside data inside your Access database. Linking leaves the data in its original location but lets Access work with it as though it were a table. Both approaches have their place.
If you receive a spreadsheet every month from someone who believes column headings are merely suggestions, do not import it directly into your production tables. Import it into a staging table first. A staging table gives you a safe place to clean up bad values, remove duplicates, validate dates, translate text values, and make sure the imported data matches your database structure before it reaches your real tables.
For example, one spreadsheet might say "Florida," another might say "FL," and a third might say "Fla." You do not want all of that mess going directly into a properly designed State table. Clean and standardize it in staging first. Future You will appreciate this. Future You may even send Present You a nice card.
Sometimes you only need one value, such as displaying a customer's phone number after selecting that customer in a combo box. In that situation, a domain aggregate function such as DLookup can be convenient. You select the customer, and Access looks up the corresponding phone number from the Customer table.
DLookup is fine for a simple lookup on a single form. It is not something you want to repeat hundreds of times on a large continuous form when a query join would do the job more efficiently. If you are displaying a whole set of records, use a query. If you need one quick value in one place, DLookup can be perfectly reasonable.
You may not even need DLookup if the value is already available. A combo box can contain hidden columns, so you can select a customer by name while also retrieving their phone number, email address, or other fields from the combo's existing row data. Likewise, a subform can reference a value on its parent form, and one open form can reference a control on another open form.
Another meaning of "pull data" is simply retrieving a group of matching records. "Pull all unpaid invoices," "pull orders from last month," or "pull every customer in Florida" are usually requests for a select query.
A select query does not copy or move anything. It reads the stored records, applies criteria, sorts the results, performs calculations if needed, and returns the matching records. That query can then become the record source for a form or report, or it can be opened in VBA as a recordset for further processing.
For instance, if you want all unpaid invoices from the previous month, build a query using the invoice table, add criteria for the unpaid status and date range, and let Access return the matching records. That is pulling data in the sense that most people mean it, but nothing is actually being duplicated.
The phrase gets even more confusing when Excel is involved. Someone may say, "I want to pull data from Access into Excel." From Excel's point of view, it is pulling data. From Access's point of view, you are exporting data.
That distinction matters when asking for help. If you are working in Access and want query results sent to Excel, say that you want to export to Excel. Access can export query results to Excel, create CSV or text files, generate PDFs from reports, export to Word, or send information to other systems.
Data can also be pulled from websites, APIs, SQL Server, and other remote sources. That is possible, but it is a different level of project. A web API may require HTTP requests, authentication, JSON or XML processing, and VBA. Web scraping may require browser automation and can break the next time the website changes its layout because somebody moved a button three pixels to the left.
If a website provides an API, that is usually the cleaner and more reliable choice. APIs are designed to provide structured data to other programs. Scraping visible web pages should generally be the fallback option, not the first choice.
There is also synchronization, which is more than simply importing or exporting. Synchronization means two sources need to exchange changes over time. Maybe Access checks an Excel file nightly, downloads records from SQL Server, or merges records from two offices.
Synchronization gets complicated fast when both sides can change the same record. You need unique identifiers, a way to identify new and changed records, timestamps or version numbers, conflict rules, and a plan for deletions. That is a real database project, not just a query with a fancy hat.
Finally, there is a useful middle ground between displaying live data and preserving historical data: copying a default value into a new transaction. A customer may normally receive a 10 percent discount, but a particular order may need 15 percent. A product may have a standard price, but a salesperson may override it for one sale.
In those cases, Access can retrieve the customer's usual discount or the product's current price and copy it into the new order. That copied value becomes part of the transaction and can be changed without affecting the original customer or product record. This is very common in order-entry databases.
So before asking, "How do I pull data in Access?" try to define the job more clearly. Is the data in another table, an Excel file, a website, or another database? Do you want it on a form, in a report, in another table, or exported to a file? Do you need one value or thousands of records? Should the result be live, or should it be saved permanently?
If you just need to display related information, use a query with a join. If you need a historical snapshot or an independently editable transaction value, copy it intentionally. If the data comes from outside Access, import or link it, preferably through a staging table. Once you know what "pull" actually means for your situation, the solution becomes much less mysterious.
Watch the embedded video for a fuller discussion and demonstrations of the different ways Access can retrieve, display, copy, import, and export data.
You can see the data. You can scroll through the records. Everything looks normal. But the moment you try to type into a field, Access beeps at you, locks the control, or acts like your database has suddenly joined a monastery and taken a vow of silence. This is one of the most common Access problems, and the form itself is often not the real culprit.
A form is really just a window onto a table or query. If Access cannot safely determine which underlying record should be changed, or if you do not have permission to change it, the form becomes read-only too. The trick is to troubleshoot it in the right order, starting with the data source and working your way up through the query, form properties, controls, joins, and possibly even VBA code.
The first question to ask is: can you edit the table directly? Open the underlying table in Datasheet View and try changing an ordinary stored field, such as a customer's last name. Do not test an AutoNumber, a calculated field, or something that is supposed to be locked. Just pick a normal text, number, or date field and see whether Access lets you save a change.
If you cannot edit the table itself, then the problem is below the form and query level. Your database may have been opened read-only, the file or folder may have Windows permissions preventing changes, or you may be working on a network share where you have read access but not modify access. Access also needs to be able to create its lock file in the folder. If it cannot, strange things can happen.
This is especially important with a split database. Your front-end file might be sitting safely on your local computer, but the back-end file containing the tables could be in a shared folder where you do not have permission to update data. The front end may open just fine, but editing records is another story.
Linked tables deserve extra suspicion. A linked SQL Server table, SharePoint list, another Access database, Excel workbook, CSV file, or text file follows the rules of its original source. Access cannot magically grant you update permissions that you do not have elsewhere.
For example, a linked SQL Server table may be perfectly valid, but your SQL Server login may not have UPDATE permission. A SQL Server view may display records but not allow changes, particularly if the view combines tables, includes totals, or has special logic. In that case, fiddling with form properties in Access will not solve the real problem.
Excel and text files are another common trap. They are fine for importing data, but they are not great as live multi-user data sources. Depending on the driver and link configuration, Access may treat linked spreadsheet or text data as read-only. Even when editing appears to work, it can be unreliable. If you need to maintain data, import it into a proper Access table and work from there.
There is also record locking. If another user has a record open and locked, you may be unable to edit that particular record until they are finished. Normally this affects one record at a time, although locking settings and multi-user activity can make it seem more widespread.
If the table edits correctly, move up one level and check the form. Open the form in Design View, click the little square in the upper-left corner so you are selecting the form itself, and then look at the Data tab in the Property Sheet.
The big property is Allow Edits. If it is set to No, users can browse existing records but cannot change them. This is useful for display-only forms, but it is also very easy to set once, forget about, and then spend an hour wondering why nothing works.
Also check Allow Additions and Allow Deletions. These do not normally stop users from editing existing records, but they explain why someone may not be able to add a new record or delete an old one. I often turn Allow Deletions off and provide my own delete button with an "Are you sure?" prompt. Users are remarkably talented at clicking Delete right before they realize they needed that record.
Data Entry confuses a lot of people too. Setting Data Entry to Yes does not mean "let me edit data." It means the form opens ready for entering new records and does not show existing ones. It is for data-entry forms, not for enabling editing of current records.
Another important form property is Recordset Type. A normal editable form should generally use a Dynaset. A Snapshot is read-only by design. Think of a snapshot as a picture of the data taken when the form opens. You can look at it, scroll through it, and admire it, but you cannot write on the photograph.
If the form is editable but one particular control will not let you type, then inspect that control in Design View. Check its Locked property. A locked control displays its value but prevents changes. This is common for fields such as OrderID numbers, calculated balances, and other values users should see but not alter.
Also check whether the control is Enabled. A disabled control is generally grayed out and cannot be clicked. In most cases, Locked is preferable to Enabled when you want users to see and copy a value. Disabled controls can be awkward, and they often look like something is broken.
The control's Control Source matters too. If it contains a normal field name such as LastName, then the text box is bound to a field in the form's record source. If it begins with an equal sign, such as a calculation like =Quantity*UnitPrice, then it is an expression. Access can display the result, but there is no single field where it can store a new value.
You cannot type directly into a calculated full name, a calculated extended price, or an assembled address block. You need separate bound controls for the actual stored fields. Edit Quantity and UnitPrice, for example, and let Access calculate ExtendedPrice for display.
Now let us talk about queries. A simple SELECT query based on one table is usually editable. That is your starting point. If a simple one-table query works, but your bigger query does not, then something in the more complicated query is making the recordset read-only.
Some query types are not designed for editing at all. Totals queries using GROUP BY, Sum, Count, Average, Min, or Max summarize multiple records into one result. If a query shows total sales by customer, what exactly would changing that total mean? Which invoice should Access modify? Access cannot read your mind, and before coffee, neither can I.
Crosstab queries, UNION queries, pass-through queries, and action queries are also generally not editable in the normal Datasheet View sense. Action queries exist to update, append, delete, or create data. Crosstab queries turn values into column headings. UNION queries combine results from multiple queries. They are useful tools, but they are not intended to serve as editable record sources.
Even something as innocent-looking as DISTINCT can cause trouble. When you set a query's Unique Values property to Yes, Access removes duplicate rows from the result. That is useful for generating a clean list of cities, categories, or other values, but Access may no longer see the result as a straightforward list of individual table records that can be updated.
Calculated fields require a little nuance. The calculated column itself is never directly editable. However, the underlying fields may still be editable if the rest of the query is simple enough. Once you add enough calculations, DISTINCT settings, joins, aggregates, or nested saved queries, Access may decide the whole result is not safely updateable.
Do not forget that a query can inherit problems from another query. If Query B uses Query A, and Query A is a totals query, crosstab query, UNION query, or otherwise read-only query, Query B is not going to suddenly become editable just because you gave it a nicer name.
The fastest troubleshooting method is to build upward from something simple. Start with the table. Then create a new basic SELECT query using that one table. Then create a simple form bound directly to the table. Test each step. Once you know that basic setup works, add one feature at a time and test after each change.
Add a join, test it. Add another join, test it. Add a calculated field, test it. Add DISTINCT, test it. Add a totals row, and odds are good you just found your problem. This is much faster than staring at a giant 50-field query and hoping inspiration strikes. Troubleshoot it like Christmas lights: add one section at a time and find the point where everything goes dark.
Joins are one of the most common reasons a query becomes read-only. Access needs a reliable way to identify each record. That is why every table should normally have a proper primary key. The primary key is the record's license plate. Without one, Access may not know which record it is supposed to update, especially in linked tables and multi-table queries.
A normal one-to-many relationship is usually straightforward. For example, Customer.CustomerID joins to Order.CustomerID. One customer can have many orders, and every order points back to one customer. That is a clean relationship based on key fields.
Do not join tables using LastName, CompanyName, or some other value that can repeat or change. Two people with the same last name are not a relationship. That is a family reunion, and somebody is probably about to argue over potato salad.
Outer joins can also restrict what Access can update. An outer join is useful when you want to show all customers even if they have no orders, for example. But when you start mixing outer joins, multiple tables, and calculated or aggregate queries, Access has a harder time determining what one row in the result actually represents.
Many-to-many relationships are another place where people often build a giant monster query and then wonder why editing gets weird. A student can take many classes, and a class can contain many students. The proper design uses a junction table, such as Enrollment, between Students and Classes.
Instead of trying to edit Students, Classes, and Enrollments all in one enormous query, use forms and subforms. Put a student on the main form and show that student's enrollments in a subform. Then build another form with a class on the main form and the enrolled students in a subform. It is cleaner, easier for users, and much easier for Access to manage.
When you expect to edit records from the many side of a one-to-many relationship, include that table's primary key in the query, even if you hide it on the form. It helps Access identify the actual record. Better yet, where appropriate, use a parent form and subform setup rather than forcing a complicated multi-table query to do everything.
Good table design prevents a lot of updateability headaches before they start. Use primary keys. Use proper foreign keys. Build relationships on IDs, not names. Avoid storing calculations when you can calculate them on the fly.
If ExtendedPrice is Quantity times UnitPrice, storing all three values means they can get out of sync. Change the quantity but forget to update the stored total, and now your database is telling two different stories. Calculate derived values in a query, form, or report whenever practical.
Lookup fields in tables are not usually the direct cause of a read-only form, but they confuse beginners constantly. A table lookup may display a customer name while storing a CustomerID behind the scenes. I generally prefer to keep tables simple and use combo boxes on forms for friendly lookup displays. It makes the design clearer and saves you headaches later.
If you use VBA to control your forms, the code may be the culprit. It is very easy to add a line that sets AllowEdits to False when an order is paid, shipped, approved, or otherwise finalized. That may be perfectly sensible at the time. Six months later, though, you may be asking why nobody can edit the form anymore.
Check the form's Load and Current events, along with any button-click code that changes control properties or form settings. Also check any DAO or ADO recordsets your code opens. A DAO Snapshot is read-only, and ADO cursor and lock settings can determine whether updates are allowed.
For SQL Server and other external databases, remember that Access cannot override server security. The user account needs INSERT, UPDATE, and DELETE permissions as appropriate. If SQL Server says no, Access is just the messenger standing at the front desk with bad news.
Trusted locations can matter too. If the database is not trusted, macros and VBA code may be disabled. That does not usually make a normal Access table read-only by itself, but it can stop the code that was supposed to prepare your form for editing.
Finally, corruption is possible. It is not the first thing I assume, but Access occasionally has a bad hair day. Make a backup, run Compact and Repair, and test again. If a particular query, table, or form seems damaged, try importing a known-good copy from a backup or recreating the object.
The overall troubleshooting process is simple: test the table first, then a brand-new simple query, then a simple form. If those work, add complexity back one piece at a time. Once you find the join, calculation, query property, form setting, permission issue, or bit of code that breaks editing, you have found the real problem.
When a form or query becomes read-only, do not immediately blame the form. Start underneath it. Access is usually refusing to edit for a reason, even if it does a terrible job of explaining that reason. For a full walkthrough of these checks and demonstrations of where to find the relevant properties, watch the embedded video.
Class modules are one of those Access features that make perfectly capable developers stare at the Create tab and think, "Well, that looks advanced. I'll just pretend it isn't there." The good news is that class modules are not magic, and you do not need to use them to build great Access databases. They are simply another way to organize VBA code when you need it.
The important thing is understanding what class modules are good for, how they differ from standard modules and form code, and when they are just unnecessary complexity. Because, honestly, nobody gets an Access merit badge for turning a simple database into an object-oriented science project.
A standard module is a general container for VBA code. It is where you put public functions, subroutines, constants, declarations, and utility code that can be used throughout the database. You might have a module for email routines, one for date calculations, one for window positioning, or one named GlobalMod that contains common functions used everywhere.
For example, a standard module is a good place for a function that formats a phone number, checks whether a form is open, calculates the next business day, exports a report to PDF, or sends an invoice email. You write the public function once and call it directly from wherever you need it: a form, report, query, macro, or another VBA procedure.
Standard modules are usually the right choice when you have a general-purpose tool. The code performs a task, returns a value, or carries out an action, but it does not need to remember separate information about multiple individual things.
One important detail is that a module-level variable in a standard module is shared during the current Access session. There is one copy of it. It is not automatically separate for each customer, employee, invoice, or form. That is fine for many uses, but it is where class modules can become useful.
A class module is different because it defines a custom object. Think of it as a blueprint. A blueprint is not a house, but you can use it to build multiple houses. Each house can have its own color, occupants, furniture, and questionable decorating decisions, even though they all came from the same plan.
In VBA, the class module is the blueprint. An object created from that class is an instance. If you create a class named CLSEmployee, you can create one employee object for Jim, another for Spock, and another for Susan. Each employee object has its own stored information.
That is the main reason classes exist: they let you work with multiple separate things at the same time, with each thing carrying around its own data and behavior.
An employee class might have properties such as employee name, employee ID, hire date, pay rate, and active status. It might also have methods that perform employee-related work, such as calculating pay, deactivating the employee, or returning a formatted display name.
Properties describe an object. A name, date, amount, status, or ID are all examples of properties. Methods are actions that the object can perform. A method might calculate a total, validate a value, save a record, or display a greeting.
Most class modules keep their actual data in private variables. For example, an employee name might be stored internally in a private variable such as mEmployeeName. Code outside the class cannot directly change that variable. Instead, it has to use the public properties and methods that you expose.
This is called encapsulation, which is just a fancy programmer word for keeping the internal plumbing inside the object. Other code can use the controls you provide, but it cannot climb into the Jefferies tubes and start pulling wires at random.
In VBA, a Property Get procedure retrieves a value, while a Property Let procedure assigns an ordinary value such as text, a number, or a date. So an EmployeeName property can allow outside code to set an employee's name and later retrieve it, while the actual storage remains private inside the class.
There is also Property Set, which is used when assigning an object reference rather than a normal value. If you are assigning a form, recordset, database object, or another custom object, you use Set. If you are assigning text, numbers, dates, or Yes/No values, you use the regular equals sign.
If you have worked with DAO recordsets, you have already used objects and object references. Statements such as setting a database variable to CurrentDb or setting a recordset variable to the result of OpenRecordset are object-oriented VBA, whether you realized it or not.
To use your own class module, you first declare a variable of that class type. For example, you might declare a variable as CLSEmployee. At that point, you have a variable that can hold a reference to an employee object, but you have not created the actual object yet.
To create the object, use the New keyword. In VBA, you would assign the new object reference with Set. Once that is done, you have a fresh employee object in memory, ready to hold its own values.
You could create two employee objects, assign one the name "Jim" and the other the name "Spock," then call a DisplayGreeting method on each one. Each object remembers its own EmployeeName value. They are both built from the same CLSEmployee blueprint, but they are separate instances with separate state.
The full video demonstrates this with a small CLSEmployee class. It contains a private employee-name variable, public Property Let and Property Get procedures, and a simple method that displays a greeting. The point is not that you need a class just to say hello to somebody. That would be using a flamethrower to light a birthday candle. The point is to see how the pieces fit together.
When you are done with an object variable, it is good practice to release it by setting it to Nothing. A simple rule to remember is: if you set it, forget it. VBA often cleans up local objects automatically when a procedure ends, but explicitly releasing object references is a good habit, especially when objects have a longer lifetime or hold other objects internally.
Class modules become more useful when an object has several related pieces of information and several related actions. An employee with a first name, last name, ID, hire date, pay rate, and active status is a reasonable candidate. So is an invoice that contains header information, line items, and a method to calculate its own total.
A customer object might contain customer data along with methods to determine customer status, format a display name, or check discount eligibility. A shopping cart object could add and remove items, calculate tax, and return a total. In each case, the class groups related data and the logic that belongs with it.
Classes can also help when you need multiple separate objects at once. You might have ten invoice objects in memory, each with its own customer, status, line items, and total. With a standard module variable, those values could step on each other. With separate class instances, each invoice keeps its own information.
Another advanced use is reusable behavior across forms. Class modules can respond to events and can be used to manage groups of controls on multiple forms. For example, developers sometimes use classes to apply common button behavior or control events across an application. That is useful, but it can get complicated quickly, so it is not where I recommend beginners start.
Classes also have built-in lifecycle events. Class_Initialize runs when an object is created. It is useful for setting defaults or creating internal objects, such as a collection that the class will use. Class_Terminate runs when the object is being released from memory and can be used for cleanup.
It is worth pointing out that you already work with classes all the time in Access. A form is an object. A report is an object. A text box is an object. A recordset is an object. Access itself is a large collection of objects.
When you write Me.Requery in a form module, you are calling a method on the current form object. When you check Me.Dirty or change Me.Caption, you are working with form properties. When you write code in a form's Before Update event, you are responding to an event raised by that form object.
The code behind a form or report is effectively a class module associated with that particular form or report. It has special access to that object's controls, properties, and events. A standalone class module is simply one you create yourself in the Visual Basic Editor, usually with Insert Class Module.
By convention, I usually begin custom class module names with CLS, such as CLSEmployee or CLSInvoice. You do not have to use that naming convention, but it makes it immediately obvious that you are looking at a class rather than a standard module, form module, or report module.
Do not confuse a class with a table. Your customer information still belongs in a properly designed Customer table. Your invoices, employees, and line items still need proper relationships, keys, and normalization. A class can represent a customer temporarily in VBA memory while your code is working with that customer, but it does not replace relational database design.
That is a beginner mistake worth avoiding: thinking classes are somehow a replacement for tables. They are not. Tables store your data. Classes organize code and temporary in-memory objects. Those are two very different jobs.
For most Access databases, standard modules, form modules, report modules, tables, queries, forms, and reports are more than enough. I have built and taught Access databases for decades, and while class modules have some nifty uses, they are not a requirement for building professional applications.
Use a standard module when you need a general tool. Use a class module when you have a cohesive thing with related data and behavior, especially if you need multiple independent copies of that thing. And if a simple public function does the job cleanly, use the simple public function. There is no prize for making the architecture more complicated than the database needs.
Once you are comfortable with the basics, class modules can do quite a bit more. You can create calculated read-only properties, validate values before accepting them, build objects from table records, manage groups of objects with collections, and create reusable event-handling systems. Those are useful next steps, but there is no need to swallow the whole object-oriented textbook in one bite.
For now, just remember the big picture: a standard module contains shared utility code, while a class module defines a blueprint for objects. Each object instance has its own private state, public properties, and methods. That is all a class really is.
If you want to see the CLSEmployee example built step by step, including creating the class, making two employee objects, setting their properties, and calling their methods, watch the embedded video for the full walkthrough.
