So you are saying I need more indexes?I'm sure Mariam will check indexing, but a very general rule of thumb is an index on any field used to filter (WHERE, HAVING). Then you fine tune from there. It is a balancing act between query performance and overall performance at times. Too many indexes can slow down inserts/updates, ...
Dim DbCon as DAO.Database
Set DbCon = OpenDatabase(FullPathToDbFile)
DbCon.Close
Set DbCon = Nothing
Private Sub Form_Load()
InitializeApp
End Sub
Private Sub Form_Close()
EndApp
End Sub
Private theLinks() As DAO.Database ' a module scope variable
Sub InitializeApp()
' opens connections to all the linked access files to improve performance
' we can find all the linked Access tables in the table MSysObjects
Const ACCESS_LINKS = "SELECT DISTINCT MSysObjects.Database FROM MSysObjects WHERE (((MSysObjects.Database) Is Not Null));"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim linkDb As DAO.Database
Dim nLinks As Long
Set db = CurrentDb
Set rs = db.OpenRecordset(ACCESS_LINKS, dbOpenDynaset)
While Not rs.EOF
Set linkDb = OpenDatabase(rs!Database)
ReDim Preserve theLinks(nLinks)
Set theLinks(nLinks) = linkDb
nLinks = nLinks + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
Sub endApp()
' close all the previously opened Access databse connections
Dim db As DAO.Database
Dim n As Long
For n = LBound(theLinks) To UBound(theLinks)
Set db = theLinks(n)
db.Close
Set db = Nothing
Next n
End Sub
but the users do get a new FE every day.
today will be the 7th day that we have been running only on the physical Citrix server. So far the input I have gotten from the users is the speed is normal, no significant slow down.Based on this comment, I don't think the correct solution was chosen.
Those of you that use Citrix, can I get your feedback? Could it be the Virtual server is the problem. Also I am waiting for Miriam to have time to review this post.