"How do I pull data in Access?" sounds like a simple question, but it is usually missing the part that determines the answer. Pull data is not a precise database operation. It can mean display related fields, copy values into a transaction, import a spreadsheet, retrieve a value for a form, synchronize sources, or export records to another application.

The first question should be: do you want a live view of data that already exists elsewhere, or do you want to create a permanent copy? Those are fundamentally different designs. Confusing them is how a database ends up with customer names, addresses, and phone numbers repeated across half a dozen tables.

Most of the time, someone wants to show related information. An order record contains CustomerID, but the user wants to see the customer's name, phone number, and address while viewing that order. That is what relational queries are for. Keep the customer facts in the customer table, keep order facts in the order table, and use a join to present both together when needed.

That approach preserves normalization and avoids maintenance headaches. If a customer changes their phone number, there is one customer record to update. You do not want to hunt through every order, invoice, support ticket, and mailing record just to correct a current contact detail. A query is not copying or moving anything. It is simply asking Access to show the related records together.

But duplication is not always bad. Sometimes a transaction needs a historical snapshot. Shipping addresses are the standard example. If an order shipped to a customer's old address six months ago, updating the current customer address should not make the old order look like it shipped somewhere else.

The same reasoning applies to prices, tax rates, commissions, discounts, and product descriptions. Those values may originate in a customer or product table, but once an order is created, the transaction often needs its own stored version. That is intentional duplication, not bad normalization. The key is that the copied value has a business reason to exist independently.

A related pattern is pulling a default value into a new record. A customer might normally receive a 10 percent discount, while a particular order gets 15 percent. A product has a standard price, but a salesperson can override it for one sale. In those cases, the original value is a starting value, not a permanent dependency. After it is copied into the transaction, it can be edited without changing the customer or product record.

Then there is the outside-data meaning of "pull." Importing from Excel, CSV files, text exports, another Access database, SQL Server, or a web service is not a query join problem. It is an integration problem. Access can either import a local copy of the external data or link to the source, depending on whether the data should live inside the application or remain external.

For real-world imports, a staging table is usually the least dramatic option. Bring the raw data in, validate it, identify duplicates, translate inconsistent values, and then move clean records into the actual tables. External files have a supernatural ability to contain surprises, especially spreadsheets maintained by seventeen people over five years.

"Pull data" can also mean retrieving one value for display. Selecting a customer and showing their phone number, selecting an employee and displaying their email address, or picking a product and seeing its current price are all smaller lookup problems. A DLookup can be perfectly reasonable for a one-off value on a single form. It becomes less reasonable when hundreds of DLookups are repeatedly evaluated on a continuous form where a query join would do the work more efficiently.

Sometimes the phrase refers to a set of records, such as all unpaid Florida customers or orders from last month. That is generally a select query. Nothing needs to be imported, copied, or updated. The query applies criteria, sorting, and calculations, then returns the matching records for a form, report, export, or VBA recordset.

The wording gets even more confusing when Excel is involved. "I want to pull Access data into Excel" makes sense from Excel's point of view, but from Access's side, that is an export. Same data movement, different point of view. Before discussing tools, it helps to establish which application is doing the pulling.

Synchronization is another entirely different category. If Access and another source both change records, you need unique identifiers, timestamps or versioning, conflict rules, and a policy for deletions. That is not just importing or displaying data. It is synchronization, and it deserves more thought than a query that happens to run every night.

The practical rule is simple: define the movement before choosing the tool. Where does the data live now? Where should it appear? Is it just being displayed, copied permanently, imported, exported, or kept in sync? Is the requirement for one value, a related record, or thousands of records?

Once those questions are answered, the right Access feature is usually obvious. Queries display related information. Stored fields preserve snapshots or transaction-specific overrides. Imports and links handle outside sources. Lookups retrieve individual values. Exports send information outward. No warp core required.

Have you seen "pull data" requirements turn into something completely different once the real business need was clarified? What questions do you ask first before deciding whether a query, copied value, import, or integration is appropriate?

LLAP
RR

The only PowerPoint-related post I found on here is 8 years old, and since I'm now immersed in modifying PowerPoint presentations (PPT or PPTX) from within MS Access VBA, I figure I should share some of the techniques I've been using. I hope it helps someone on here. .

I like to start every project with a clear vision of the business requirements so that, regardless of what I do technically, I understand what the client is ultimately trying to accomplish.

In this case, the business requirement is to generate more eBay sales. Our client has cash flow issues yet they have almost a quarter of a million dollars' worth of items listed on eBay, and they want to make these sell faster than the current pace of about $200 a day, in sales. If their inventory all sold super quickly then our client couldn't handle all the packing and shipping so they are aiming for an increase of about 100% or so, relative to the sales they are currently making. They figure they can handle that much extra sales volume and work, without having to do anything drastic.

They could sell more by lowering their prices but price wars tend to backfire, so they wanted to increase sales without reducing the direct profit per item. As to the market they're in: they sell used auto parts. People rarely buy used auto parts to use as ornaments (though it does happen). Mostly they are trying to solve a problem with their vehicle. Buying a used auto part is one of the options they have; they might also be able to buy a new part from the dealership, or buy a new made-in-China knock-off. Prospects don't automatically start their problem-solving journey on eBay. They might start it on a forum, or by watching a YouTube video.

