Optimize multiuser Access Database using ADO recordsets 1

[src23]

Hi everyone here,



I have a database application and I'm trying to optimize it, but I'm not sure if I'm doing it right.
This database will be accessed my more than 20 users simultaneously.
Initially I tried to use linked tables - I split the database, the BackEnd was on a remote comp and the 20 FEs were on the workstations-the same network domain. This solution failed right from the start, because when more than 4-5 users accessed the DB, it became EXTREMELY slow (I used bound forms with linked tables - VERY BAD IDEA !!!)


I must say that I have no possibility to use any kind of server (MSDE, SQLServer etc).


Then I thought of another solution, which I want to optimize to work as its best.
I will tell you how I want it to work, and then where I want to make the optimizations, which I'm not sure about.

The BE: I use Access for the back-end database, which will reside on a remote computer.
I don't use MsAccess user security, because I implemented my own security system - users log in with their username and password.

Then, the FE:
I use ADO recordsets. Users WILL NOT HAVE SINGLE - CONTINUOUS FORMS, no need for scrolling through recordsets with hundred records.
For view/edit/PrintPreview, the users will access ONLY ONE record at a time - and that's all. When they want to view more records, they will do a search, limited number of records will be displayed in a TABULAR - CONTINUOUS form, records which they will be able to view one by one using a SINGLE - COLUMNAR form.
I want to optimize EVERY READ/WRITE operation to DB, and to reduce their number to the minimum possible.


Having these into consideration, I thought best solution will be disconnected ADO recordsets.
My questions are the following:

1. Is there any performance difference between ADP and ACCESS databases, using them as FrontEnd, with disconnected ADO recordsets ?
2. What should I use to open a connection: ODBC with DSN or just JET ?

"With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strCONST_database_full_path
.Open
End With

With rs
Set .ActiveConnection = cn
.CursorLocation = adUseClient
etc etc"


3. How much does affect the db speed leaving a connection open from each FE to the BE (there will be more than 15 FEs open simultaneously) ?
... or .. is better to close the connection as soon I disconnect the recordset ? Or, should I keep a connection open, and all the recordsets should use that connection ?

4. Is there any other possibility to manage multiple user access to an AccessDB backend (which is rather a file-server than a client server).... ?

I must mention that the menu for each edited record is defined separately for each type of user - that means that every time a user opens a record, the BackEnd wil not be requested only for the record being displayed, but also an 8-joined-tables query will load the menu for that request and for that type of user. This user-level access menu is a real pain when I used these linked tables.


I'm asking all of these questions because now I'm running out of time, and I don't have enough time to learn only from mistakes. But if you have any recommandations, any useful links, that would be much appreciated. I think I need to learn more about how an AccessDB manages simultaneous ADO opened connections, and multiple batch update operations, and how a connection uses multiple opened recordsets.




Thanks in advance !

 

[petermeachem]

8-joined-tables query

This sounds wrong. Is there any other way to do this?

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[src23]

Yes, there are 8 tables for menus.
More details: it's a loan management app.
Tbl1: RequestType (5 types of requests)
Tbl2: RequestStatus (request will pass through 8 status from analysis to approval)
Tbl3: UserGroup (there are 5 groups of users)
Tbl4-8: For each request status, each user group will have access to different menus. The Menus are commands which are stored in tables (a command consist of a label, and a form name, and some details: fields on which applies filter etc.)

Whic concerns me is not the problem with the tables (which are well normalised - the app has 47 tables) - it is how should I proceed in migrating from DAO to ADO:

I want to make things as simple as possible:

each operation on a request should be very simple - no form with subform - and should consist only of a simple add/edit in a single table

I would like to find an answer to those questions:

1. Is there any performance difference between ADP and ACCESS databases, using them as FrontEnd, with disconnected ADO recordsets ?

2. What should I use to open a connection: ODBC with DSN or just JET ?

3. How much does affect the db speed leaving a connection open from each FE to the BE (there will be more than 15 FEs open simultaneously) ?
... or .. is better to close the connection as soon I disconnect the recordset ? Or, should I keep a connection open, and all the recordsets should use that connection ?

Thanks in advance !

 

[mayajhon]

I don't think I'll be much of help it's just seems to me that You can help me since I have a problem with my VB multiuser application.
I have a problem while using same database in two
applications. I have two different applications
running on different PCs but using the same MS-access
database which is shared..

One application is using database for reading
writing(FFD) and another application(DataReport) for
read only. The PCs are connected through LAN. I run
the one application and without closing the
application, I tried to run another application on
other PC which reads the database. When I close one
application no one can open it again ,who ever is
logged on can use it but it will not allow any new
users and if you logged off you can’t access it until
all the users go out of the application.



When only one workstation using these application
there is no error it just gives an error in multi user
environment.

