[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Securing an Access Database or ADP

Posted on 2009-04-30
4
Medium Priority
?
685 Views
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 godaddy.com 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.
0
Comment
Question by:drtrmiller
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:borki
ID: 24277038
Hi

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...

HTH

Felix Burkhard



0
 

Author Comment

by:drtrmiller
ID: 24277068
This is the information I have when I set up the SQL Server with my shared godaddy.com hosting account:

Host Name:  XXXXX.db.2940227.hostedresource.com
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:

https://p3nmssqladmin.secureserver.net/6/scripts/2005/login.aspx?uniqueDnsEntry=XXXXX.db.2940227.hostedresource.com

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

(XXXXX represents the SQL Server and YYYYY represents the password)
0
 
LVL 9

Accepted Solution

by:
borki earned 1500 total points
ID: 24277881
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



0
 

Author Closing Comment

by:drtrmiller
ID: 31576741
I don't know what I was doing before, but it now works!! Amazing!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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