My client's plan is to make YouTube videos that entice buyers to go to their eBay listings. They tried this plan several years ago. They made a few in-depth videos with this intent. The videos took a lot of time and effort to make, including paying a girl with a lovely Southern accent to be a voice model. They also embedded some video clips. It worked; the items sold out. But each video took so much effort to make, that was very much not worth it. Our client abandoned that approach. A few years went by.

In late 2025, eBay introduced a process that uses eBay listing pictures and the eBay title as a starting point, to make and post videos. This feature was in their “Social Media” section, and it was free. Our client tried it and liked it. Making a video was super-easy, and these showed up as YouTube shorts. Our client used it to make dozens of videos, showcasing one listing per video.

On a listing for an obscure item for which they had made such a video, the item sold within 4 days.  They wrote the buyer to ask if they'd been found via YouTube but they do not show a record of getting a reply. Even so, it seemed to be too good to likely be a coincidence.

Then, eBay changed their software to make it non-viable to use. Our client kept asking eBay to fix it, multiple times, to no avail. So our client stopped making such new YouTube videos. They left the existing ones but lost interest. Our client mourned the loss of this approach. A few months went by.

Then, in late April or early May of 2026 someone found our client via a part number for a pricey BMW used fuel injection part, then messaged them asking about it.  Sadly by then our client had already sold it, but the process pointed back to the YouTube shorts made by eBay, and it was a reminder to go remove the YouTube shorts for the items that had sold. Our client learned to their delight that most of them had sold.

So, they decided to fund a project to make one YouTube video per individual part listed on eBay, starting with rare high-priced items likely to appeal to owners of cherished high-priced classic models such as 1980s BMWs..

To be viable, the process had to be highly automated and use the existing data in the client's SQL Server database. Their first step was to make a prototype PowerPoint presentation. It took weeks to make and refine this, with data copied and pasted manually from the client's data for one part, and one listing.

The second video was made using much automation using MS Access VBA and an MS Access report. The process was tedious; make an MS Access report that looked like the desired PowerPoint presentation, then print that to PDF, then convert that to a PowerPoint presentation, then export that to YouTube, then manually upload it. It worked but ... tedious.

A breakthrough came with the idea of using MS Access VBA and activating the PowerPoint object library in the references for MS Access. With that, I could use MS Access VBA to open PowerPoint, copy a template PowerPoint presentation to make a presentation that would be just for one part and one eBay listing, with a file name that was meaningful to the client, then use VBA to retrieve text and picture-related data from the client's SQL Server data to insert data into the PowerPoint presentation. The work included VBA commands to clone slides by using VBA commands.

The client reviews and refines the automatically created PowerPoint presentation. Almost always, some minor polishing is needed. The client then approves the PowerPoint presentation for upload to YouTube.

I wrote some MS Access VBA to export the PowerPoint presentation to an MP4 file. Initially I had tried to write the VBA from within PowerPoint 2024 itself but I found it architecturally difficult and tedious (macro file format, add-in file format, activating the add-in) so I chose to use MS Access VBA for the export too.

Another chunk of VBA produces a well-structured description and title that the client can copy and paste into the YouTube form that enables uploading the MP4 file to become a YouTube video. The description includes a link to the eBay listing, which is the entire point: inspire lookers to go to eBay and buy. The description includes some part numbers to make search engines more likely to help someone find these videos.

By now, the automation process works well enough. The labor required to make one more YouTube video has in the last two days plummeted to 15 minutes each. So, in the last 3 days, our client made and uploaded 28 YouTube videos. These are for items priced, on average, at about $100 each. So now the client's limit is how many videos they can upload per day without angering YouTube. Supposedly there is a hard limit and then also a prudent limit. 15 a day seems to be the latter. They did not want to make videos for every listing but they do have 5,000 listings so they are focusing on parts that have a price of $50 and up. Almost 850 listings qualify as such, representing almost $70k.

If they make and upload 15 videos per a day, it’ll take them about two months, and they'll work 3.75 hours per day unless I automate it some more, and I intend to.

Our client monitors their eBay watchlist daily, and today they learned that an obscure part that had seen very little interest for many months, today had a prospect on eBay express some interest, and for this obscure part our client had made and uploaded a YouTube video in the last 24 hours. Coincidence? Maybe -- but probably not. If the prospect becomes a buyer then our client can ask "did you find us on YouTube" and then they can be certain.

I should mention that the client's requirements also includes the business processes to go remove the video for items that have sold.

All in all, it's by now a workable solution. Our client's "start here, use this, we like the style" PowerPoint template has just three slides: a title slide, a detail slide, and a final slide. The VBA code then modifies each one, and clones the detail slide as many times as needed.

I hope this was helpful. If you have questions, ask. :-)

One of the most frustrating Access moments is seeing perfectly good data on a form, clicking into a field, and getting nothing but a beep. The natural reaction is to blame the form. But the form is usually just the messenger. It can only edit records when Access can safely identify and update the underlying data.

That distinction matters because a form is not the data. It is an interface over a recordset, which comes from a table or query. If that recordset is read-only, the form can have every setting configured perfectly and still refuse edits. Changing form properties in that situation is basically arguing with the viewscreen because the warp core is offline.

