Profiler For Msaccess

I have a MSAccess .mdb file backed with sql server. When i am trying to open a form ,It takes a long time to display I know i had stuff on load event of Form. Is there any way to t

Solution 1:

Faheem; I have never seen such tracking software.

A simple, 'old school' method: Place a msgBox at the end of each function call. Once you have isolated which function is running longer than expected, examine the function. Are your recordsets utilizing indexed fields? Avoid opening multiple recordsets simultaneously. Are you minimizing the use of Loops? Are your loops optimized?

Another thing to check is the use of Access macros to calculate Aggregates (sums, averages). This causes Access to run individual queries repeatedly. If you had a situation where you were dealing with more than 100,000 records and 2 dozen columns, the Access macros would fire 2 dozen times(once for each column).

Using the methods outlined above I have taken a good-sized DB Warehouse app (.25 million records in main Data table, 40 columns) and streamlined reports taking an hour to process down to 5 seconds. The client was very happy.

Solution 2:

I don't really know what you mean by "I know I had stuff on load event of form". Can you not check the macros/VBA attached to your form? What things are you doing on load?

• Do you have multiple subforms within your form? I have experienced long open/load times when using forms with multiple subforms. Especially when the subforms pull a small subset of data from a very large record source.

Check this link out: http://bytes.com/topic/access/answers/204374-timer-function-determining-code-execution-speed

Dim sngStart AsSingleDim sngEnd AsSingleDim sngElapsed AsSingleDim time AsSingle

sngStart = Timer ' Get start time'your code here

sngEnd = Timer 'get stop time
sngElapsed = sngEnd - sngStart

time = Format(sngElapsed, "######0.0000000000")
MsgBox "Time elapsed: " & time, vbInformation, "Time Elapsed"

Solution 3:

OptionCompare Database
OptionExplicitPrivateDeclareFunction timeGetTime _
Lib"winmm.dll" () AsLongPrivate mlngStartTime AsLongPrivateFunction ElapsedTime() AsLong
ElapsedTime = timeGetTime() - mlngStartTime
EndFunctionPrivateSub StartTime()
mlngStartTime = timeGetTime()
EndSubPublicFunction MyTest()

Call StartTime
DoCmd.OpenQuery "Query1"
DoCmd.GoToRecord acDataQuery, "Query1", acLast

Debug.Print ElapsedTime() & _

Call StartTime
DoCmd.OpenQuery "Query2"
DoCmd.GoToRecord acDataQuery, "Query2", acLast

Debug.Print ElapsedTime() & _
EndFunction

See Here

Also, check your form design. Are there a lot of subforms / Master/Child links? How much aggregation is there? Etc.