Securing an Access Database or ADP

Posted on 2009-04-30
Last Modified: 2013-11-29
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.
Question by:drtrmiller
    LVL 9

    Expert Comment


    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


    Author Comment

    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)
    LVL 9

    Accepted Solution

    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


    Author Closing Comment

    I don't know what I was doing before, but it now works!! Amazing!!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now