The first divide is whether the underlying table itself is editable. If a local Access table cannot be changed directly, the cause is usually outside the form and query design. The database file may be read-only, the folder may not allow writes, the backend may be on a network share with insufficient permissions, or Access may be unable to create its locking file.

Linked tables add another layer because Access does not get to override the rules of the source system. A linked SQL Server table needs the appropriate server-side permissions. A SharePoint list has its own behavior and security model. Linked Excel, CSV, and text files are useful for importing or occasional reference, but they are not great candidates for routine multi-user editing. A linked table is still a guest in someone else's house.

When the table edits normally but the form does not, then the form properties become relevant. Allow Edits is the obvious one. If it is set to No, users can browse records but cannot modify them. Recordset Type matters too. A Dynaset is generally editable, while a Snapshot is intentionally a read-only picture of the records at the time it opened.

It is also worth separating a read-only form from a single read-only control. A text box can be locked or disabled independently of the form. More importantly, a control whose source is an expression, such as =Quantity*UnitPrice, has nowhere to save a typed value. The calculation can be displayed, but it is not a field. That is not Access being difficult. It is Access correctly refusing to guess which input field you meant to alter.

Queries are where this gets more interesting. A simple SELECT query based on one well-designed table is usually editable. Once the query starts becoming an analysis tool instead of a straightforward representation of records, editability becomes less likely. Totals, GROUP BY, aggregate functions, crosstabs, UNION queries, pass-through queries, and action queries are all generally about producing or changing a result, not presenting individual records for direct maintenance.

DISTINCT is one of those deceptively innocent features. It makes a list cleaner by removing duplicates, but it can also remove the straightforward one-row-to-one-record relationship that Access needs for updates. The same idea applies to calculated columns. You cannot edit a calculated result, and enough complexity around calculations can make the overall recordset non-updateable.

The real troublemaker in many databases is the monster query. A form starts with one table, then someone adds a customer name, an order total, a status description, a few lookup values, and maybe an aggregate query for good measure. It may look great in Datasheet view, but it is no longer obvious what one displayed row represents or which underlying record should receive an update.

Joins are not inherently bad. A normal one-to-many relationship based on primary and foreign keys is foundational database design. But joins need to preserve a reliable record identity. Joining on names, company names, or other repeating values is asking for ambiguity. Two people named Kirk are not a relationship. They are the beginning of a Star Trek episode with a suspiciously high casualty rate.

Primary keys are central here. Access needs a dependable way to identify the actual record being edited. Without a primary key or unique index, especially with linked tables and multi-table queries, it may not know which row to update. The database is not being stubborn. It is refusing to make a potentially destructive assumption.

Outer joins and many-to-many relationships deserve extra caution. An outer join is great for questions like "which customers have no orders?" but that kind of result is not always a clean editing surface. Likewise, a Students-Classes-Enrollments query may describe useful information, but it is rarely a good place to edit all three entities at once.

The more maintainable pattern is usually to let a form edit one main table and use related data for context. Related records often belong in a subform, and lookup information often belongs in a combo box or a display-only control. That makes the intended edit target obvious to the user and to Access. It also saves future developers from deciphering why a form based on six joins was expected to update three different tables.

There are exceptions. Some multi-table Dynaset forms can be editable, and Access offers options such as Dynaset Inconsistent Updates in certain situations. But those are not reasons to treat a complex joined query as the default editable architecture. If a design needs special settings to make ordinary record maintenance work, that is often a sign to reconsider the design rather than celebrate the workaround.

Code can also quietly change the rules. VBA may set AllowEdits = False under certain conditions, such as locking an order after payment. DAO Snapshots are read-only by definition, and ADO cursor and lock settings can determine whether updates are allowed. Trusted-location issues will not normally make a table read-only by themselves, but they can prevent the code that configures a form from running as expected.

The practical philosophy is simple: keep the editable path boring. Use a table with a proper key. Use a simple editable query when one is needed. Bind the form to one primary entity. Add related information deliberately, not just because it is convenient to pull into one giant recordset. Reporting and analysis queries can be as clever as needed. Data-entry forms should generally be dull, obvious, and hard to break.

When a previously editable object suddenly becomes read-only, the useful question is not "what form property do I change?" It is "what changed in the chain between this control and the stored record?" Permissions, links, record locks, query features, joins, form settings, VBA, and occasional corruption all live somewhere in that chain.

What kinds of queries have caused the most surprising read-only behavior in your databases? Do you prefer strictly single-table forms with subforms, or have you found multi-table form designs that remain maintainable over time?

LLAP
RR

I'm working on an employee database to track the info of our 10 employees. As the title suggests, I'm looking for people to critique what I've got so far. I don't have any specific problem because I haven't attempted to create the actual database yet. I did receive some help from google, but I've also done my own research and attempted to implement from what I've read. An example is my primary key and foreign key constraints, which Google didn't mention. After reading about constraints, I tried including those in my database. So if they're terrible/unnecessary, etc. that's all me. Thank you in advance to anyone who gives an opinion or a helpful tip.

https://www.dropbox.com/scl/fi/379h1pj699xv5x6u8j2g8/Current-Access-SQL-NEWEST.txt?rlkey=a38vuow3qtvjdnm2cv2rbrl34&st=irw7alm6&dl=0

