Home » MS Office

Setting Security for Two Access 2003 databases (front-end and back-end)

Looks like MS Access 2003 security has a lot of workarounds and issues. For example, I have two databases (a front-end and a back-end linked to the front-end). I wanted to secure both of them. For example setting password (this is database password and not .mdw) to back-end (BE) blocks the starting up of front-end (FE) database. Joining .mdw in both the databases (FE and BE) also doesn’t work which means it causes other databases to also prompt for credentials. So a combination of setting password to front-end and joining back-end to secure .mdw has worked. Now the databases are secure and can be opened by credentials (.mdw creds for back-end and .mdw creds + database password for front-end). Does someone have an idea why this happens?

1 Answer Found


Answer 1

Hello Prasad.

Setting a database password in't a best practice for securing a database. Although you could protect the backend database using a password, this password will be stored in the frontend database and can be read by sophisticated users. If the backend tables are already linked in the frontend when specifying a password for the backend database, one must delete all linked tables from the frontend and re-link them (specifying the password).
User-level security is a complex topic and I will not explain all of it in detail, here you will find more about it: http://support.microsoft.com/kb/165009.

Simply "joining" a workgroup using the Access menu commands does not have an effect on the database opened at all. Specifying a workgroup here means telling Access to open that other mdw file (instead of the default system.mdw from your user profile) when starting Access in the future. Configuring security in the database right now will be quite useless and will not secure your database. To see that I am right, start Access and join the default workgroup again and re-open the backend database. This should not be possible if your database was secure.
The reason why "other databases prompt for credentials" is that you set a password for the Admin user in the workgroup file that  Access uses when starting (your new workgroup). When the Admin user in the workgroup used has a password, the user is prompted (and if not, the user is automatically logged on as user Admin). To solve this issue, re-join the default workgroup. This should always be done after creating a new workgroup file. To tell Access to use your new workgroup when opening your database, use a shortcut to Access that contains command line parameters: "DatabasePathAndFile" /wrkgrp "WorkgroupPathAndFile"
Users and groups are stored in the mdw file, permissions are stored in the database.

Here are the essential steps for securing an unsecured database.

Create and join a new workgroup file using the Workgroup Administrator. Close and restart Access. Create users and groups in the new workgroup, configure group membership. Don't forget to add a user account for you that belongs to the Adminis group. Afterwards, remove the Admin user from the Admins group. Define a password for the Admin user (you are logged on as Admin) Close and restart Acces. Create a new blank database and log on using your username when promted to (no password yet). Define your password, if you wnat to (and you definitely should). If you want, specify the default permissions for new objects in your database. Import all objects from the unsecured database to the new database (including custom menues and import/export specifications, if present). Configure/verify permissions. The Users group and the Admin user should have no persmissions at all, don't forget to select the object "Database" in the permissions dialog. Create a shortcut that starts Access, opens your database and uses the new workgroup file Re-join the default system.mdw file to make Access use that one by default again.

In steps 1 and 3, don't forget to write down (or take screenshots of) the workgroup information shown and the username/groupname and PID. This information must be available if case that your workgroup file has to be re-created for whatever reason. (The PID is only displayd while entering it. After creating the user/group, it can never be displayed again.)

Following these steps for frontend and backend databases will make them secure.

Kind regards,



I splitted my access 2007 database into back-end/front-end files. From another computer, belonging to the same workgroup as the computer that holds the back-end file, I am currently trying to open the front-end file and access return this error message: The Microsoft office Access database engine cannot open or write to the file "path\file_name." It is already opened exclusivly by another user, or need permission to view and write its data.

path\file_name: is the file name that I put while spliting the database. I am sure the path is correct.

My question is to know if I there is a way to define permission as far as who can access the back-end file from the network.

Thanks for helping.

Getting ready to migrate about 30 Access 03 dbs to Access 07.  Most of my DBs apps link to tables in multiple DBs. Are there any known problems of having mixed backends?  Access 07 front end with both 03 and 07 links?
I read some documentation on the web today about using Excel as front end and
SQL Server as back end.

Actually, this link was pretty good:

This is pretty good too: http://bytes.com/topic/sql-server/answers/486783-updating-sql-server-Table-using-excel

This is very good; gets pretty complicated though:

I’m still pretty confused about how it all works. I found a sample of VBA
online that lets me import a couple columns from a Table:
Sub Import()
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = "select LastName, FirstName from Employees"
' Set up the connection string, reference an ODBC connection
connstring = _
' Now implement the connection, run the Query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), Sql:=sqlstring)
End With
End Sub

