You do Not Have Exclusive Access to the Database 2

[vanessa03]

I have inserted the Execute statement in the following code below to run an update query to update the date the labels were printed. It works great, however, when I go to design mode after testing, I get the error "You do Not have Exclusive Access to the Database". I physically have to get out of the database and get back in to continue. I am new to VB, so I would greatly appreciate any help. Thanks.

Private Sub cmdPrintBulkBagLabelsCrystal_Click()
On Error GoTo Err_cmdPrintBulkBagLabelsCrystal_Click

CrystalReport2.ReportFileName= "M:\AccessDatabases\Inventory\PPProductionLabelsBulkBags.rpt"
CrystalReport2.Destination = crptToPrinter
CrystalReport2.Action = 1
If MsgBox("Did labels print correctly?", vbYesNo) = vbYes Then
CurrentDb.Execute "qryPPUpdateBulkBagCCLabelsPrinted"
Else
End If

Exit_cmdPrintBulkBagLabelsCrystal_Click:
Exit Sub

Err_cmdPrintBulkBagLabelsCrystal_Click:
MsgBox Err.Description
Resume Exit_cmdPrintBulkBagLabelsCrystal_Click
End Sub

 

[oharab]

Try splitting out your code a little:

Private Sub cmdPrintBulkBagLabelsCrystal_Click()
Dim db as DAO.Database
On Error GoTo Err_cmdPrintBulkBagLabelsCrystal_Click
Set db=Currentdb
CrystalReport2.ReportFileName= "M:\AccessDatabases\Inventory\PPProductionLabelsBulkBags.rpt"
CrystalReport2.Destination = crptToPrinter
CrystalReport2.Action = 1
If MsgBox("Did labels print correctly?", vbYesNo) = vbYes Then
    db.Execute "qryPPUpdateBulkBagCCLabelsPrinted", dbFailOnError
Else
End If
        
Exit_cmdPrintBulkBagLabelsCrystal_Click:
Set db=nothing
    Exit Sub

Err_cmdPrintBulkBagLabelsCrystal_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintBulkBagLabelsCrystal_Click
End Sub

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[vanessa03]

Thanks for helping Ben,
I put in the code, but it gives me a compile error on the Dim db as DAO.Database statement. "User defined type not defined".

 

[PHV]

When in VBE, menu Tools -> References ...
Tick the Microsoft DAO library.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vanessa03]

Thanks to you both. It now works fine.