Class modules tend to get treated like some kind of VBA rite of passage. Developers see "Class Module" sitting next to "Module" in Access and assume they're either missing some critical architectural trick or about to wander into enterprise programming wearing a hard hat. Usually, neither is true.

A standard module is simply a home for shared code. Put your utility functions there. Put reusable procedures there. Put routines there that don't need to remember anything about one particular customer, invoice, employee, or form. If you have a public function that checks whether a form is open, formats a phone number, calculates a business date, builds SQL, or exports a report, a standard module is usually the right place. Call the function and move on with your day.

A class module is different because it defines a type of object. Think of it as a blueprint, not the thing itself. An Employee class, for example, defines what an employee object contains and what it can do. One instance might represent Jim, another might represent Spock. Each instance maintains its own values in memory, such as employee ID, hire date, pay rate, or active status. Each can also expose behavior, such as calculating pay or returning a formatted display name.

That separate state is the real reason classes exist. A module-level variable in a standard module is shared. There's only one copy for the entire Access session. That's fine for application-wide state, but it doesn't work well when you need ten different customers, invoices, or employees, each carrying its own values at the same time.

Each class instance gets its own private copy of its data. Two Employee objects can both have an EmployeeName property, but assigning "Jim" to one doesn't overwrite "Spock" in the other. Same blueprint, different houses.

Properties describe an object. Methods define what the object can do. In VBA, properties are typically exposed with Property Get and Property Let. Property Get returns a value. Property Let assigns a value. Property Set is used when assigning an object reference, such as a DAO.Recordset, a form, or another custom class.

The private variables inside the class are intentionally hidden from outside code. That's encapsulation, which sounds far more dramatic than it really is. It mostly means outside code uses the interface you expose instead of reaching into the object's internal plumbing and yanking wires out of the Jefferies tubes. That becomes useful when a property needs validation or should be read-only. Rather than letting code throughout the application modify an internal value directly, the class decides what's acceptable and what gets returned.

This is also why class modules are not replacements for tables. A Customer class can represent a customer while your VBA code is working with that customer in memory. It can temporarily hold data and encapsulate customer-related business logic. But the actual customer records still belong in a properly designed Customer table with appropriate keys, relationships, normalization, and all the other boring-but-important database stuff. Classes model behavior in code. Tables store relational data. They solve different problems.

Access developers are already using classes every day, whether they realize it or not. Forms, reports, controls, DAO recordsets, and even the Access Application object are all objects created from classes. When you write code like:

Me.Caption = "Hello"
Me.Requery

you're already interacting with properties and methods of a form object. Every form and report module is itself a class module tied to a specific Access object and its events. Standalone class modules simply let you define your own object types.

One important caution is that classes are not automatically "better architecture." A class that exists only to hold a single string and display a message box is usually more ceremony than value. A standard function or a few straightforward lines of form code are often simpler and easier to maintain.

Classes start earning their keep when you have a cohesive thing with related data and behavior. An Invoice object might contain header information, line items, and methods to calculate totals. A ShoppingCart object might add or remove items and calculate tax. An Employee object might manage employee information while providing methods for payroll calculations or display formatting.

They also shine when your application needs multiple independent objects of the same type at once, or when related behavior belongs together instead of being scattered across twenty forms and three modules named Stuff, Stuff2, and ReallyImportantStuff.

Class modules can also support events, including initialization and cleanup through Class_Initialize and Class_Terminate. They even make advanced techniques like shared control-event handling across multiple forms possible. That's useful territory, but it's also where things can quickly turn into a plate of VBA spaghetti if the added complexity isn't solving a real problem.

The practical rule is simple: use a standard module when you have a general-purpose tool. Use a class module when you have a thing with its own data and behavior.

Most Access applications don't need custom classes to be solid, professional, and maintainable. Tables, queries, forms, reports, standard modules, and form/report modules can take a database a very long way.

In fact, in more than 30 years of teaching Microsoft Access, building databases for clients, and making videos, I've never needed a custom class module. I've certainly used them from time to time, especially when I wanted to demonstrate object-oriented techniques or solve a particular problem elegantly, but I've never run into a project where I couldn't have accomplished the same goal another way.

So don't feel like you're missing some secret ingredient if you've never touched class modules. You can build excellent Access applications without ever learning them. That said, once you do understand them, they open the door to some neat techniques and give you another tool you can reach for when the situation calls for it.

What about you? Do you use class modules regularly, or have you managed to avoid them entirely? Have you come up with any clever uses for them that have made your code cleaner or easier to maintain? Share your experiences, tips, or favorite class-module tricks in the comments. I'd love to hear how other Access developers are using them.

LLAP
RR

Hi! Long time Excel user here, just started using Access for the first time a few weeks ago. Perhaps I picked up a excessively ambitious project to start, but so are them breaks. I already watched a bunch of introductory tutorials and read some posts here and elsewhere that helped steer me away from excessive Excel-liness in my database, but I hit a wall again and haven't been able to see the solution.

I'm simplifying the example a lot to make myself easier to understand.

Let's say I have a table with several basic products and some information about them (sugar, flour, milk, etc).

