Splitting databases

[Roderick O'Regan]

I've created a database for a client (Access 2003 and Win XP Pro).

This application has a facility for creating purchase orders. A user
creates a new PO and the system looks for the last number used in the
table called "Codes" and enters this in the form.

However, if the user decides to cancel the operation the code returns
to the Codes table and subtracts 1 from the existing number.

This is the relevant piece of code behind the Cancel button:
Set rs = db.OpenRecordset("Codes", dbOpenTable)
With rs
.Edit
.Fields("Last_Nbr_Assigned") = pcodevalue - 1
.Update
.Close
End With

However, the client now wants to split the database and Access Help
tells me that dbOpenTable can't work on a linked table sitting within
the backend database. But it doesn't tell me how to overcome that
problem.

How can I modify the Set =rs statement above, please, to allow the
form in the frontend to access the table "Codes" located in the
backend?

Roderick

 

[John Spencer]

Use a query string instead.

strSQL = "SELECT * FROM Codes"
Set rs = db.OpenRecordset (strSQL)

Or perhaps even simpler is just execute an SQL statement

strSQL = "UPDATE Codes SET Last_Nbr_Assigned = " & pcodevalue - 1
rs.Execute strSQL

If this is a multi-user database, you could have problems with your current
approach.
UserA starts creating a purchase order (gets Number 12345 - last number is
set to 12345
User B starts creating a purchase order (gets number 12346 - last number is
set to 12346
User A Cancels does last number get reset to 12345.
Next PO get 12345
Next PO gets 12346 --- whoops got two PO with same number


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

John's answers are correct, but to the dbOpenTable, change it to
dbOpenDynaset and it will work for linked tables..

 

[Roderick O'Regan]

Thanks John and David for your replies. Will put them to work
tomorrow.

Thanks also,John, for pointing out that I had goof'd on my logic. It
IS a multi-user database (3 people) and I suppose, this scenario could
take place.

Back to the drawing board on this! Will warn the users whilst I think
up version 2.