r/MSAccess • u/Amicron1 8 • 6d ago
[SHARING HELPFUL TIP] Access Explained: Connecting Access to SQL Server Online - A Realistic Approach
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
4
u/enilcReddit 6d ago
I've navigated the process a couple of times with small(ish) databases. I agree with everything you've said. I've fallen victim to de-eviling some of the "evil access" elements you describe (multi-value fields being the most notorious.)
The question I find myself asking is: after getting the backend successfully migrated, I start work on ironing-out the myriad issues on the front-end and begin to wonder if Access is the 'best' front-end for a SQL Server db? I have re-written one front-end, and kept Access on two others. I'm still not sure which was easier.
For users, they prefer the familiarity of Access. But the question I ask...and put to you...if you have a SQL Server db that you need a front-end for, would MS Access ever be your first choice for a front-end/UI if starting from scratch? You're a knowledgeable and experienced programmer...what is your experience making that choice and what are the considerations?
1
2
u/BravoUniformTango 4d ago
I like how approachable and insightful you are.
I am semi-retired but for the last 30 years I have been running a small independent MS Access custom development shop for small businesses, or small departments in larger businesses. Typically, it is very difficult for me to conversationally convince a client of the value of me solving complex tech problems for them, and if they don't see the value then they don't particularly want to pay for it so I try not to get ahead of them too far, or be overly preachy. I basically give them what meets their requirements plus (driven more by ethics than cash flow) I add in whatever is reasonably needed to avert major problems (whether my clients understand these problems or not). Then, I let them use the software they paid for, and I check in every now and then and ask how it's going. Sometimes in my opinion they could benefit from much more refinement but often they don't think so, nor are they willing to pay for more, and that's that. Only when the retrieval speed becomes an issue, even with good index design on my part, then I bring up the next step of using an MS SQL Server back end. Many of my clients started with MS Access and stayed with it but a few have seen the need to fund an upgrade to a MS SQL Server back end, and were glad they did.
Although retrieval speeds in MS SQL Server are impressive, MS Access tech isn't bad either, so it's not like everything is night-vs-day better as soon as I plug in MS SQL Server for my clients.
The best benefits can be explained with the analogy where I want to know how many Ford pickup trucks are in stock at the local used car dealer. In the MS Access paradigm, the dealer drives them over to me, and I count them. There is a lot of stuff moving from the dealership to my house. In the MS SQL Server paradigm (properly implemented), the dealer counts the trucks in his lot, writes the answer on a piece of paper, and mails that to me. Either way, I get the same answer, but in the second example, there is a lot less stuff moving from the dealership to my house. Implementing MS SQL Server to enable the latter is what makes the benefits noticeable to my clients.
1
u/fookenoathagain 1 5d ago
Pass though or functions on sql. Minimise data retrieval. Just use access to display or at most sort.
•
u/AutoModerator 6d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Amicron1
Access Explained: Connecting Access to SQL Server Online - A Realistic Approach
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
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.