Securing an Access Database or ADP

In summary, I am trying to create a MS Access multi-user front-end app that I can continue to develop, possess the capacity to remotely disable outdated applications, and assign user rights that only allow the execution/opening of forms/reports, and NEVER allow direct viewing/designing of tables or queries, all while hosting the back-end data tables remotely.

I am reliant on MS Access 2007 for developing a front-end application that stores and organizes multiple clients' data across a number of tables (customers, insurance policies, invoices, etc).  Each front-end application is assigned a unique ID through a macro.  I have created a decent, albeit easily crackable system of macros that automatically check with the main back-end server to determine if that front-end application is active, or if it has been deactivated, in which case the Access database quits.  Of course, this is circumvented by holding down SHIFT when opening the access file.  

If a user holds down shift when opening the Access file, they can view *everything* in the linked source tables, a very bad thing given that the tables hold data for a number of clients.  One could also modify queries that are designed on a per-application basis to filter only the data that pertains to them.  I don't want that happening.

I am heavily dependent on Access because of the GUI and relative straightforwardness in comparison with other "databases" that are more script and language-intensive, so I would like to keep Access so as to continue developing a working model.  But I need to secure the data and assign user rights easily with regard to the linked tables.  For example, a standard user should *never* be able to directly open or execute ANY table or query.  Only forms/reports within the front-end Access application should display the data and read/write/edit the respective tables.

I wish I could keep everything in Access.  But if a back-end SQL Server were *required,* I have a hosting account with MySql and SQL Server support, and it also supports Direct Database Connections and DSN, but I am clueless as to how to set this up with a front-end Access database.  I can't even seem to get Access 2007 to point to a valid SQL Server.

Thanks in advance for the help.
Who is Participating?
borkiConnect With a Mentor Commented:
Did you create an ADP? Do you connect to MS SQL server? When you look at

Office Menu > Server > Connection

you get Microsoft's standard data link properties dialog. Put in the host name in the server name box etc.

Tried that already? I have used and connected to many remote hosts this way with various hosting providers in AUS and the USA.

Could it be that GoDaddy does not allow you to connect from Access (or SQL Management Studio, Visual Studio, - they all use the same technique) and only allows code connection eg ASP.NET using a connectionstring...

Good luck

Felix Burkhard


If you develop a SQL Server DB (tables. views, stored procedures), you can connect to it from Access using two methods:

a) Create a DSN. In Access link to the tables using External Data > More > ODBC and specify your DSN
b) Create an Access Data Project (.ADP). In Access select New DB > Browse > Select ADP from the type list and create new ADP file. It will prompt you if you have existing data in which case you can create a SQL connection

I have used both methods, but prefer the second as it is closer to SQL Server. On the server side you have all sorts of options to restrict access. However, it will NOT secure your program code nor prevent the user from looking at the forms or reports...


Felix Burkhard

drtrmillerAuthor Commented:
This is the information I have when I set up the SQL Server with my shared hosting account:

Host Name:
Database Name:  XXXXX
Database Version:  2005
User Name:  XXXXX
Password:  YYYYY
DSN:  mssql_XXXXX.dsn

I see the fields in Access 2007 to create the connection, but these are not absolute paths, are they?  When I input them, it can't find the server.  But I can log in on godaddy,com by going to:

What do I need to do with this info to set up the connection?

(XXXXX represents the SQL Server and YYYYY represents the password)
drtrmillerAuthor Commented:
I don't know what I was doing before, but it now works!! Amazing!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.