I’m not sure if it’s Locktype and cursorlocation
problem or there is a problem with the workgroup file
or something else


the error is:

Error Opening the Demo.MDB Database in Directory.
The Database might be opened exclusively by another User.
Could not lock file Share.exe hasn't been loaded(3050).



I use on the application that user read
only(DataReport)



cursorlocation: adUseserver
Locktype: adopenoptimistic
cursortype:adopendynamic


When FFD application runs the following files are open

Client.mdb

client.ldb

system.mda

system.lbs

I use:
With conData
.Mode = adModeShareDenyNone 'Share Database
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=P:\Dir\Demo.mdb" 'Database Location
.CursorLocation = adUseClient

.Open
End With


I don’t know what the other program uses since I don’t
have the source code the only thing that the creators
of the application told me that is a
adModeShareDenyNone connection.

the Access DB on the server is Access 2.0 is much older then the
one I have on the workstation 2000 -2003 convert it, do you have any other suggestions?

Hope I didn't confused you because I am confused.

Thanks so much,

Maya

 

[dilettante]

Here are some thoughts, sort of stream-of-consciousness. Anything that looks like BS or misunderstanding of your questions/needs, please feel free to ignore it.


On your number (3.) I'd recommend opening one connection and then using it for everything until your fat client ("FE") closes. I assume your fat client here is done in Access VBA or something, even though you're posting in what appears to be a VB forum.

No matter, VB or Access VBA, same rules apply. With Access though I'm not sure how you keep Access itself ("front end") from trying to open the "back end" database with an additional connection (I think it's smart enough to use only one itself). No Access forms, reports, etc?

The nature of Jet databases though is that you normally need a file-open on the MDB, LDB, etc. across the LAN. Every ADO connection will have to open these separately. The overhead of multiply-opening/closing the files isn't worth any benefit you might obtain unless you can detect an idle user or something.

The cost of keeping the connection open is pretty much just the cost of keeping files open at the client machine and the file server machine.


Regarding (2.) above, I'd recommend avoiding ODBC altogether unless you have a very good reason. Instead use OLEDB, and use a DSN-less connection string much like the one in mayajhon's example code.

From the MSDN Library:

You can use code to open a database in shared mode. When using ADO with the Microsoft Jet 4.0 OLE DB Provider, the Open method of the Connection object opens the database in shared mode by default.

So don't worry about setting the connection's Mode unless you need some other value.

I not sure what "just Jet" might mean. I think the only meaningful way this can be interpreted isn't worth the trouble from VBA though, because it seems to mean bypassing ADO and OLEDB to me. Perhaps you meant "using a DSN-less connection string."


On (1.) above, I have no opinion.


The idea of using disconnected recordsets for your lookup tables has merit. I'm assuming that's what all that business about "8 joined tables" is... fetching descriptive info to go with codes or foreign key values in a primary table, populate dropdowns, etc.

Not sure how this will work for you. If your logic needs those joins... what a mess. You can't join a table with a recordset, disconnected or not. If you have a way, let me know - I can use that myself!

So you've tried having the FE pull the data in at initialization? I mean copying the BE data from these lookup tables into local tables in the FE database? That keeps them cached at the client, and up to date by fetching them (once each run). Of course then you are back to doing joins with the linked table(s) on your BE database.

Without being able to join stuff in the disconnected recordsets I'm not sure it buys you much though. Then again maybe I don't understand how you are using those tables. You know though, and that's what's important.

Another option though is to use the .GetRows() method of an ADO recordset. Once the recordset is loaded (.Open) you can use .GetRows() to pull the data into a variant variable, where it will be available as a 2-dimensional array. Then .Close and trash (Set rs = Nothing) the recordset to free up resources if you can use the array for what you need the info for.

If your need for the data is fairly "lightweight" (not planning to use rs.Find(), etc.) the overhead cost is less than keeping a bunch of disconnected rs's around. The advantage to this is small though if the tables pulled into that recordset are very large (lots of records, say > 500 or so).


Good luck!

 

[dilettante]

I had another thought today.

You might want to explore what performance benefit (or at least user-perception of benefit) you might see with Jet 4.0 row-level locking on your updates.

Briefly, you set one extra Jet property on the connection:

[tt]conn.Properties("Jet OLEDBatabase Locking Mode") = jetAlcatraz[/tt]

Where [tt]jetAlcatraz[/tt] is an integer constant 1, requesting Jet 4.0's newer (Alcatraz) locking mode.

Note that the first user who opens the database sets the locking mode, you can't use different locking at different times by different users or connections. Of course getting the database closed and then having a (new) first open on it sets the locking mode for the next interval. This means you can use row-level locking for normal operations, but have a maintenance process that opens the database in exclusive-access mode and uses page-level locks for faster bulk operations.

