Security Flaw 2
By Richard Rost
2 years ago
Fixing Security Flaw in Linked Tables in MS Access Part 2
In this Microsoft Access tutorial, I will show you how to identify a significant security flaw involving linked tables and how to fix it using unbound forms and connection strings. This video covers setting up a connection string, opening record sets programmatically, and minimizing security risks in your Access databases. This is part 2.
Members
In the extended cut, we will learn how to handle continuous forms and open arguments. I will show you how to adapt the technique to work with continuous forms and how to use open arguments to open forms for specific records.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Recommended Template
- I also have a much more comprehensive Access Unbound Forms Template which has many more features including simple procedures you can use for your forms, it handles Subforms, Combo & List Boxes, Reports & Temp Tables, and it includes a complete explainer video, template, and source code
Keywords
TechHelp Access, security flaw, linked tables, split database, securing data, database password, front end, back end, connection string, unbound forms, VBA code, record set, ACCDE file, form load, form unload, record source, SQL Server backend, database security, user and group security, system log, workflow setup
Intro In this video, we address a major security flaw in Microsoft Access involving linked tables and explain how to secure your data even if your back-end database is password protected. I'll show you how to set up a global connection string in VBA, connect your forms directly to the back end without using linked tables, declare form-level objects for managing the connection, and properly bind and release record sets when opening and closing forms. We will also cover best practices for managing these connections and briefly discuss tweaks for using different back end types. This is part 2.Transcript Today is part two of my security flaw video series, where we discuss a glaring security problem with Microsoft Access when it comes to linked tables. Even if your back end has a password, if your front end doesn't, it's easy to get at that data. So today we're going to show you how to fix that.
All right, so here I have my split database with the password; the back end has a password on it. What we're going to do is, we are going to, let me get rid of this one here, let's go into the front end file. I am once again going to delete all of these objects out of here. So now we no longer have any references to them. If I try to open up a form now, it's going to say it can't do it because it can't find that table.
What we have to do is we have to say, okay, when I go to open up the customer form, when the form loads, I'm going to connect to that database, specify the connection string with the password, make that connection, and then when the form is closed, it will just clear that connection. There's no linked table involved. It's a record set in the form itself. OK?
First thing, we have to set up our connection string. I'm going to put this in a global module. I'm going to put it right about here. I'm going to declare it as a global constant. All right, global constant. Where are you? There you are. Global, that means the whole database can use it. It's a constant, so it doesn't change. Yeah, I got a whole separate video on constants. You can go watch that if you want. So I'll put a link to it down below.
Here's the constant name. I want to say the variable name. The constant name is connect string, and here is my connection string. Let me close this. We don't need it right now. This is the connection string. It's got to be in this format. Okay, if you're connecting to your password, PWD, semicolon, database equals, and then the full path to your back-end file, just like that exactly as it appears. If you don't know what that is, you got to go find it. Here's my folder. It's right up here. There it is, right? And there's the F E dot A C C D B. That's my front end, and the B E is the back end.
OK, so that's the connection string that Access is going to use.
Now, is it a security concern that the password's there in plain text? Not really, because you're going to be distributing an ACCDE file, right, an encrypted execute-only file, that they won't be able to just easily find that password. I mean, yeah, could they go in with a sector editor and try to find it, yeah, okay, they could. You can hash this or encrypt this in other ways. You could use, you know, ASCII codes or, there's all kinds of ways you can hide this better, but this is going to keep 99.9% of everybody out from figuring out what your password is, especially if it's something weird. If you make it something like that, then it's going to be even harder to find. So there's that, save it.
Now we're going to go over to our customer form. This is the guy we're trying to open, design view. Right now, it's bound to a table that doesn't exist. So you're going to see all these little green little dot thingies there because this can't say, "I can't find this stuff." So go to the form properties, go to data, find customer T, and just delete it. This is now an unbound form. That means if I save this and close it and open it back up again, it's going to say I have no idea who you're talking about. So when this form opens, it's going to make a connection to the customer table and just store that in memory, don't actually make a table out of it.
So we're going to go back to the form properties, go to event, find the onload event, dot, dot, dot. That puts us right in here with the onload event. Okay. Now, the first thing we need is a record source object as well as a database connection object, and I'm going to put those as form level objects. So they're going to be outside of all the subs right here under your option explicit, right there, form level objects. We're going to declare a database and a record set, and these objects will exist at the form level. So, in other words, they will exist as long as this form is open. When the form loads, we're going to initialize them, and when the form unloads or closes, we're going to destroy them. But they're declared up here outside of all the subs.
Now, inside your form load, we're going to put three lines. And I'm just going to copy and paste them because you don't really want to see me type them to you. There they are. Set DB, that's our database object, equals open database. And this is the syntax that you're going to use with a connection string that we already specified. I put it in a global module because you can use it with multiple forms then. OK? These are just some options, if it's read-only, whatever, but this is what you need to do here. Ignore the name because the name is specified in the connection string. OK? That's going to return a database object.
Now, we're going to open a record set in that database, whatever table or query you want to open, put there. OK, yes, this can also be an SQL statement. All right, and we're gonna open it as a Dynaset. This is new; I didn't cover this in the in the record set TechHelp video because you don't need it if you're opening up a local table or even a linked table. But if you're using a connection string like this, you have to specify either a Dynaset, which is editable, or a snapshot, which is not, which we use for like reports and stuff. OK, once you've got that record set object, then we're gonna say set me dot record set, that means the record set for this form, which is me, set it equal to that record set that we just returned. Now the form will be pointing to this guy. OK, now that initializes all this stuff.
When the form unloads, we have to destroy all this stuff, free up that memory. So come over here, drop down load and find unload. This is the form unload event. And we're going to put in there this stuff. There, this is all easy stuff. On error resume next, because if you get any error messages while the form's open, these things might be destroyed without you thinking about it. So these might throw errors if that happens. It's rare, but it can happen. This just covers your butt. So close the record set, close the database, set it equal to nothing, set it equal to nothing, free up the memory, which should be freed up anyways when the form closes, but this just makes sure.
OK, all right, ready? Save it, debug, compile, everything should be good. Close it, close it, close it. Ready, open it, and there you go. Look at that. This form now opened; it made the connection to the back-end database using VBA code. It created a record set; it linked directly to it. You can come in here now. You can make modifications. This is an editable record set. You can go between the records. And then when you're done, you close it, and it frees up that memory. And there's no linked tables, so there's no security risk here. This works whether you're dealing with a linked back-end Microsoft Access file; it works with online SQL Server, local SQL Server. There are some minor tweaks that you have to do to get it to work with SQL Server. We'll talk about that in just a minute.
Now, there are some other issues with some more complicated forms and stuff that you might have to be aware of. And we will talk about that in the extended cut for the members. The first thing is continuous forms. If you got a continuous form, there's one other little thing you got to be aware of, and I'll show you how to do that. And also open arguments, like how we have the contacts. You want to open the contacts for a specific customer, you got to know how to use open arguments to open up this form for just that customer. And we'll talk about that in the extended cut as well.
And beyond that, I actually have put together a complete template that has lots of stuff that's not covered here. For example, I'll show you how to set up subforms, combo and list boxes, reports, which reports actually require a totally different technique using temporary tables. And I cover that in the explainer video for this template. It's all easy to call packaged procedures. With a full explainer video, it works with Access and SQL Server backends, and you can find more information about this template here at Unbound Forms.
And of course, I would be remiss if I didn't mention my security seminar. This covers locking down your front end as much as possible, and it includes setting up security inside your front end database. User and group security, workflow, who has access to what objects and stuff like that, setting up a system log, and lots and lots and lots more. So check that out as well.
But members, stick around for the extended cut. I'll show you how to use the technique that I just showed in continuous forms and open arguments. As a reminder, members, silver members and up get access to all of my Extended Cut videos, all of them, not just this one, all of them. And gold members can download these databases and you get access to the code vault. So that's really cool stuff. But that'll do it. That's going to be your TechHelp video for today. I hope you learned something about the security flaw and how to get around it with an unbound form. It's a little bit extra work, but in the long run, it really creates a cool database because now you can distribute it and you can specify the data source right in your code. You can even specify it by sending a user a value, like a custom password just for them, and say, "Here, type this in," and they can connect with your code that way. All kinds of stuff you can do with this, folks. But that's your TechHelp video for today. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
TOPICS:
Fixing security issues with linked tables in Access
Setting up a connection string in VBA
Declaring a global constant for the connection string
Connecting to a database in the form's OnLoad event
Using a record set in forms
Setting form level objects for database connections
Opening a database using a connection string
Opening a record set as Dynaset
Binding a form to a record set
Releasing database and record set objects in OnUnload event
Handling errors in the form's unload process
Making the form unbound from tables
Using VBA to connect to different back ends
Tweaks for connecting to SQL Server back ends
Additional tweaks for continuous forms
Using open arguments in forms
Setting up subforms, combo boxes, list boxes, and reports in a template
Handling reports with temporary tables in an unbound form setup
COMMERCIAL:
In today's video from Access Learning Zone, we tackle a major security issue with Microsoft Access linked tables. I'll show you how to connect your front end to a secured back-end database using VBA, bypassing the need for linked tables and improving your security. You'll learn how to set up a global connection string, bind it to your form, and manage database connections dynamically. We'll also touch on handling continuous forms and open arguments in our extended cut for members.
You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main security concern addressed in the video tutorial?
A. Inability to access data due to password protection
B. Front end database vulnerabilities even when the back end is secured with a password
C. Data corruption when linking tables
D. Difficulty in setting up user permissions
Q2. What should you do with the front end objects of the split database according to the tutorial?
A. Encrypt them with a password
B. Delete all existing linked tables
C. Create multiple backups
D. Share them directly with all users
Q3. Where should the connection string be set up in the Microsoft Access database?
A. Inside every form
B. In a global module
C. Within the back end database
D. In a local table
Q4. What is the purpose of using a record set in the form itself rather than a linked table?
A. To make the form load faster
B. To reduce database size
C. To improve security by avoiding linked tables
D. To simplify SQL queries
Q5. What type of file is suggested to distribute to users for better security?
A. MDB
B. ACCDE
C. ACCDB
D. Excel workbook
Q6. What object types are declared at the form level to manage the database connection?
A. Constant and variable
B. Table and query
C. Database and record set
D. Form and report
Q7. How should the database connection be handled when the form unloads?
A. The form should be minimized only
B. The connection should be kept alive for future use
C. The record set and database should be closed and set to nothing
D. The current record should be locked for security
Q8. What error handling method is recommended during the form unload event to manage potential errors?
A. Ignore all errors
B. Log the errors to a table
C. Resume next on error
D. Open a message box for errors
Q9. Which object property must be changed to convert the form from bound to unbound in the form properties?
A. Control source
B. Record source
C. Form name
D. Default view
Q10. What programming event is utilized to load the record set when the form is opened?
A. OnClick
B. OnCurrent
C. OnActivate
D. OnLoad
Q11. What is necessary to initialize a record set as editable using the connection string?
A. Dynaset type
B. Table type
C. Snapshot type
D. Linked table type
Q12. What additional feature is covered in the extended cut for dealing with continuous forms?
A. Caching data locally
B. Updating subforms dynamically
C. Handling open arguments
D. Using Access macros for automation
Q13. What benefit does the security seminar provide as mentioned in the video?
A. Step-by-step guide on basic form creation
B. Detailed overview of managing user permissions and workflow
C. Techniques for creating complex queries
D. Instructions on designing user interfaces
Q14. In the context of the video, with which back end database types does the discussed technique work?
A. Microsoft Excel and Google Sheets
B. Microsoft Access and SQL Server
C. Oracle and PostgreSQL
D. NoSQL and JSON files
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-D; 11-A; 12-C; 13-B; 14-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on addressing a significant security vulnerability found in Microsoft Access when working with linked tables. Specifically, if your Access back end is protected with a password but your front end is not, it is still quite easy for someone to access your data. In this session, I will walk you through how to secure your database by eliminating linked tables in favor of connecting directly to the back end with VBA.
To start, I have a sample split database set up. The back end is protected with a password. My goal is to configure the front end so that it does not expose any linked tables that could present a security risk. The first step is to remove existing linked tables from the front end. If you attempt to open a form at this point, Access will report that it cannot find the necessary table.
To resolve this, the key idea is to establish a connection to the back-end database in your form's load event using a connection string with the password embedded. The connection is made strictly in memory as a record set, avoiding linked tables entirely. When the form closes, the connection is properly cleaned up.
Let's discuss how to set this up. Begin by defining your connection string as a global constant inside a module. Declaring it as a global constant allows you to use this value throughout your database, ensuring consistency and easy updates if things change in the future. The connection string will need to follow a specific format, including your password, the keyword "PWD," and the full path to your back-end database file.
A common concern is that storing the password as plain text in your code introduces risk. In this scenario, you will distribute your solution as an ACCDE file, which is an encrypted, execute-only format. Most users will not be able to view or extract the password from this type of file. While determined attackers with special tools might be able to find it, this approach will prevent the vast majority of breaches. If you want additional protection, you can use encoding techniques to further conceal your password, but even as written, you will deter nearly everyone.
Next, open your target form in design view. Since we removed the linked tables, the form will now show errors about missing data sources. Clear out the record source property so that the form is truly unbound. This prepares it to accept a newly created record set at runtime.
In the form's properties, set up procedures for the OnLoad and OnUnload events. You will need to declare form-level variables for database and record set objects, so they persist for as long as the form is open. When the form loads, use VBA to initialize these objects by opening the database using your global connection string, and then opening a record set for the table or query you need. Set the form's record set property to point to this new record set, so the data appears and is editable as usual.
Be aware that when using a connection string to open a remote or secured backend, you have to specify the record set type, such as Dynaset for editable data or Snapshot for read-only use. Once your form is running, you will be able to view and edit records as normal, but without exposing any linked tables that might be vulnerable to snooping.
When the user closes the form, make sure to use the unload event to properly clean up the database and record set objects. Use error handling in this event because unexpected closures can sometimes cause errors if those objects have already been destroyed. This step is essential for freeing up memory and keeping your application stable.
The advantage of this method is that your forms will connect directly to the data using VBA, removing the security problems that come with linked tables. This approach works not only with Access back ends but can be adapted for SQL Server databases as well, with some variations in the connection string and object handling.
There are some additional considerations with more advanced forms, such as continuous forms or when you want to open a form filtered for a specific record (using "openargs"). I demonstrate these techniques in detail in the Extended Cut video for members, where I also cover how to handle subforms, combo boxes, list boxes, and reporting. Reports, for example, usually require a separate technique involving temporary tables when you are not working with bound tables.
For those seeking a complete solution, I have created a template that builds on these techniques and includes setup for more advanced features, packaged procedures, and full explanations for both Access and SQL Server back ends.
Finally, to further improve your application's security, I recommend looking at my Security Seminar, which covers everything from locking down your front end to implementing user and group security, logging, and defining object-level access.
Members, be sure to watch the Extended Cut, in which I walk you through continuous forms and open arguments in detail. As a reminder, all silver members and higher can watch any of my Extended Cut videos, and gold members have download access to sample databases and the code vault.
This completes today's TechHelp session on closing the security gap with linked tables by switching to unbound forms and record sets managed through VBA. While it may require some extra effort, this change will provide far better protection for your data and open a lot of customization possibilities. With this system, you can even tailor data source details for specific users.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Fixing Access linked table security flaws
Setting up a connection string in VBA
Declaring a global constant for the connection string
Connecting to a password-protected back end database
Making an Access form unbound
Declaring form-level database and record set objects
Initializing the database connection in the OnLoad event
Opening a record set as Dynaset using a connection string
Binding the form to a record set in VBA
Releasing database and record set objects in the OnUnload event
Error handling during form unload to release objects
Removing a bound table from a form
Editing data via an unbound form with a record set
Using VBA to connect to different back ends including SQL Server
Explaining plain text password implications in Access ACCDE files |