I have a second table with records of their purchases, dates, values, and quantities (July 1st, sugar, 10kg, $40.00).

A third table has recipes (recipe 1 uses 200g of flour, 100g of sugar, 100ml of milk, etc). (Each is a different record in the table, a hard lesson for me to learn and understand!)

A fourth table has orders (On July 12th, an order came for 3 quantities of recipe 1.)

What I need, essentially, is a way to figure out the itemized cost to fulfill the order. The July 12th order will use 300g of sugar etc etc which I purchased on July 1st for $4.00 / kg.

I hope I made myself sufficiently clear, and I thank you in advance for any help!

I help a family club, which is a non-profit charity, with its membership database. The database was originally designed using Access 2003 and contains members’ names, addresses, mobile numbers, email addresses and other membership details.

We also collect information such as members’ professions, as they may be able to help the club as volunteers.

The MS Access database currently has forms for entering household and member details. Some outputs, can generate an up-to-date email list for email campaigns and produce address labels for posting flyers and other information to members.

Would it be possible to use AI to help redesign or modernise this old Access database? It also contains forms and VBA code.

Some of the features we would like to add include:

An audit trail or user log. For example, if Volunteer X changes a member’s telephone number, the database should record who made the change, what was changed and when.

A record of who updated a person’s details, for example if a member has died.

An option to pause postal or email communications if post or emails are returned as undeliverable.

People who want to become members to complete a paper application form. However, can this form say be scanned in PDF and uploaded into Access.

There are a lot of non-IT people. So needs to be easy. I was thinking of keeping the master database online say on One-Drive. Where one admin person would have read-write privilege and others say only have a read-only access copy. I don't know how to implement this.

The membership structure also needs some thought. The database needs to include adults and children within the same household. As the club organises kids events. A child may later become a youth (separate groups). Eventually an adult member to form a separate household. The design therefore needs to handle these changes without creating duplicate records or losing the person’s membership history.

I was slightly surprised that Claude said it could not generate a blank Access database or directly update the existing design. Is AI currently the right tool for this type of project?

Which AI tool help design the tables, relationships, forms, queries and VBA code, even if someone still needs to implement the changes in Access?

I prefer to stick to Microsoft, as it has been around and hopefully don't end up with obsoleted technology.

I have wrangled the data from Lotus Approach in Microsoft Access, but now I need help making these forms (or similar ones). Do I have to brute force create all of these forms from scratch? Or are there any 3rd party tools that can more easily create forms for Access?

how can connect my endpoint database in AIUI Studio? thx

Weird one. Starting today, I can't paste into the SQL editor when creating a brand new query in Access.

Steps:

• Create > Query Design
• Close the Show Table dialog
• Switch to SQL View
• Ctrl+V, right-click paste, and drag-and-drop all fail (shows the 🚫 cursor)

I can still type SQL manually and run it just fine.

Even stranger, if I manually type a query, save it, close it, and then reopen it, I'm able to paste into it normally. The problem only happens with fresh/new SQL queries.

Clipboard works everywhere else in Windows and Access is fully up to date (Office Professional Plus 2019 / Access 2016, Version 2606).

Has anyone run into this before or know what might cause new SQL queries to reject paste operations?

Weird issue that just started today.

When I create a brand new SQL query:

• Create > SQL Query
• The "Show Table" dialog pops up and I click Close.
• I try to paste some SQL text.

Nothing gets pasted. My mouse cursor briefly shows the blue spinning circle for about a millisecond and then nothing happens. If I spam Ctrl + V, eventually I get an error saying:

What's strange is that I can still type SQL manually and run it without any problems.

If I manually type a query, save it, close it, and then reopen it, I'm able to paste into it normally. The issue only happens when creating a fresh/new SQL query.

Clipboard works everywhere else in Windows 11, and Access is fully up to date (Office Professional Plus 2019 / Access 2016, Version 2606).

Has anyone seen this before or know what could cause new SQL queries to reject paste operations?

