Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Acsess Backend DB efficiency

As the number of users has increased from 5 to over 20 my client is experiencing slow response time in a DB we developed for them several years ago.

It is split MDB's with a local version of 'Proc.mdb' ( local work tables, forms, reports, macro's etc..) on the local machine and the 'Data.mdb'. on the server.

I have another question posted on EE about moving to SQL Server and that is a definite possibility but will take some time.

In the meantime I want to make sure I am doing everything as efficiently as possible in the current configuration.

I have some specific questions below.  I am looking for feedback on the current setup and changes that may improve response while keeping the current MDB backend.

I have all bound forms set to 'No locks'.  My understanding is that this is the most efficient way, is that correct?

I have some look up routines that open a table using ADO to obtain information from the table (for example: name, address, etc..) based on the record ID passed.  I am enclosing my code below.  Is there a more efficient way of doing this?  Is the ADO connection string the most efficient or are there other options I should use since these table only need to be opened 'read only'?  I am using this connection string:
selectString = "Select * From qryProperty_wAddressInfo Where [ACCT] = " & passedACCT

Open in new window


I am reading that maintaining a current connection to the backend DB at all times should provide a performance improvement?  Is this true?  If yes, I have read about using hidden forms or tables to maintains this connection but not seen any examples.  Could someone provide an example of how they maintain this constant connection.

Any general suggestions to improve performance or response time will be appreciated.

Here is one of my lookup routines:

Public Function GetPropertyInfoFromACCT(passedACCT As Long, _
                                        returnPropertyID As Long, _
                                        returnPropAddr As String, _
                                        returnOwnerName As String, _
                                        returnMailAddr1 As String, _
                                        returnMailAddr2 As String, _
                                        returnMailAddr3 As String, _
                                        returnMailCity As String, _
                                        returnMailState As String, _
                                        returnMailZip As String, _
                                        returnPhoneNum1 As String, _
                                        returnPhoneNum2 As String, _
                                        returnPhoneNum3 As String, _
                                        returnComments As String)
returnPropertyID = 0
returnPropAddr = cNoRecFound
returnOwnerName = ""
returnMailAddr1 = ""
returnMailAddr2 = ""
returnMailAddr3 = ""
returnMailCity = ""
returnMailState = ""
returnMailZip = ""
returnPhoneNum1 = ""
returnPhoneNum2 = ""
returnPhoneNum3 = ""
returnComments = ""
'
'Dim rs As Recordset

selectString = "Select * From qryProperty_wAddressInfo Where [ACCT] = " & passedACCT

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.EOF Then
Else
    rs.MoveLast
    rs.MoveFirst
    If rs.RecordCount > 0 Then
        '
        returnPropertyID = Nz(rs!ID, 0)
        returnPropAddr = Nz(rs!PropAddr1, "")
        returnOwnerName = Nz(rs!OwnerName, "")
        returnMailAddr1 = Nz(rs!MailAddr1, "")
        returnMailAddr2 = Nz(rs!MailAddr2, "")
        returnMailAddr3 = Nz(rs!MailAddr3, "")
        returnMailCity = Nz(rs!MailCity, "")
        returnMailState = Nz(rs!MailState, "")
        returnMailZip = Nz(rs!MailZip, "")
        returnPhoneNum1 = ""
        returnPhoneNum2 = ""
        returnPhoneNum3 = ""
        returnComments = Nz(rs!Comments, "")
        '
    End If
End If
'
rs.Close
Set rs = Nothing
'

End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"While it's not unheard of to have a robust and performant Access db with more than 20 users"

Or even 120 ....  :-)
I read an article about keeping the database connections open a long time ago, I think it was in Access Advisor.  In the article, the author mention how he maintained an open connection to the backend, which looked something like this.  I modified this for a single backend, but if your application has multiple backends, you can modify this relatively simply to store multiple databases and call each by name.  But for a single backend, this is the way I do it.

Put the following in a standard (non-form) code module.  call it when your Splash or Switchboard loads.

Global DBs As New Collection

Public Function GetDB() As DAO.Database

    Dim db As DAO.Database
    
    If DBs.Count = 0 Then
        Set db = CurrentDb
        DBs.Add db, db.Name
    End If
    
    Set GetDB = DBs.Item(1)
    
End Function

Open in new window

 Then, instead of referring to Currentdb or in your application, just use GetDB instead.  Examples follow

Debug.Print Getdb.Name

Getdb.Execute strsql, dbfailonerror

for each tdf in Getdb.Tabledefs

set rs = getdb.openrecordset(strSQL,, dbfailonerror)

The function keeps the connection to the database open in the collection and the collection is dropped somehow, the next call to the function reinstantiates it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or just open a bound form and keep it open. That forces the persistent connection as well.
Yes, the bound Form certainly works. However, with the code approach, you don't need a form and you don't have to have it bound to some table in the BE.

