Solved

Acsess Backend DB efficiency

Posted on 2014-01-26
24
495 Views
Last Modified: 2014-01-30
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

0
Comment
Question by:mlcktmguy
  • 7
  • 7
  • 5
  • +2
24 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 39810589
I have all bound forms set to 'No locks'.  My understanding is that this is the most efficient way, is that correct?
I'm not sure if setting the form locks will have much of a difference in terms of performance. You can try other settings to see what happens, of course.
I am reading that maintaining a current connection to the backend DB at all times should provide a performance improvement?  Is this true?
Yes, in most cases this will provide some improvement. It's known as a "persistent connection". Just open a form with a recordset and keep it open. I have a "system" form that I use for my apps. I create a Recordsource that returns a small set of data, and keep that form open all the time. The form is hidden, and I use it for various things, like storing "global" variable.
Any general suggestions to improve performance or response time will be appreciated.
In general DAO connections are slightly faster than ADO, at least when working with Access databases. I'm not sure I'd go to the trouble to change things if you're in the process of upsizing to SQL Server, however.

One of the more important things in regard to performance is indexing. In general, your tables should have indexes on all fields used in a JOIN, all used in WHERE clauses or searches, and any fields where you have a high number of distinct values (i.e. a "Gender" column would not be a good candidate).

Unfortunately, the number of users is probably the biggest limiting factor. While it's not unheard of to have a robust and performant Access db with more than 20 users, that seems to be the "wall" for most home-grown apps. I've got a few that work well with more than that, but they are primarily reporting-stype apps (not a lot of data entry, lots of searching and printing reports). They're also on Terminal Server environments, with the FE and BE local to the same machines hosting the TS, so they have no network to traverse.

Personally, I'd say get on the stick with the move to SQL Server. That is usually where you'll find the most performance gains, assuming you take the time to that right.
0
 
LVL 75
ID: 39810614
"While it's not unheard of to have a robust and performant Access db with more than 20 users"

Or even 120 ....  :-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39810787
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.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 125 total points
ID: 39810853
Aka "Persistent Connection'. I can attest that this dramatically improves performance in many situations.

But Dale, how does that code create a persistent connection on the Back End ?

To create a persistent connection to a back end db, all you have to do is this:

If the Declarations Section of the a regular Module put this:

Dim db as dao.Database

Then, have the AutoExec macro call a Function in the same module that does this:

Public Function mSetPersistentConn
    Set db=DBEngine(0).OpenDatabase("SomeFolderPath/SomeDbName")
End Function

This will cause Access to create the LDB file.

mx
0
 
LVL 84
ID: 39810881
Or just open a bound form and keep it open. That forces the persistent connection as well.
0
 
LVL 75
ID: 39811052
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
0
 
LVL 10

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS earned 125 total points
ID: 39812019
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 39812310
@Joe,  My bad, I was doing that from memory and confused a couple of procedures.  I use that as a replacement for Currentdb.  So I don't have to dimension and instantiate a db object throughout my applications.  To open the connections, I use:

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

0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 39814941
Thanks for all of the excellent responses and information.  I split the points among responders
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39816749
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?
0
 
LVL 75
ID: 39816807
"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
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39816910
Thanks you for the follow up.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
ID: 39817283
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39817314
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.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39817692
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.
0
 
LVL 84
ID: 39817750
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).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39818002
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?
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39818028
Not sure of the answer to that one but I will check.  Thanks again for the heads up.
0
 
LVL 75
ID: 39818915
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
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39819072
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.
0
 
LVL 75
ID: 39819094
Yes ... sometimes it's just an 'orphan' LDB - not really locked by any process.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39819338
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
0
 
LVL 75
ID: 39819423
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.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39820836
Wow, thanks again for all of the input, advice and great suggestions.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now