A lot of people are leery of row-level locks with an MDB though, especially across a LAN to a file server. More I/Os to the LDB file, more potential for corrupting the database. This is really the same old drawback to using a "file based database" like Jet MDBs ("Access databases") across a network. But then again, that's the target market for such a database unless we're looking at single-user applications.

<crazed rant>
The hilarious part (to me) here is that people often poo-poo the use of Jet in web applications. There is a tradeoff here of course because if the web app database is on a file server, you have the same performance and corruption issues you have in an Access/VB fat client application. Also, putting the MDB locally on the web server means more local disk I/O. And in general, all the crunching Jet does will eat processor and memory on the web server.

But at a certain point on the performance curve and given enough memory in the web server, using Jet MDBs for web apps will outperform SQL Server unless you have a very fast LAN between the web server and database server. If you have the SQL Server database on the web server itself, Jet will always outperform SQL Server (MySQL, Oracle, and many others) until the database is very large and complex.

FoxPro databases have many of the same advantages, for the same reasons. They can be trim and fast up until a certain point, and many users will never hit that point.
</crazed rant>

So... regarding row-level locking: your mileage may vary. The biggest risk comes from flakey LANs or if you have users who experience machine lockup a lot, hard power-off their boxes, use Task Manager to kill off your client application, etc. Same risks as with page-level locks, just more likely to make database recovery harder (or less successful).

 

[src23]

Hi,

Thank you dilettante for your replies, I read them and I found useful information there.

I apologise I didn't answer sooner, I've been away from the office. For a few days I will be away from the internet, but I'll develop my database and as soon as I return (in 4-5 days) I will tell you what results I had - i'm gonna make some tests with ADO + JET OLEDB / ODBC. Im my previous post, when I said "just Jet", I meant Jet + OLEDB:

    Dim cn As New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & strCONST_database_full_path
        .Open
    End With

The problem with record locking I solved very simple (though I havnet implemented it yet): when a user accesses a record for editing, he sets a flag on it, and the others WON'T be allowed to edit it, will only be able to see it, until that user leaves the record.
They access a record through a modal form, with locked / unlocked controls and buttons to "close & save changes" and "close without saving", or no "save" button.
As a matter of fact, it's very unlikely to access each other records, because everyone of them edit its own loan request.

But there will be a problem with simoultaneous search through large recordsets, I'm working on it right now.

I'm trying to persuade my boss, maybe he will buy a VB license ... maybe I am lucky
I like more VB than Acccess.....


Once again, thank you for answering me, I'll write when I come back.

 

[BenSaunders]

Hello BuggyCode,

I have developed a similar application, where the access db file resides on the server, and client VB Apps connect to it. Initially, I had similar problems to what you describe, but I used a few little techniques to solve it. As with many programming "theories", one person said very different things to others - but this did work. (FYI I generally used the forum on vb2themax.com to pose questions relating to many aspects of this).

The first I decided to do was to ensure that the client application had minimal interaction with the database. I therefore made a connection when the client application was opened, which remained open until the user closes the application. However, that connection was only ever utilised when reading or writing to the database, which generally would take a split second.

For example, I displayed a list of records from a table in a listview control (say 2000 records), these were read in using code - the recordset was opened to read in, and closed when all records read in. This gave the advantage that there was no bind to the database once this was done... any futher database activity is perfomed on an as-needs basis.

Those as-needs activities may include opening a record which is selected from the listview to display the record in a modal form - this would connect to the recordset just to retrieve the record and display it on the screen, and then if the user chose to save it - it would connect to a recordset again just to perform the update.

I must emphasis that never did I use any of the data access toolbox controls to create a "ready-made" data form, rather I used code at all times.

Unfortunately, I don't have the code available at this precise moment.. but will do later on today. If you would like some connection examples, I could gladly provide some.

Let me know

Ben

 

[BenSaunders]

Oh.. Sorry, one other thing if forgot to mention which may be useful, is that a while ago I wrote an ASP 3.0 application which ran on IIS6 and connected to a MS Access database. If you are not familiar with ASP, then it is extremely similar to Visual Basic code in web pages.

For a long time users would occassionaly call me to report that a DB error was occurring and the site was hung. I took me ages to discover the cause of the problem, but I eventually solved it on the Macromedia website (makers of Dreamweaver), where a knowledgebase post reminded users that regardless of whether or not your access database has a password set on it (just a DB password, not workgroups), you should always specify a username and password in your VB connection string. I was not doing this, and did not have a password set anyway, so hadn't bothered. It turned out that this was the source of the problem so when there is no DB password just specify Username=Admin;Password="" in the connection string. The web application has never malfunctioned to this day and has now been continuously running for 11 months. I have since always followed this practice in my Visual Basic Applications, and it has seemed to improve performance.