mx
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlcktmguy

ASKER

Thanks for all of the excellent responses and information.  I split the points among responders
I know I closed this and awarded the points I have a follow up on this one.  I want to get into the client site tonight to make some DB revisions but find the lock file (LDB) still exists.  The client claims everyone is out of the system.

I implemented the code for creating a persistent DB connection in fyed's last post, in the new release I put out today.

Global DBConn As New Collection

Public Sub OpenConns()

    Dim dbBE As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    strSQL = "SELECT DISTINCT mSysObjects.Database AS dbPath " _
           & "From mSysObjects " _
           & "WHERE mSysObjects.[DATABASE] Is Not Null " _
           & " AND mSysObjects.Connect Is Null"
    Set rs = GetDB.OpenRecordset(strSQL, , dbFailOnError)
    
    While Not rs.EOF
        Set dbBE = DBEngine.OpenDatabase(rs!dbPath)
        DBConn.Add dbBE, dbBE.Name
        rs.MoveNext
    Wend
    
End Sub
                                            

Open in new window


I assumed that if the users exited the DB, the persistent connection would be lost.  Now I'm wondering if that is true because the LDB is still loaded with users that claim to be out of the application.

Question:  Will the persistent connection cease to connect when the exit the application?
"Will the persistent connection cease to connect when the exit the application?"
It should ... but, if Access did not close properly and is 'stuck' in memory - even if you can't see the Access Application window - then the connection could remain.

"I assumed that if the users exited the DB, "
So refresh my memory ... does each user have a front end (linked to back end) on their desktop ... and that code executes when the db is opened ?

Also, whereas I respect Dale's code, it sees overly complex just to create a persistent connection to a single back end.

Also regarding:

OpenRecordset(strSQL, , dbFailOnError)
I don't think it's correct  to use dbFailOnError because the definition of dbFailOnError is "Rolls back updates if an error occurs."  ... and OpenRecordset does not do any updates.

I would replace that with .OpenRecordset(strSQL, dbOpenDynaset)

mx
Thanks you for the follow up.
it sees overly complex just to create a persistent connection to a single back end.
Agreed. Just open a form or recordset. That will do the trick.
Joe is correct, it is "overly complex to connect to a single backend".  

I pulled that from an app that links to multiple backends.  If you know you are only going to have one BE database, then Joe's code would work just fine.  It does the same thing as mine, but for a single BE.  

My code finds all of the BE files linked to the application and opens a connection to each of them.

I agree that it is likely that someone exited out of the application improperly (either intentionally or unintentionally) which left their "connection" to the BE open.  First test is to attempt do delete the .ldb file.  If someone truly has a connection to the db open, you will not be able to delete the file.
Thanks to all for the additional info.  The client was asking me how the LDB file could be in place when all users claimed to be out.  They were going to read the riot act to any users showing up in the LDB.  I just wanted to make sure under what conditions the lock would persit even when the users exit.  Just yesterday I had added the new persistent locking logic and wondered if that could be the answer.
Regarding that riot act ...

This can also occur if a network connection drops, or if a machine is powered off unintentionally. While I would be willing to bet this is caused by a "5:00 hustle" (i.e. punch the power button on the machine just before you hustle out the door),it's conceivable that other issues could have caused this.

I've also seen the lockfile remain even when I KNOW I've exited an application correctly. Invariably I find a code issue (something didn't shut down correctly, for example) or an install issue (corrupted file/dll, etc).
Micktmguy,

As Scott mentioned, this also occurs if a network connection drops.  Are any of the user connecting via Wifi, rather than over a wired network?
Not sure of the answer to that one but I will check.  Thanks again for the heads up.
As I noted above ... Access does not shut down correctly - and the LDB remains :-)

mlcktmguy ... did you (or client) try to manually delete the LDB file ?  Or just 'observe' that it is still there ?

mx
I just observed that it was there.  The network admin claimed some of the Citrix users had not loggged out properly which caused an issue.  He logged them out and manually deleted the LDB.
I'll certainly try and delete it next time I see it.
Yes ... sometimes it's just an 'orphan' LDB - not really locked by any process.
You might want to consider adding a feature that would allow you (or someone at the client site) to log users off after hours, or when the database needs maintenance.  

There are several ways to do this, but here is a good article from the Microsoft Knowledge base on the subject
Humm ... I thought I posted the Forced Shutdown here ... guess that was in another Q.


Force Shut Down
http://www.peterssoftware.com/fsd.htm

I built an entire Forced Shut Down module from this.  Used each night prior to backup and Compact & Repair of 38 backends on our server.

Peter took everything into account ... and it's free.
Wow, thanks again for all of the input, advice and great suggestions.