Create Connection without Lock File

[tcjones]

Hi there, we currently have a database that users write to through an excel form. Each time a user sumbits a record to the database we open a connection up with the below code, insert the record and then close it. This creates a ldb file for 1-3 seconds depending on how long it takes.

' OPEN DATABASE CONNECTION
Set dbConn = New ADODB.Connection
dbConn.CursorLocation = adUseClient
dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
dbConn.Open sConn

' INSERT RECORD THROUGH EXECUTE COMMAND
dbConn.Execute("INSERT INTO.... VALUES....")

' CLOSE CONNECTION
dbConn.Close

Is it possible to open the connection up without an ldb file being created so more than 1 user can insert a record into the database at the same time?

Thanks for your time,

Cheers

Tony

 

[Pat Hartman]

Access creates the .ldb file because that is how Jet controls sharing so no, there is no way to avoid it. The presence of the .ldb file is not preventing sharing the database, in fact, it is what allows sharing. Check the network permissions for the files and the directory.

 

[tcjones]

Thanks Patt, I've checked the folder permissions on the database and all users have access to read and write to it. It seems that whenever someone is writing to the table it will 'lock' it and the next user wont be able to write to it until the ldb file has cleared itself.

Is there anything wrong with the connection string?

Thanks again for your help,

Cheers

Tony

 

[Pat Hartman]

I don't have any experience with Excel inserting rows into an Access table so I can't help you with the sharing problem. It seems a little like an ant lifting an elephant. Why would you not simply create an Access application with forms and export data to excel when people want to play with it?

These are the only responses so far on the MVP site:

I can't imagine Access inserting semi-colons like that. There has to be some other external explanation for the files.

Looks to me as some sort of Unix or VMS/VAX versioning; so I suppose that maybe the file server is a UNIX or a VMS/VAX server or something like that.

 

[tcjones]

In an ideal world we'd have access to SQL Server for this app... Basically the database is acting as a holding point, with over 1000 sites writing to it every day.

It's kind of like we are using Excel as our web form and access as our back end. Not ideal but it works quite nicely.

We then have a second database that grabs the tables it needs out of the database above so we can export reports on the information received. This leaves the first database free to simply receive information and do nothing else.

Unfortunately we can't deploy access forms to the users as quite simply they don't have access...

Any other ideas? I've been searching and from what I've read access should be able to handle around 20 simultaneous connections. I'm hoping this is the case as the users are simply inserting rows and not affecting the table in any other way.

Any ideas would be greatly appreciated.

Cheers

Tony

 

[Pat Hartman]

Unfortunately we can't deploy access forms to the users as quite simply they don't have access...

The solution for this is to purchase the "Visual Studio Tools for the Microsoft Office System" which in older versions of Access was referred to as the developer's edition.

The product includes, among other unnecessary but expensive components, the packaging and deployment wizard and the license necessary to distribute the runtime version of MS Access to an unlimited number of PCs.

 

[tcjones]

Thanks for the reply Pat sounds like a good package. Unfortunately we are just not in a position to distribute this version of access to our sites.

This is hopefully a short term solution until a proper web based tool is developed. Thanks for your help with this i'll do some further searching into using excel and access together.