ADO Connection Puts Network Database In Read Only

[Schwimms]

Hi All,
I have an excel spreadsheet that is linked into an access database through ADO connections. The spreadsheet is set up to make updates and adds directly into a table in my database. The database is on a server, what is happening is, and I don't know the cause, the database is being put into read only mode and gets stuck in read only. There is no record locking database file that is getting created as well, which is usually what happens when you open a database.
The connections when contacting the database are set to:

Conn1.Mode = adModeReadWrite

and when it is done with either the update or add the connection is closed:

Conn1.Close
Conn1.ConnectionString = ""

Does anyone know how I can avoid my database getting locked up? Or is this jsut a server issue that may be causing it? Is there a way to avoid the server from causing this issue?

 

[xenou]

If a lock file is not created you (or the owner of the file) probably have no write privileges on the directory where the database exists. This must be addressed by the network admins. Access databases must have write privileges since they must create this lock file.

 

[Ruddles]

Xenou, would it be a useful test to copy the database to the local hard disk and try it from there?

 

[slinky]

May be a silly question, but you haven't got the database open have you? I seem to run into read/write issues if I have a db open whilst trying to interact with it from excel..

 

[xenou]

Xenou, would it be a useful test to copy the database to the local hard disk and try it from there?

I think it would - to be sure that the only "problem" is the location of the file and not anything else in the code.

@slinky:
You can set an access database to open in "shared" mode or in "exclusive" mode. You probably want to be sure the database is "shared" if you are accessing it from Excel while it is also open otherwise. You might want to check your Application settings for access to make sure that the default open mode is "Shared".

 

[Schwimms]

So I know what causes the problem. A user will crash their Excel in the middle of refreshing their spreadsheet of the data being pulled from the database. Is there any way to destroy a database connection before a users spreadsheets crashes?

 

[slinky]

I fear that would require a crystal ball... M$ haven't delivered that yet..