Web Development

Ask something
Discussion Locked

Using Access simultaneously with ASP

0 helpful
2 replies

by shanghai sam

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My scenario:
Developing a web app on an intranet

Specifics:
NT4 Network with NT4 IIS server and seperate NT4 fileserver plus exchange, domain controller servers etc.
Database residing on the fileserver with a copy of it on the IIS fortesting using a ODBC system DSN.

Problem:
I have been tasked with deveolping a medium sized MS Access 97 database with a back end that stores the data an Access front end for the boss to use, and a web based front end for all other users, theproblem is i obviousley need to have both the access and the asp to be able to use the data from the back end at the same time. Not a problem i thought(stupidly!) the back end works fine on its own - it just sits there, next got the the Access front runnig it works well, so i close the front end and try the asp through IE not a problem works well(seems to), so i open the front end again with the web page still up - fine, next open the asp page with the Access front end running thats where it decides not to play.

i get this error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use.

/mandamags/index.asp, line 10

At themoment the front end just uses tables linked direclty so i thought i would use an ODBC connection but that didn't want to play either i got this error message:

You cannot use ODBC to import from, export to, or link am external Microsoft Jet or ISAM database table to your database.

I've tried using ODBC to connect to the database on the file server but it doesn't like that but i think i found a workaround for that at the MSDN.
So if anyone could help me with getting the asp and Access front to run at the same time either with or without using ODBC or any other method it would be apprecited greatly.

Cheers Iain

2 replies

by gbworld

The error could be caused by permissions, as well, but I will assume that you already checked that out.

The easiest method I know is to create shadow databases for people to work in. Rather than open the actual database, they will open the shadow. It is a bit time consuming, but here goes.

1. Create an empty copy of the Access database (you can copy and delete tables).
2. Create links to all of the tables in the old database (the one they originally attached to) using the wizard. Make sure to name them the same thing.
3. Check the queries, et al, you have created to ensure they work.

Now, have the users that used to open the Access database, use the new database that is linked to the old database.

What this will do: Now, both the web app and the users of the database will be connecting through links rather than actually locking the database. There is still a slight chance of a lock, but it will generally be for such a short time that ASP can handle it.

Before doing this, try the following:

Check permissions on the database. In general, you will need the everyone group, unless you alter the permissions on the ASP files to ensure only domain users can use the files. By default, users accessing non-locked down ASP files are authenticated as IUSR_MachineName, which is a local user, not a domain user. If you restrict the MDB in any way, this user cannot access it. The only way around this, other than mentioned, is to create an MTS component and assign its package to a domain user that can access the database.

by glen_mcleod

The 'file in use' error seems to point to exclusive use by one of your front ends.

If I remember correctly, Access 97 opens databses exclusively by default (not so in Acc2k), you have to manually de-select that option or change the startup parameters.

Also check the ASP code that opens the DB. You might have specified (or omitted) the parameter that controls exclusivity, although from your explanation above, it seems to be the Access front end that is causing the problem.

Glen

This discussion is closed to new replies.