front end back end help

[oaishm]

I'm new at this, so appologies. This all started when I tried to avoid the:
The database has been put in a state by .. that prevents it from being opened or locked.

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.Open CurrentProject.Connection
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 * Quantity * 1.0925
    .Update
End With
cn.Close
Set cn = Nothing
Me.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

I looked around the web and found no real answers. Apparently, Access decides whether there should be an exclusive connection to the database or not. If they decide there is, some things happen, but most importantly, my application fails. So I tried a suggested hack, open a dummy connection when the form first opens. This worked once.

Then I tried to link the tables into a new front end so there would be a front end and back end. This failed to work as well.

Finally, I tried to open a connection to the data in VBA with a few strategies. None of them worked. The first was a method from Cardoza's Access 2003 VBA book:

cn.open = "File Name=c:\db\scrappink.udl"

where the udl was made with a wizard and the connection worked. This failed with:

rs.open MenuItem, cn

where MenuItem was a table in that table.

I tried:

rs.open "Select * from MenuItem", cn

which magically worked. Then I tried addnew. Alas, it's read only
There's no easy documentation to figure out how to make the connectino not read only

 

[CameronM]

The answer to your initial problem is that you have got an open connection to the database, which you are using when you create the first recordset using the command .ActiveConnection = CurrentProject.Connection. You then attempt to explicitly open it again to create your update recordset using cn.Open CurrentProject.Connection.

Obviously Access wont let you have two connections to the same database open at the same time.

You can get your original code to work by commenting out any reference to the cn object, including, .Open and .Close and modifying the line of code for your update recordset to

rs.Open "CustomerItem", [B]CurrentProject.Connection[/B], adOpenDynamic, adLockOptimistic, adCmdTable

This will use the current connection and should avoid your locked state error.