"SELECT ... FROM ... IN" read-only problem

[drkhelmt]

Alright, this is a little hard to explain but I'll do my best. I'm also not sure this is the forum for it, so please advise if a better forum is available.

I have 2 Access database files:
sales\sales.mdb
accounting\po.mdb

Important users:
me- read\write all directories
shop & sales- read to accounting, read\write to sales

After sales makes a sale, I then assign it a po and give it to the shop to send for outside labor. Shop people need to create a shipper, so in sales.mdb, I have created a report to create a shipper. The onOpen event for this report is:

    Dim sqlst As String
    
    sqlst = "SELECT po.poNumber, po.trackingNo, vendor.vname, sale.saleNo, customer.custName, sale.dateNeeded, saleItem.siDesc, saleItem.quantity, color.color " _
        & "FROM (customer INNER JOIN sale ON customer.custID = sale.custID) INNER JOIN (color INNER JOIN ((vendor INNER JOIN (po INNER JOIN siAssignments ON po.poID = siAssignments.poID) ON vendor.vendorID = po.vendorID) INNER JOIN saleItem ON siAssignments.siID = saleItem.siID) ON color.colorID = saleItem.colorID) ON sale.saleID = saleItem.saleID IN '\\sacdsu\sacshare\accounting\po.mdb' " _
        & "WHERE (((po.poNumber) Like '*P' Or (po.poNumber) Like '*Q') AND ((po.trackingNo)=" & Globals.trackingNo & "));"
    Me.RecordSource = sqlst

Here's the problem, if I have po.mdb open, the shop guys get an error ... could not use po.mdb; the file is already in use. If I do not have the po.mdb open, everything works as it should and the shipper gets created.

I don't think the po.mdb is opened exclusive since other accounting staff can open the file and issue POs when I have it open.

Any thoughts or work arounds are appreciated.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[danvlas]

Looks like the 'offending' workstations are attempting to open the file exclusively. The others aren't.

Check the general options of Access.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[drkhelmt]

hey danvlas,

Thanks for the input. I checked the shop's workstation and mine and both open shared as their default.

Any other ideas?

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?