mlcktmguy
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:
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:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
Then, instead of referring to Currentdb or in your application, just use GetDB instead. Examples followDebug.Print Getdb.Name
Getdb.Execute strsql, dbfailonerror
for each tdf in Getdb.Tabledefs
set rs = getdb.openrecordset(strSQL
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all of the excellent responses and information. I split the points among responders
ASKER
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.
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?
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
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
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
ASKER
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.
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.
ASKER
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).
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?
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?
ASKER
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
mlcktmguy ... did you (or client) try to manually delete the LDB file ? Or just 'observe' that it is still there ?
mx
ASKER
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.
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
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.
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.
ASKER
Wow, thanks again for all of the input, advice and great suggestions.
Or even 120 .... :-)