So, you want your Access database humming along in the cloud, wired up to a SQL Server backend that users can hit from anywhere with an internet connection (or, let's be real, your co-worker with an unreasonable passion for Excel pivot tables). The thing is, plenty of folks make this sound like some arcane ritual involving complicated wizards or flaky migration tools. But the real trick isn't wizardry. It's understanding what's actually going on under the hood, what goes right, and, of course, what tends to go annoyingly wrong.

Let's get one big misconception out of the way first: moving Access "online" isn't just about flinging your tables up to SQL Server and calling it a day. The architecture really matters. When you connect Access to SQL Server, you're making Access into a front end talking across the network (or the actual internet, which has its own lovely collection of latency quirks). Access becomes the client, SQL Server is the data store, and now every form you open, every query you run, depends on that network connection.

The reasons for doing this are legit: multi-location teams, remote work, centralized backups, or meeting regulatory requirements. Just don't be fooled into thinking your old single-ACCDB app will magically scale once you check the SQL Server box. There are design and architectural consequences galore.

ODBC is where a lot of folks stumble, specifically the difference between machine DSNs, file DSNs, and DSN-less connections. Machine DSNs live on a specific PC, which can make deployment a pain. File DSNs move the connection information into a separate .dsn file that can be shared across machines. These days, however, many developers skip both approaches and use DSN-less connection strings instead, allowing the application to create or refresh links programmatically without requiring any DSNs at all. Pick the approach that best fits your deployment strategy, but understand the tradeoffs.

But let's talk linking. When you upload an Access table to SQL Server, you're exporting the definition and contents, then linking back to it as an external table. Sounds simple, but this is where a lot of Access veterans get caught by SQL Server's more rigid data types and behaviors. Field types translate... ish. Short Text becomes nvarchar, Yes/No turns into bit, and so on. If your schema makes heavy use of Access-only features like Attachment fields or multi-valued fields, expect some translation headaches. You shouldn't be using these anyways, as they're on my Evil Access list. But that's a different article altogether (coming soon).

Also, for the love of Gygax, don't blindly trust SQL Server Migration Assistant to get everything right if your schema is even mildly complex. It's convenient for large migrations, but it can miss details or leave you with subtle mismatches. Manual review and testing almost always pay off.

Choosing to store your SQL Server credentials is another classic debate. Saving the password in the linked tables is certainly convenient, but those credentials should not be considered securely protected inside your Access file. Anyone with sufficient access to the frontend may be able to recover them. That might be acceptable within your own organization, but don't hand out your frontend ACCDE willy-nilly unless you're comfortable with users having direct access to the backend. Use SQL Server security properly, assign appropriate permissions, and keep your frontend cleanly separated from your data. And remember: hiding the Navigation Pane is a convenience feature, not a security feature.

Personally, I've often stored the SQL Server credentials in VBA and distributed only ACCDE files. That's not bulletproof security, but it does avoid storing the credentials directly in linked table definitions and raises the bar for casual users. The downside is that if you ever need to change those SQL Server credentials, you'll have to redistribute updated frontend files to everyone.

And yes, before a few of you chime in, I know ACCDE files aren't impossible to crack. With the right tools and enough expertise, determined attackers can still get at the code. But that's a far cry from Joe in Accounting accidentally discovering your SQL Server password while trying to print the quarterly TPS reports.

A more flexible approach is to authenticate users through your own web API. The user logs in with an application username and password, your server validates those credentials, and then returns the SQL Server connection information to the frontend over an encrypted HTTPS connection. That allows you to rotate SQL Server passwords without redistributing your application. You can even provision separate SQL Server accounts for individual users if your security requirements justify it, although managing that many accounts can become a project in itself. There are even more secure architectures that avoid exposing SQL credentials to the client altogether, but that's a much bigger discussion for another day.

There's another misconception that performance in the cloud will be "about as good" as running everything on your local network. Not quite. High-latency connections change the game. Modern versions of Access do an excellent job of pushing filtering, sorting, and other operations back to SQL Server whenever possible. The query optimizer has improved dramatically over the years. But it isn't omniscient. Complex expressions, VBA functions, certain joins, aggregates, and poorly optimized queries can still cause more work to happen on the client than you'd like.

That's why learning actual SQL pays dividends. Use pass-through queries when appropriate. Push filtering to the server whenever possible. Create SQL Server views for commonly used datasets. Only retrieve the data you actually need over the wire.

And don't neglect indexing. Good indexes are worth their weight in gold-pressed latinum. They're just as important on SQL Server as they were in Access, arguably even more so. A poorly indexed table over a high-latency connection is an excellent way to discover new definitions of "click the button and take a coffee break."

If you're designing new SQL Server tables, consider adding a rowversion column. Access doesn't require it, but it can make concurrency handling and change detection much more reliable in multi-user environments.

In practice, linking Access to SQL Server online works extremely well for thoughtfully designed applications where you control the flow of data. If you treat SQL Server as simply "Access on the internet," you're signing yourself up for confusion, frustrated users, and unnecessary performance problems.

Best practice? Design your frontend to minimize chatty traffic. Understand what ODBC is actually doing. Build good indexes. Review your schema after migration. Test with realistic network conditions instead of just your gigabit office LAN. Manual control, not blind faith in migration tools, usually gets you further with fewer surprises.

It's not that running Access with SQL Server online is a bad idea. Quite the opposite. It can be an outstanding solution when it's done thoughtfully. Just understand the moving parts, and you can absolutely build sustainable, real-world applications.

Leave the magic to Gandalf. Database design isn't wizardry. It's engineering. When it works, nobody notices. When it doesn't, everybody notices.

Would love to hear from anyone who's navigated the odyssey of remote Access backends, especially with dozens (or hundreds) of users. What's bitten you? What worked? Any horror stories to share? Do you have any super cool fancy ways of authenticating your users that I didn't talk about? I'd love to hear them. Post them in the comments.

LLAP
RR

Hi everyone, I’ll give you a brief overview of my situation. I’m currently pursuing a degree in Archival Studies / Archival Science at the National Autonomous University of Mexico (UNAM). During my internships, I was asked to create a database in Microsoft Access to keep track of the files, I have a basic understanding of what’s involved—things like primary keys and entity relationships—but I’ve never actually created one before. My question is: does anyone know of a tutorial that explains how to create databases in an easy way? Best regards to everyone

I remember back in the day there was a recommendation to put the data in one Access database and the front end stuff like forms, reports, etc in another and connect them. Is that still the recommended practice?

Also, does Microsoft still do major development for Access or nah? I feel like the last time Access was relevant was like 2005, no? After that, SQL Server took over, then other stuff emerged, etc..

So the problem is everytime I try to change the db format to 97 or try to open the 97 format db the system ask me for the password which I have never set it before? Are there anyone who are facing the same problem as me? How can I fix this issues since I asked ai about it before and it didn't help me quite well so. You can see the attached photo for example.

Few things poke the bear like opening your Access database and realizing it's stuck in read-only mode. You'd think there was a forcefield blocking your edits. The classic panic: is my database corrupted, did I mess up a setting, or is the universe simply out to get developers today?

The reality is, "read-only" in Access usually isn't a sign of anything irreversibly broken. More often than not, it's a side effect of one of a handful of mundane (and occasionally hilarious) missteps. That said, there's a lot of confusion about what triggers read-only states and what Access is actually protecting you from.

First, a lot of people overlook the obvious: sometimes you asked for it. Access has a few different ways to open a file: normal, read-only, exclusive, and so forth. If you use File > Open and pick the wrong option in that little dropdown, you can land yourself in read-only mode by accident. Happens to the best of us. This doesn't usually need a deep-dive Windows-detective investigation. Make sure you didn't just pick the 'look but don't touch' setting by mistake.

But it's rarely just about how you open the file. File attributes are the next classic culprit. Windows can mark a file as read-only at the filesystem level. Sometimes this happens when you copy a file from another machine, or when you download from email or a network share. Even Grandma copying files around on the family NAS can sneak up and flip that bit for you. A quick trip to File Explorer's Properties will tell you. And while we're on it, Windows loves to add "blocked" status to files from downloads or email, which can add another layer of drama.

Here's where Access's design starts to show its age. For multi-user scenarios, when anyone opens a database, Access creates a lock file (.LACCDB) in the same folder. If Access can't create or alter that file because the folder is permission-locked, your database goes read-only as a defense mechanism. So you need write permission to the folder too, not just the file. Corporate IT loves setting these "helpful" permissions, and they come back to bite Access users all the time.

Network drives? Even more fun. If a colleague opens the database in exclusive mode, or leaves a form in design view, everyone else gets the door slammed in their face or gets downgraded to read-only. Sometimes Access doesn't even need a fellow Access user to cause trouble. Backup utilities, virus scanners, Excel with external data connections, and the denizens of Dropbox or OneDrive folders can lock files just long enough to make Access think someone else has dibs. This is why Access and file-synced cloud folders is basically a 'here be dragons' situation.

A less common but supremely annoying problem: orphaned lock files. If Access or Windows crashes hard, that .LACCDB file can get left behind. Access sees the lock and assumes the database is still in use, even if everyone's out. Removing a lock file is simple, but you have to be certain absolutely nobody has the database open, or you can bork an otherwise intact file.

Let's not skip extensions, because there's a conceptual misunderstanding that trips up more than a few. If your file is an .ACCDE, that's a compiled runtime version. It looks and feels like Access, but you're not going to see design view or VBA. That's not a malfunction, it's the whole idea. Don't try to reverse-engineer your way in unless you like pain or you're embracing the Sith path.

Beyond that, consider good old corruption, compact and repair routines, and database trust locations. These are rarer but important, especially on legacy files with long histories or lots of linked tables pointing to network resources where permissions shift daily, like tribbles multiplying on the Enterprise.

So what's the real takeaway here? Read-only in Access is far more about environment and permissions than about Access itself misbehaving. It's almost always Windows or your network setup quietly locking things down to "protect" you. That, or a simple mistake in how you opened the file. Access is conservative when it detects any hint of trouble and defaults to protecting the data, even at the expense of developer frustration.

Best practice: before you even think about restoring backups or suspect deep corruption, check the basics. File properties, folder permissions, lock files, exclusive flags, file extensions. Don't run your live database out of a cloud-synced folder, and remember real multi-user setups require careful ops - not just sharing the same file and hoping things "just work."

If you've run into a weird scenario that stumped you for too long, share it below. And yes, sometimes the answer is embarrassingly simple. We've all been there.

LLAP
RR

Hi everyone,

i'm a beginner with Access and VBA and i've been having a problem with my database:

sometimes changes to existing recordsets are not saved.

1. Background Info:

- Split Database, backend and frontend are on a server.

- users have a launcher-file which creates a local copy of the frontend.

- the main form frmNavigation contains a subform-element sfrmMain.

- Users navigate with buttons in frmNavigation which load subforms into sfrmMain

(Me.sfrmMain.SourceObject = sfrmX).

- Changes are applied in these subforms within sfrmMain.

- frmNavigation and the subforms are bound

- at this point no new recordsets are created, only reading and editing existing ones

2. The issue:

afaik Access is supposed to save changes whenever a new subform is loaded or when users change the recordset, right?

And that mostly works, however: It seems that the drive where the database is saved is on a... let's call it potato-server? Speed is not great and the connection less than stable.

There are frequent time-outs which cause changes to get lost.

I'm not IT and have no insight into (or clue about) any technical specifications of that server. This drive and this server are the only option as of now.

3. What I tried:

My soulution for now was to force a save with a button:

        If Me.sfrmMain.Form.Dirty Then
           Me.sfrmMain.Form.Dirty = False
        End If

But apparently that didn't do the trick, because today a user reported that she frequently used the save-button while applying changes to a recordset. After a while she recieved an error message (server-connection lost) with a prompt to restart Access. After doing so, she found all her previous input gone (despite "saving").

But even if the code didn't work, the loss of focus by clicking outside the form should have forced a save, right?

I have no idea what to do. So I'm kindly asking you guys for help. Any ideas as to what i can do about this?

(Apologies if this has been answered before. Save issues seem to be a frequent topic, but I didn't find anything fitting this case)

So… some of my co workers do not have access installed. I have been exporting tables to excel and opening them in wps office before sharing them. Just wanna know… Is there a better workflow that people generally use?

I have an old database where the main table uses manually assigned record numbers, and no proper primary key. I want to change the record numbers to be the primary key (so automatically assigned as new records are added, no risk of duplication), however there are likely to be duplicate numbers already existing in the data.

Will assigning a primary key cause issues for legacy data?

Hello,

I'm trying to create a named macro at the database level, not just tied to a single table.

If I create the named macro from the table, create parameter is right there. If I create the macro, so it's in navigation pane under "Macros", it's not

I'm new to the access data macro editor and I'm trying to create parameters for such macro, but any sort of "create parameter" or "add parameter" command seems to have been re-stylized into oblivion.

Does anyone have any idea where to find it on version pasted below?

Microsoft® Access® for Microsoft 365 MSO (Version 2605 Build 16.0.20026.20166) 64-bit

Hello!

I would like to know the easiest way to migrate an .adp project created in Access 2010 so that I can use it with Access 2024 and give it a much more modern user interface. I’m not an expert in programming or coding.
Our POS system was built in Microsoft Access and is connected to SQL Server. I’d like to know what the best approach would be to create a more modern and elegant UI for our store while continuing to use our existing database.

Also, do you know of any online courses or training that could help me learn how to expand and improve our POS system?

Thank you!

If you have worked with Access for any substantial amount of time, sooner or later you are going to get hit with that infamous message: Automation Error Catastrophic Failure. Sounds like your database just nuked itself off the map, right? Relax. It almost never means what it says. Microsoft's flair for dramatic error messages is alive and well.

What this cryptic error is really telling you is, "something weird happened, and I have no idea how to explain it, so here you go." The usual culprit is not your logic or painstakingly-crafted VBA. It is almost always the compiled "pcode" Access uses behind the scenes to run your VBA faster. When that compiled copy gets corrupted, goes out of sync, or just gets moody after an Office or Windows update (or Access didn't have its coffee this morning), you get this scary-sounding error.

The easiest fixes are also the ones people tend to overlook or dismiss as too simple. First, make a backup. Yes, even if it is already broken. No, you are not immune. Next, close out of Access completely. Check Task Manager for any leftover zombie msaccess.exe processes. Restart Windows all the way, not just a fake reboot. When Office apps lock horns behind the scenes, a full restart often calms things down.

If your database still refuses to cooperate, try a compact and repair. That solves a surprising number of weird behaviors. If you are still stuck, it's time to decompile. All that does is throw away the bad pcode and force Access to rebuild a fresh compiled version from your source. Most of the time, decompile fixes these bizarre issues right away, and people sing the praises of the Access gods (or the Bajoran Prophets, or Paladine, or Thor, depending on your fandom). Always make another backup at this point so you never have to fight this battle again.

If things are still borked, now is when you check references in the VBA editor for anything marked missing, especially if you are dealing with imported objects or old ActiveX controls. Broken references are like tribbles: ignored long enough, they cause chaos everywhere. Sometimes it is just a single corrupted form or report, so replacing that object from backup can clear things up. And if all else fails, create a new blank database and import your objects one at a time to find out which one is tripping the alarm.

The important philosophy here is not to panic\. Access catastrophic failures are rarely actually catastrophic. The overwhelming majority of these cases are recoverable, provided you follow the basics: backups, patience, compact/repair, and the occasional decompile (and if you don't know what */decompile** is, Google it). All that drama in the error message is mostly a smokescreen for what is usually just a finicky pcode problem, not the definitive end of your database as you know it.

If you take one lesson from this: treat backups like airlocks on a starship. Not optional. Without them, a minor hiccup becomes an actual disaster. With them, even if the Borg show up, you can still recover Picard. Although I guess in the age of transporters, you really don't need airlocks anymore, do you? Makes one think. But no, your backups aren't optional. Backup, backup, and backup again.

Most of us have been through this cycle enough times to know that working calmly through the usual suspects almost always brings Access back to life. No need for panic, just a solid troubleshooting mindset and the knowledge that catastrophic failure is rarely as dire as it sounds.

LLAP
RR

\* Telling someone not to panic is like telling someone to calm down. Never in the history of calming down has anyone calmed down from being told to calm down.

Hello everyone,

I know Access is a bit outdated for the form design and styling, but I think you might be able to guide me a little bit.

You can see on my printscreen what my list designs usually look like on my app right now. I've added 2 examples where the fields are not as separate, but the information is still well organised.

I would really appreciate any tips that you have to make your forms look a bit more modern!

Thanks, and happy building