Good practice advice please

Apologies for the long post, some issues that have been bugging me for a while: -

I have built a multi-user vb6 app running off a fairly complex access database. Whilst always needing development I wish to put less focus on extending functionality for a while and try to perfect the existing infrastructure as much as possible. This is as much because it is 'the thing to do' as well as trying to pre-empt MS Accesses weaknesses with multiuser, complex databases.

I'd appreciate any pointers anyone has regarding improving performance/stabililty of generic vb/access applications.

===========(This part is optional reading)===============
Some areas i've identified/think that i should look into more are: -

Architecture (ok a big step to be puzzling over now):
I am currently using a local exe file for each user and connecting to a common shared database. Am i right in assuming this is the best approach as all of the processing is done local to each machine and then only db requests will be sent across network which minimises network traffic and maximises performance? are there any benefits to running the exe across the netork?

globally available recordsets:
currently i'm dynamically populating combo boxes/list boxes etc that are viewed by multiple users frequently, my app currently has 3 main forms that are used frequently using unload/load to view the different forms, if i'm populating on form load are these combos querying the database every time each user views each different form? If so is there an approach to use a sort of shared object so the database needs only be hit once then only when the data is changed? Or would i be better off using show/hide but then risk the data currently in the form being out of date?

Error handling:
Currently i use very little, when an error occurs the users generally let me know about it I'll look into it, they'll lose their current data and re-load the app. If I on error resume next I know i'll have a much less stable application but whats the intermittant solution where errors are identified and minimal data is lost for the users?

DB Connection and recordset usage:
I currently open a connection for each user to the db on opening the app and close it on leaving, whilst running the app recordsets are opened and closed and destroyed as needed. I am also using across my app "myRecSet.Open StrSQL, MyConn, adOpenDynamic, adLockOptimistic" to return recordsets, never really got my head around cursor types and lock types, is this something that could be making much impact on the performacne and stability of my app?
=================(end of optional bit)========================


That'll do for now, overall i'm just interested in the 'right way of doing things' which I also know is a very subjective matter. Any thoughts, ideas and direction anyone can give is much appreciated.

Cheers

Hi,

Quite a few questions indeed...
Here're some answers

>are there any benefits to running the exe across the netork?

well at least one, think of a moment when you have to re-distribute a new
version of the application

> if i'm populating on form load are these combos querying the database every time each user views each different form?

naturally if you populate the combo in form_load there would be only one call
to the database (unless the user doesn't unload the form)


>If so is there an approach to use a sort of shared object so the database needs only be hit once then only when the data is changed? Or would i be better off using show/hide but then risk the data currently in the form being out of date?

Not really follow here sorry


>Error handling:
Currently i use very little, when an error occurs the users generally let me know about it I'll look into it, they'll lose their current data and re-load the app. If I on error resume next I know i'll have a much less stable application but whats the intermittant solution where errors are identified and minimal data is lost for the users?

Please have a look here
http://www.xtremevbtalk.com/showthread.php?threadid=16987


>never really got my head around cursor types and lock types


http://www.xtremevbtalk.com/showthread.php?t=39722
(see post #9)


Regards,
Shurik.

Shurik12 -
I went to the first link http://www.xtremevbtalk.com/showthr...?threadid=16987 and then clicked on the attachement on that post but I get a "You are not logged in or you are not allowed to access this page" error. Well I am logged in - hence the post. So what do you have to do to get access or is something wrong with the servers?

Hmm... strange I have no problem.
Could you try one more time
http://www.xtremevbtalk.com/showthread.php?threadid=16987

Well when the Login page came up I just re-logged in and it let me in. Weird. But I got the file.

good

With Access, be sure to minimize the number of open connections. The database is easily corrupted when multiple connections are open.

It'a also good to add an automated backup schema, because you'll probably need it in a multi-user environment.

IMO, though, a more effecient use of your time would be spent in converting from Access to MSDE. :-\

cheers guys, much appreciated. I do intend to go down the MSDE route as more users are planned, is this much of a task? I'm starting at the MS site anyone have any other good resources?

Thanks again!

Take a look at the *** for more info.