That’s pretty slick, actually! However, I’m thinking I need to use Excel to
LINK to SQL Server, not COPY or IMPORT. Basically, I’m trying to find an
easy way to use Excel as a front end interface so that a user may make
changes in Excel and save all changes back to SQL Server? This should most
likely update a Query and have this Query update several Tables, I would
think. I believe this would be some type of ‘append Query’ or I would
somehow append new records. I do a lot of work in MS Access and if I was
using an Access Form, I’d connect the Form to a Query, make changes to
records in the Form, and save the changes back to the Query, which
subsequently updates the data in the underlying Tables. How can I do
something similar, but use Excel as a front end and SQL Server as a back end?

I’m using Excel 2007 and SQL Server 2008 Express.

I’d definitely appreciate any/all help with this! I guess if I could just
see some simple examples, step by step, and get something working, I could
probably figure out the rest.





As you know there are front-end tasks and back-end tasks associated with the Web. What could we consider ASP.NET, is it suited only for front-end tasks or it could reach to write any "sort of middle-layer system for a three-tier architecture"?




We are in the process of implementing terminal services on Windows 2003 Server. One of the things that will be used with terminal services is an Access 2003 database linked to a SQL Server 2005 database that runs on a different Windows 2003 Server.

The driver used is the SQL Server 2005 native client, not sure if relevant. Prior to activating Terminal Services we tested the Access 2003 FE on the Windows 2003 Server that will be used with Terminal Services. It ran very slow by a factor of 10 or more compared to when being run on an XP box for forms with multiple list views and sub forms.

The Access 2003 FE performance is fine when used on a Windows XP box and connecting to the same SQL Server 2005 database.

Any ideas why? I did see this http://www.tek-tips.com/viewthread.cfm?qid=1086592&page=1

By the way the Windows 2003 Server is fully service packed.




Sorry bit of a SQL newbie here.



HOSTED SQL 2008 Server

Access 2000 Front end

I have a table 'Trainees' which has 65 columns. The access front end is used to update data in this table and one field 'Photo' stores an image in a VARBINARY(MAX) field.

Now everything worked perfectly until I recently added 2 more columns to the table using SQL Server Management Studio. As soon as I re-linked the table in the access front end, the images will not save to the database.


Now from what I can see happening:

Backup of Access Front end, will still post images to the 'Photo' field.

Current Access Front end (re-linked) with extra 2 fields showing - will not post to 'Photo' field. In fact, upon clicking the save button, you can see the image blank before the form closes.

Is there a limit to the number of fields a linked table can hold in Access?

As I say bit of a novice and not sure how to rectify this, any suggestions would be appreciated.



Is it possible to do a distributed BizTalk UDDI 3.0 so that we have one UDDI backend database and multiple UDDI web front ends.  We would like to have uddi web service for each environnents (Dev, Uat, Integratio, Pre-production and production). My constraint is that i can't have more than one sql server and i think that UDDI 3.0 don't support multiples instances on the same server.

Thanks for help.

Is there any way to set call forwarding settings on behalf of end users on the FE server? We have a deployment where we need to temporarily forward a large number of users calls to their old IP-PBX number and would like to avoid having the end users setting the CFA in MOC themselves.

hello everybody, i dont have knowledge of the dot net platform, plz tell me if i can link an oracle database to a c# front end. if this is not possible, are there other ways to build a database an link it to front end.

hi friends please tell me how to generate databases,tables,procedures programmatically from front end using c#.

thanks in advance

i had wrote this code in notepad and using this text file in my application but i am unable to write stored procedure please tell me how t o do this





IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tblPermissions]') AND OBJECTPROPERTY(id,

N'IsUserTable') = 1)
CREATE TABLE [dbo].[tblPermissions](
    [PermissionID] [int] NULL,
    [UserType] [varchar](25) NULL,



IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tblusers]') AND OBJECTPROPERTY(id, N'IsUserTable')

= 1)
CREATE TABLE [dbo].[tblusers](
    [Userid] [int] NULL,
    [UserCode] [varchar](25) NULL,
    [UserName] [varchar](25) NULL,
        [LoginName] [varchar](25) NULL,
        [Password] [varchar](25) NULL,
        [EmailID] [varchar](25) NULL,
        [MobileNo] [varchar](25) NULL,
        [PasswordRetrieval] [varchar](25) NULL,
        [HintAnswer] [varchar](25) NULL,
        [PermissinID] [int] NULL,
        [PunchID] [int] NULL,
        [IPAddress] [varchar](25) NULL,
        [FingerPrint] [varchar]




IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tblPermissionDetails]') AND OBJECTPROPERTY(id,

