database has been put in a state that prevents it from being opened or locked

[oaishm]

I've read about this on the web a number of times. This allegedly occurs when Access, on its own volition, decides that it is better to open the database in exclusive mode and thus prevent any subsequent opening. The solution has been to open two connections through VBA and just leave one of them open. I guess they didn't just find a patch to this bug as no one has an option to just open the database in shared mode or turn off exclusive opening on a single user database. Anyway, It tried to open two connections when a form opens and jsut keep the connection open, but it errors out upon opening of the form:

cn.Open CurrentProject.Connection

Does anyone have a consistent solution that always works?
By the way, in tools options advanced, I've checked shared, no record locks etc. etc.

 

[oaishm]

Finally, Finally a solution. 16 hours of fighting later and finally a solution. The solution sucks though.

Here are a few things that don't work.

Do not try to open a second connection through vba when the form opens. Maybe try one when the application opens. Normally forget it though.

Don't try to link the tables from one database to the other and then expect to just work with the linked tables in the client. That's a failure in the waiting as well.

I linked the tables from server to client. But then on top, I also had to use ADO to manipulate the server database as if the tables weren't link. I'm sure there's a less rookie way to do it, but no one has documented this.

Here's another poorly understood item not found in books everywhere. Making a connection that is not currentproject.connection

For other beginners, here's the sweet secret. Go to connectionstrings.com. This covers almost everything about connections except this: To put the database into READ and WRITE mode, you must stick in mode=readwrite. Unbelievable.

Here's my full connection methodology:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\Documents and Settings\Administrator\My Documents\scrappink.mdb;" & _
        "Mode=ReadWrite;" & _
        "Persist Security Info=False"
cn.Open

If there's no Mode=ReadWrite, Access defaults to read only.

Here's my full code. I hope this helps someone else from burning up 48 hours of punishment. Of course, this thing is dog slow on the updating. I haven't figured out why. If someone who's better at this wants to opine, I'd be grateful.

Private Sub plu_AfterUpdate()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim RA As Long
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
    .CommandText = "Select * from MenuItem where PLU='" & plu & "'"
    .CommandType = adCmdUnknown
    .ActiveConnection = CurrentProject.Connection
    Set rs = .Execute
End With
rs.MoveFirst
nme = rs!Description
price = rs!price * 0.85
rs.Close
Set rs = Nothing
Set cmd = Nothing
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\Documents and Settings\Administrator\My Documents\scrappink.mdb;" & _
        "Mode=ReadWrite;" & _
        "Persist Security Info=False"
cn.Open
With rs
    .Open "CustomerItem", cn, adOpenDynamic, adLockOptimistic, adCmdTable
    .AddNew
    !Item = plu
    ![Item Name] = nme
    !Customer = cardnum
    !price = price
    !quantity = qty
    !Tax = price * qty * 0.0925
    ![Total Amount] = price * qty * 1.0925
    .Update
End With
cn.Close
Set cn = Nothing
Me.Requery
Me![CustomerItem subform].Requery
qty = 1
plu = ""
plu.SetFocus
End Sub
Private Sub plu_BeforeUpdate(Cancel As Integer)
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim RA As Long
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
    .CommandText = "Select * from MenuItem where PLU='" & plu & "'"
    .CommandType = adCmdUnknown
    .ActiveConnection = CurrentProject.Connection
    Set rs = .Execute
End With
If rs.EOF Then
    MsgBox "This item doesn't exits"
    Cancel = True
End If
rs.Close
Set rs = Nothing
Set cmd = Nothing
End Sub