If you purchased my Access SQL Online Server Seminar, you already know how to connect your Access database to an online SQL Server so people all over the world can use it. This works great, and I still stand behind the method. However, there's a security concern I want to address more deeply.

In the seminar (I believe it's Lesson 5), I show you how to connect to SQL Server using linked tables and how to dynamically relink them at startup, then unlink them afterward. This method is fine if you're working with a small, trusted group of people who you don't suspect are world-class hackers.

Here's the problem:

When you use linked tables, Microsoft stores the SQL Server password in the connection string. They didn't hide it (brilliant, right?). So while it's not obvious or easily found, someone who really knows their way around Access (like me) can extract it. If someone got a hold of your front-end, they could potentially find your database credentials and wreak havoc.

I also cover how to use ADO recordsets to get data without linking tables, which is safer. But that method doesn't work as well when you want to use Access forms, combo boxes, or reports. So it's limited.

While I've been dealing with this dental nightmare over the past week and couldn't record videos, I've been working on something new. I built a prototype database that doesn't use linked tables at all. No local tables either. Everything is done in memory.

Here's how it works:

When you open a form, it binds to an ADO recordset directly in memory. The recordset is filled with data from SQL Server. You work with the data like you would in a regular bound form, but there's no table or linked table involved. For reports and combo boxes, I generate local temp tables on demand. For example, a customer list might only need to update once daily.

This keeps your SQL Server credentials out of linked tables and makes them much harder to get at. Right now, the login info lives in the code, which is better, but still not bulletproof. Someone with a sector editor or decompiler could dig them out.

So here's where it gets really cool...

I'm working on the next level: no credentials stored anywhere in the database. Not in code. Not in a table. Nowhere.

Instead, when a user logs in, the front-end sends their username and password to a secure website (I'll show you how to build this too, or I might make it a service available on my website). That site validates the login, then sends back an encrypted packet with the SQL Server credentials. The front-end decrypts it in memory and uses it to connect. No trace of it is left behind.

That's about as secure as you can get with Access. Even I wouldn't know how to steal the credentials out of system memory without some serious low-level hacking tools and knowledge.

So before I finish turning this into a full seminar or release the finished database template with videos and documentation, I want to gauge interest.

Would you be interested in purchasing a copy of this advanced version? It'll be an add-on to the SQL Server seminar, not a free update, but priced fairly. I did spend a lot of time on this one figuring it out.

Let me know. If enough people are interested, I'll move it up the priority list.