N'IsUserTable') = 1)
CREATE TABLE [dbo].[tblPermissionDetails](
    [PermissionID] [int] NULL,
    [Permissions] [varchar] (25) NULL,
    [Permitted] [varchar](25) NULL,
        [PunchID] [int] NULL,
        [PunchDay] [varchar](25) NULL,
        [PunchTime] [datetime] NULL,
        [PunchTolerance] [varchar](25) NULL,



We are upsizing our MS Access database to SQL Server 2008 and want to let users keep on using MS Access as a front end since they are familiar with the current application's reports and data entry screens. The users have been using MS Access replication so what is the best way to let these "Occasionally Connected" users replicate their local MS Access data to SQL Server?

On the SQL Server side, merge replication is the best option. I thought one option was to use SQL Server Compact Edition on the laptops so it would be a SQL Server to SQL Server replication scenario with MS Access being a front end to SSCE but that doesn't look feasible from other posts I've read.

How have other people solved this? It would seem that the synchronization framework would be a natural fit but it looks like this is not feasible as well.

Thanks in advance for any advice.



I have a custom message interceptor  where I'm setting CorrelationManager.ActivityId  which I get from the client everyting works fine for successful method call,but when exception happen new activityid is created and ignored the ActivityId   which I set in the interceptor  and new event is recorded in the DB.All this work if I set the Monitoring to "HealtyMonitoring".

Any help is greatly appreciated.





If you have an Access 2007 database that's been split into a front-end and back-end design, what OS does Microsoft support for "hosting" the back-end database file?  The system is very small with two Windows 7 PC's on a hard-wired GigE network. 



According to the Windows (Microsoft Office) Live Small Business (WLSB) help content, there is a means to setup a survey that will allow you to use Sharepoint list data from the back-end of a WLSB web site to validate an entry from a front-end web site answer. If this is still possible, is there any current detail information that demonstrates a step-by-step approach to how it can be done. The usefullness of this solution would aid in web page development allowing for the developer to have a landing page in which a client or customer could fill out a survey, enter their client identification number, and then have the Sharepoint list data on the back-end validate the user's id so that if the id is not a valid pre-pitted id entry the storage of the survey entry could be declined.

The specific message in Sharepoint survey development is "Lookup (information already on this site)" [when creating a survey question]. It is an option under the type of answer to this question is.

Do any of you have knowledge that you could share in order to solve this solution delima?

MCITP-SA, MPN, CRCP, and WestconGroup Partner; http://oguninternationalmedia.com
I am trying to distribute an Access 2007 application with a back-end database.  I'm using the Access Developer Extensions to create the runtime app.  I don't know how to reconnect the front and back-end databases after installation at the deployment pc.  I cannot find much information on this topic, so any advice would help.


I am facing issue to build an COM dll for 64-bit environment.

I have followed the steps given at http://msdn.microsoft.com/en-us/library/9yb4317s%28v=VS.80%29.aspx

Still getting above error. I am using VS2005.


If any one knows answer and replies to this that will be great help!



What makes a web front-end server a front-end server ?

I know it might be strange, but are there any recommendations regarding on how to configure a WFE in a small farm?


Thank you for your help


I have several apps developed in Access 2003 and distributed to computers running Access 2003 runtime. Macro security was an issue when I first distributed these, since there is no user interface for disabling macro security, and the user would receive a series of three popups every time the app was opened. The first is, "Do you want to block unsafe expressions", and the other two are similar.

I have always been able to bypass these with this registry entry:


However, I just had to do my first install of one of these apps on Windows 7, and even adding the registry entry does not bypass the security-related messages. I am not prepared to take the time to update all my apps to Access 2007 or 2010 at this point. How can I get past this to avoid frustrating the user with having to answer the three security-related questions?


I've been doing quite a bit of copying versions, loading versions in staging, validating and committing them.  And now something seems broken.   I see all the versions for the model listed in viw_SYSTEM_SCHEMA_VERSION, but I can't see them through the front-end.

All the data manipulation I've been doing has been through t-sql, not the front-end.  I've been loading the staging tables, and then running some of the following sprocs:





.udpVersionSave ...use this to commit and set a flag



udpVersionSave ...use this to set a flag on just copied version

my initial guess is that I messed things up by using transactions and try/catch blocks.  I was having weird things happen, so I ended up getting rid of the transactions.  Now, however, something isn't right.



Hello All

Our BI solution has the typical steps you'd see, with a suite of SSIS packages all running each night etc, to populate our Executive Information System (Pro Clarity, SharePoint, SSRS etc).

Currently, only myself and the other developer, receive success/failure emails after each step is run. To summarise, there's a main data import from the legacy systems, then there's a set of packages that populate the fact tables (with the intelligent bit inside) then we create dimensions and process them etc. This takes place twice, for different areas of the business. Each day, we expect around 6 success emails for various different steps.

What I'd like to do is create an SSRS report or page (or whatever?) that our users can look at each morning and see if the system is ready to use and is up to date, maybe like a traffic light notification? I know I can simply have a table with run dates and use update statements at the end of each email notification task to update a flag etc but I was wondering if there's a more intelligent way of doing this, perhaps using some of the built in tools in SSIS?

I'm open to suggestions and opinions here, as I'm guessing there's no particular way of doing this. It's not a major issue, just something I'm looking at implementing when I have some spare time!

All the best,


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure