By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Is this technique efficient for multi-user environment?

P: n/a

Hi,

I am writing an application in Excel 97 in which the users click a
button and data is saved/read to and from an Access 97 database on the
LAN. I am concerned about performance because there will be multiple
users using it at the same time.

Generally my technique for database reads is as follows:
-I create parameter queries in the database. Doing so allows me to use
a sort of 'function' paradigm. i.e. given x and y, query fn returns a
recordset
-Note: To do so, I often have chains of queries that query other
queries where each query perform a specific task (ie find max value in
one table) and is joined to another. Are there significant penalties to
doing so vs SQL subqueries?

My VBA code (in Excel Class Module) to utilize these queries is:

public function GetStuff(CatID as string)
Dim Db As DAO.database, mySet As Recordset, daoWS As DAO.Workspace
Dim DataField As DAO.field, sql As String, myQuery As QueryDef
Set daoWS = CreateWorkspace("", "admin", "", dbUseJet)
Set Db = daoWS.OpenDatabase(dbPath, False, False)

Set myQuery = Db.QueryDefs("qry_0440_GetData_CatID")
myQuery.Parameters("CategoryID").Value = CatID

Set mySet = myQuery.OpenRecordset(dbOpenSnapshot)

If Not mySet.EOF Then
mySet.movefirst
do while not myset.eof
someValues = mySet(0).Value
'etc
'etc
loop
End If

'Close objects and set to nothing

end function

My write-to-DB technique is similar except that I use
db.Excecute "INSERT ..."
if db.recordsAffected <1 then err.raise ...

My major questions are:
1. Does this technique make sense?
2. What kind of overhead is there to create a QueryDef object, populate
it, open a recordset and use it vs just a Db.OpenRecordset("SELECT
....")
3. Should I anticipate locking problems with multiple users using this
technique?
4. Are there best practices for testing a multi-user app like this? I
was thinking of having two computers write at the same time, read at
the same time, and read and write at the same time.
5. My question above about the efficiency of setting up queries that
perform small tasks and then having a master query that joins them
together to perform a bigger task.
Advice is greatly appreciated.

Thanks,

Michael D.

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.