What is the best way to handle client/server envirnment for ten or more computers in a network using

V

Victor Cheung

I developed the software with microsoft access.. and windows XP.. but..
Windows XP give me hell... so.. probably use Windows 2000 Professional..
which I think is a better OS.. i guess.. my question is how can i avoid..
the problem when two workstation is try to write to a database file.. I
mean.. it is okie.. to read .. from a database.. but.. any error tracking
method.. or code.. that can find out the file is used by another machine..
and automatically wait until the file is free.. and than write to the
database.. instead of giving me error.. when another computer is writing
data to the database.. etc.. thankz..
 
M

Michel Walsh

Hi,


XP is 2000 with some minor improvements.

If you write to the database (JET or MS SQL Server) in a multiple
users environment, you may specify which type of record locking (optimistic
or pessimistic) you want in order to avoid incoherence when the two users
write more or less at the same time on the same record of the same table.
You get run-time trappable error (through an Access Form OnError event, or
through the standard on error handling if you are outside an Access Form)
and there, you can resubmit the "operation", as it fit your need, or cancel
it (if that is how it should be done).


Hoping it may help,
Vanderghast, Access MVP
 
J

Jonny Smith

Hi,
Are you using Access as a front end? It sounds like you already have the
front end split from the back end.

Make sure all of the users are opening the database in a shared mode. The
problem with using forms and a query in the record source is the constant
connection maintained between the user and the backend. If you use a
listbox, you may have more contention for resources. I've read about and
then proved that problems start with 12 - 15 users and by the time you get
to 30 you will have a corrupted database.

Some of the systems I've built have as many as 200 users accessing the
database during a fifteen minute period. The users are writing between 7 and
12 transactions per second. Incidentally this is on an NT machine with 550
mhz single processor and two scsi hard drives.

One of the methods I use is by using unbound controls. The program on the
user's machine connects to the server through ado code. An sql statement
gets the info. The user program disconnects from the database. The user
views or changes the data. A connection is re-established with the database
on the server. The updates/inserts are done via sql code. Then we
disconnect from the database.

The Access help screens can show how this is all done. Briefly what I do is
set up a module with several functions that do all the work:
Create the following functions:
ConnectMDB (inDatabase as string)
GetRS(inSql as string) as adodb.recordset

In the form call the two functions, set your textbox's to the values from
the recordset. For the occasional locking situation this system encounters,
I employ the trappable errors that are mentioned by Michel Walsh. Quite
often all that is needed is to have your program pause for a second then try
again to update. Try to update every two seconds for ? many times.

If you are writing new records (inserts) then locking shouldn't be an issue.
Anyways I could type here for an hour an not cover all the various scenarios
that can be encountered.

There are many good books out there. Also I use the same techniques with VB
6.0 and Access, VB 6.0 and Oracle....

Good luck...
 
V

Victor Cheung

can you post the code for the error traps? that pause and retry.. etc...
thankz.. here is my code..

I use ADO + Jet + Access..


<html><head>
<title>dbnewrec.asp</title></head>
<body bgcolor="#FFFFFF">
<% ' My ASP program that allows you to append a record %>
<form name="myauthor" action="dbnewADOrespond.asp" method="GET">
<p>Author ID: <input type="TEXT" name="id"></p>
<p> Author Name: <input type="TEXT" name="name"></p>
<p> Year Born: <input type="TEXT" name="year"></p>
<p> <input type="SUBMIT"> </p>
</form></body></html>

The form responder looks like this:

<TITLE>dbnewADO.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<!--#INCLUDE VIRTUAL="/ADOVBS.INC" -->
<!--#INCLUDE VIRTUAL="/learn/test/lib_errors.asp" -->
<%
on error resume next
auname=request.querystring("name")
auyear=request.querystring("year")
auID=request.querystring("ID")
If auid<9000 then
auid=auid+9000
end if
conn="DSN=Student;uid=student;pwd=magic"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open "authors",Conn,adopenstatic,adlockoptimistic
RS.AddNew
'RS("AU_ID")=auid
RS("Author") = auname
RS("Year_Born")= int(auyear)
RS.Update
Call ErrorVBscriptReport("Adding Record")
Call ErrorADOReport("Adding Record",RS.activeconnection)
RS.Close
set rs=nothing
%>
</BODY>
</HTML>






Here is the include file that displays appropriate errors:

<%
SUB ErrorVBScriptReport(parm_msg)
If err.number=0 then
exit sub
end if
pad="&nbsp;&nbsp;&nbsp;&nbsp;"
response.write "<b>VBScript Errors Occured!<br>"
response.write parm_msg & "</b><br>"
response.write pad & "Error Number= #<b>" & err.number & "</b><br>"
response.write pad & "Error Desc.= <b>" & err.description & "</b><br>"
response.write pad & "Help Context= <b>" & err.HelpContext & "</b><br>"
response.write pad & "Help File Path=<b>" & err.helpfile & "</b><br>"
response.write pad & "Error Source= <b>" & err.source & "</b><br><hr>"
END SUB

SUB ErrorADOReport(parm_msg,parm_conn)
HowManyErrs=parm_conn.errors.count
IF HowManyErrs=0 then
exit sub
END IF
pad="&nbsp;&nbsp;&nbsp;&nbsp;"
response.write "<b>ADO Reports these Database Error(s) executing:<br>"
response.write SQLstmt & "</b><br>"
for counter= 0 to HowManyErrs-1
errornum=parm_conn.errors(counter).number
errordesc=parm_conn.errors(counter).description
response.write pad & "Error#=<b>" & errornum & "</b><br>"
response.write pad & "Error description=<b>"
response.write errordesc & "</b><p>"
next
END SUB
%>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

My computer will print from ALL other programs except MS publisher 2007 4
"Your network access was interrupted." 10
Reinstalling Access 97 on a Vista PC 7
Excel 2010 fails to save or open from network drive 0
How do I set the necessary permissions to read and write? 1
Problem opening database file via wirelss home network 2
ActiveX Error 429 7
Windows XP Professional SP 3 With RMS POS freezing up 4

Top