Question Multiple connections (1 Viewer)

[LambtonWorm]

Hi All

I know the multiple users issue is extensively documented in this forum and elsewhere, but I've been unable to achieve what I need yet.

Background:
I have an Excel application. This reads/writes to an Access DB, using SQL strings in VBA, via DAO. Works fine.

Requirement:
I need to be able to have other users simultaneously read/write to the same DB too, via Access, while the Excel app is running.

Details:
I think the problem may be the way I am accessing the DB from Excel, which is probably unsuitable.

I have the following as global constants, (bad idea?)

Public g_dbFAT As dao.Database
Public g_wrkTrades As dao.Workspace

Then set both objects using below abridged function at start of code.

Public Sub DB_OpenDatabase(sFilename As String)
 
    On Error GoTo errHand
    '  validation here..
 
    Set g_wrkTrades = CreateWorkspace(vbNullString, "admin", vbNullString, dbUseJet)
    Set g_dbFAT = g_wrkTrades.OpenDatabase(sFilename, True) 
 
wayout:
    ' housekeeping here..
Exit Sub
errHand:
    ' error handling here..
    Resume wayout
End Sub

Then I just refer to the global constant g_dbFAT in the code wherever i'm querying the DB etc.

So, this method of accessing the database appears to be locking it out.

What I've tried so far:
I have successfully split the DB into front-end and back-end. That allows multiple access sessions, but when the Excel app is running, it's still locked.
I found this link, softcodedDOTcom/web_design/upgrading_accessDOTphp , it would be useful but appears to be aimed at ADO. Everything is already written fro DAO in this application.

As you can see, I have no idea what I am doing. Sorry.

Thanks very much!

 

[LambtonWorm]

Re: Multiple connections **SOLVED**

I think I might have answered my own question..

instead of above, using

  Set g_dbFAT = g_wrkTrades.OpenDatabase(sFilename, [B]False[/B])

tells excel to open the DB in non-exclusive mode.

However, the question about whether using a global variable for the Database itself is appropriate or not, if anyone has a better way of doing it then let me know..

Thanks