MS Access and ASP.NET

[mdProgrammer]

I have a database (developed by another programmer, but I'm helping them out) that uses data in SQL Server. The data was originally in access, but a requirement was added to have webpage functionality, and it use it in another 3rd party software. Basically, the structure (which wasn't well defined at first) is like this -

The database has to do with job postings and resumes.

- All staff members can access one portion of the database to search job postings, and add/edit resumes. This is the webpage portion.
- Certain staff members have access to a different set of data which is adding and editing job postings. This is the MS Access portion.
- There is a 3rd portion which takes data (originally from two MS Access databases - provided that the person doesn't have it open), and makes a report in a 3rd party software.

Now, why isn't it all in SQL Server? It wasn't my call.

Ok, so here's the thing. I recently found out that multiple people are going to be using the MS Access database. The problem is, you can't have more than one people open the same MS Access file. Now, the MS Access file links to the tables in SQL Server, so I can't make a backend - since they won't show up there. The MS Access table is set to open in shared mode, though. Is there a way to allow multiple people to use one MS Access file? (i.e., remove the locking)

I already tried making a backend, and having ASP.NET access that. The problem is, the other developer bound many of the fields (even in search forms) in the MS Access database to the data (originally in MS Access, and now in SQL Server). If they were the actual tables in the backend, I run into this problem:

Whenever a backend table (or even the physical table in the same MS Access file) is "open" (even if it's a bound combo box list), ASP.NET will return an error that it can't lock the file. This will be a problem since I know our users tend to leave programs open (even though they're not supposed to), and even if they're viewing a search page, it will render the web application unable to search, or even read/write data all because it can't lock a file.


I did notice one interesting thing, though. If you have multiple asp.net application files loop through several hundred iterations of opening/closing an MS Access file, it will "break" the lock, and it can be accessed even if a table is open.

 

[mdProgrammer]

Basically, what I want to do is disable the file locking in that database.

 

[JoeAtWork]

you can't have more than one people open the same MS Access file

In Access, look at Tools -> Options -> Advanced tab

Make sure Default Open Mode is set to "Shared".

 

[mdProgrammer]

It's already in shared mode.

 

[mdProgrammer]

Another odd thing I noticed -- I tried it with another computer, and they were able to get access, but only read only access. Now, both the other computer and mine access the file using a workgroup file (mdw). The owner of the database doesn't use an mdw file. When he had it open (without having to login -- which seems to default to admin, I couldn't access it on my end.)

 

[PaulBricker]

This is probably an SQL server issue and not an MS Access issue. Your table is linked to an SQL server backend so the record-locking properties for that table are inherited from SQL server. You might want to post the question in the SQL server forum.

Paul