OleDb connection fails for file opened by EA

[bittercoder]

I've been working on an Add-in that for performance reasons needs to open a connection directly to the EA project (either the SQL server or the EAP (access) database) - works well on 99% of machines, but on a small subset of machines it appears that when the OleDbConnection attempts to connect to an EA Project it fails, with the following exception being thrown (.Net Framework 2.0)

System.Data.OleDb.OleDbException: Could not use ''; file already in use.

I don't have access to the failing machine to perform additional diagnostics with a debugger etc. attached - but I was wondering if anyone had some ideas.. it appears to hang for about 200+ seconds, so I'm guessing the exception is thrown as a result of a timeout while waiting to get access to the database, the call is made after EA is already connected i.e. I'm grabbing the connection string details from the repository itself, I know the filename appears to be blank, but I believe that's just an oddity of the way the exception message is formatted.

Any thoughts on what the problem could be, or tips on how I could track down this issue without actually having access to the target machine?

[Geert Bellekens]

Maybe it has something to do with the allowed number of simultanious connections to the database on the target machine?

[bittercoder]

Is there actually a limit imposed by EA or Jet when connecting to an EAP (MDB) file though?  ... This appears to be happening as soon as the code attempts to create any kind of connection, i.e. it's almost like EA has locked the EAP file for it's exclusive use... I was wondering if this could be related to the Lock file (i.e. LDB) though I'm not exactly sure what to look for.

[«Midnight»]

See if anyone has the Desktop Edition of EA. That version used to exclusively lock EAP files.

Otherwise, carefully inspect your connection string. MS Access has a specific syntax for locking. Most of the time you'll never see any version of this, since there are defaults. However, if one of your users has MS Access (rather than just the Jet drivers) they may have set the defaults (via Options or something) to lock exclusively.

Note that the exclusive user does not have to be the one to first open the file. You'll get a failure message if Jet cannot establish an exclusive lock because someone else currently has write access to the file.

David

[bittercoder]

Your absolutely right, the Desktop edition certainly does cause this problem - is there any known work around for this, or will I have to resort to only making calls against the EA automation interface instead of making use of direct SQL calls?

[bittercoder]

...since there are defaults. However, if one of your users has MS Access (rather than just the Jet drivers) they may have set the defaults (via Options or something) to lock exclusively. David

David does this mean that say if I load Access 2007, go to advanced options, and change the "Default record locking" from "No locks" to "All records", and change the Default open mode from "Shared" to "Exclusive" that I'm likely to see this behavior with any edition of EA combined with my Add-in as well? ... nice little gotcha that one!

[«Midnight»]

Any Sparxians have an insight here?

I'm not sure, but I think you're in a bind.

If you don't need to write to the file via automation (i.e. you are only harvesting information) then you might be able to tweak your automation connection string. You'll have to write the string yourself, not use something EA created for you.

It has been quite some time since I did this stuff with Jet, so I don't have the syntax handy. You can look at a reference text or query the MS online documentation to find the settings you need.

Still, you might have to consider some other means, since the Desktop Edition is not very flexible.

David