Splitting a Database to Improve Efficiency

Standard Microsoft Access database interfaces are originally designed to be all encompassing within one file.  This file holds all the tables (data), queries, forms, reports, modules and macros.  This model works very well for small databases that are only going to be used by one person.  However, when the amount of data grows too much, or when it becomes necessary for multiple users to be utilizing the data simultaneously, it is a generally accepted practice to split a database into two files, the front-end (or interface) and the back-end (or data file).

When an Access database is split in two, all of the tables from the original file are exported into a new file.  Standard naming conventions suggest that the new file be named the same as the original file with an added “_be” before the file-type.  For example, if we were working with the database Sample.accdb, we would export the tables into a new file Sample_be.accdb.  The “_be” designates it as a back-end or data file for easy reference later.

This process, in essence, takes all of the data out of the original file and puts it in the new file.  In order to make the original file continue to work properly, it must be linked to the new back-end.  Linking to a back-end file involves creating a connection in the front-end to the back-end that will synchronize all data between the two files.

Microsoft Access contains a built in tool that will accomplish both of these tasks.  In the 2007+ version of Access, the tool is located in the Database Tools ribbon, in the Move Data section, and is labeled as Access Database.  This tool will ask for a new file name and will then proceed to export the tables to the new file and link to those new tables after they have been created.

The disadvantage of this tool is that it will export all the tables within the Access database.  There are times when some tables should not be exported.  For instance, if you have a table that includes information about the interface file, such as when it was last modified and by whom, this may be better kept separate in case there are multiple editions that are created later on.  The process can be done without the automated tool and it only takes slightly longer to do.

The first step of splitting a database manually is to create the new database where the tables will be exported to.  Make sure that you follow the proper naming conventions when creating the new data file, as explained above.

The second step is to export the tables to a new file.  This can be accomplished by selecting all of the tables that you wish to export in the Navigation menu on the left, and then selecting the export tool either by right clicking on one of the tables and selecting Export->Access, or under the External Data ribbon, in the Export section, choosing the Access option.  Here, you will be asked for the data file that you created in Step 1. Once you click Ok, you will be asked whether you want to export the structure and the data or just the structure.  In this case, you want to export the structure and the data.

The third and final step is to create the new links.  In the External Data ribbon, in the Import & Link section, select Access.  In the File name box, select the file that you created in Step 1.  In the options below it, select the second option entitled “Link to the data source by creating a linked table.”  In the popup, select all the tables you want to link to (you will need to select each one individually; using the shift key will not work) or use the Select All button to select all the tables in the file.  You will likely want to select all at this point.  Hit ok once the appropriate tables are selected and the link will be created.

The only disadvantage of this process is that it creates two files that must be moved together if the database is moved from one computer to the other.  Also, since the links are based on the location of the data file, if the data file is moved into another folder, or if it is put in a different location on a new computer, the links will need to be updated to account for the new location.  That, however, will be the next topic of this blog series, so you will need to come back to find out more!

Posted in Uncategorized | Leave a comment

User defined function to return first and last day of previous month

I have a recurring need from database to database to return the first day of last month as well as the last day of last month.  Usually in response to running a batch of update queries or possibly a data range for a  report.

So here’s the technique I’ve implemented:

On the form for running the update queries or the form for running the reports, I add two text box controls and set the format to ‘mm/dd/yy’ and the default value to:

txtBeginMonth, default value: =fncReturnFirstDayLastMonth()

txtEndMonth, default value: =fncReturnLastDayLastMonth()

 

In a new module for the database (not for the particular form), I then create the following two functions:

Function fncReturnFirstDayLastMonth() As Date

    Dim val As Variant
    
    Dim strLastMonthDay As String
   Dim strLastMonthMonth As String
    Dim strLastMonthYear As String
    
    strLastMonthDay = "01"
    strLastMonthYear = Format(Date, "yyyy")
    
    Select Case Format(Date, "mmmm")
    
        Case Is = "January"
            strLastMonthMonth = "12"
            strLastMonthYear = Format(Date, "yyyy") - 1
        Case Is = "February"
            strLastMonthMonth = "01"
        Case Is = "March"
            strLastMonthMonth = "02"
        Case Is = "April"
            strLastMonthMonth = "03"
        Case Is = "May"
            strLastMonthMonth = "04"
        Case Is = "June"
            strLastMonthMonth = "05"
        Case Is = "July"
            strLastMonthMonth = "06"
        Case Is = "August"
            strLastMonthMonth = "07"
        Case Is = "September"
            strLastMonthMonth = "08"
        Case Is = "October"
            strLastMonthMonth = "09"
        Case Is = "November"
            strLastMonthMonth = "10"
        Case Is = "December"
            strLastMonthMonth = "11"
    End Select
    
   val = CDate(strLastMonthMonth & "/" & strLastMonthDay & "/" & strLastMonthYear)
    
    
    
    fncReturnFirstDayLastMonth = val
End Function

Function fncReturnLastDayLastMonth() As Date

    Dim val As Variant
    
    Dim strLastMonthDay As String
    Dim strLastMonthMonth As String
    Dim strLastMonthYear As String
    
    strLastMonthDay = "01"
    strLastMonthYear = Format(Date, "yyyy")
    
    Select Case Format(Date, "mmmm")
    
        Case Is = "January"
            strLastMonthDay = "31"
            strLastMonthMonth = "12"
            strLastMonthYear = Format(Date, "yyyy") - 1
        Case Is = "February"
            strLastMonthDay = "31"
            strLastMonthMonth = "01"
        Case Is = "March"
            strLastMonthDay = "28"
            strLastMonthMonth = "02"
        Case Is = "April"
            strLastMonthDay = "31"
            strLastMonthMonth = "03"
        Case Is = "May"
            strLastMonthDay = "30"
           strLastMonthMonth = "04"
        Case Is = "June"
            strLastMonthDay = "31"
            strLastMonthMonth = "05"
        Case Is = "July"
            strLastMonthDay = "30"
            strLastMonthMonth = "06"
        Case Is = "August"
            strLastMonthDay = "31"
            strLastMonthMonth = "07"
        Case Is = "September"
            strLastMonthDay = "31"
            strLastMonthMonth = "08"
        Case Is = "October"
            strLastMonthDay = "30"
            strLastMonthMonth = "09"
        Case Is = "November"
            strLastMonthDay = "31"
            strLastMonthMonth = "10"
        Case Is = "December"
            strLastMonthDay = "30"
            strLastMonthMonth = "11"

    End Select
    
   val = CDate(strLastMonthMonth & "/" & strLastMonthDay & "/" & strLastMonthYear)
    
    fncReturnLastDayLastMonth = val
End Function

Now, when the user enters the form, the first and last day of last month are presented to the user.  The user may feel free to change those dates prior to running update queries or reports.  But if he/she so chooses, these dates are already prepared and ready to go.

 

 

Posted in Microsoft Access Solutions | Tagged , , , | Leave a comment

Using File System Objects in Microsoft Access

If you want to manipulate files and folders from within Access, here’s how you do it.

First, open a module or create a new one. From the Tools menu, choose References… and the References window opens.

Scroll down to the Microsoft entries and find ‘Microsoft Scripting Runtime’ and you’ll note that it refers to a file ‘scrrun.dll’. Click the box to include this library. Now you have access to file system objects in your code.

File System Object Reference Window

 

From Microsoft:
FileSystemObject was originally created for the Visual Basic Scripting Edition. FileSystemObject is not included in the object library for Visual Basic or Visual Basic for Applications. To use FileSystemObject, you must select the Microsoft Scripting Run-time in the Project References dialog box for your project.

Here’s a pretty simple use of the file system object, once the above reference has been installed. Please note that I heavily relied on the following resource to accomplish this code:
http://technet.microsoft.com/en-us/library/ee198742.aspx

Further note that I’ve created a table named zstbl_Import_Error_Messages for holding error messages that this code might generate. I then open this table for today’s errors if any occurred: If nz(DCount(“ErrorMessage”, “qryImportErrors”)) > 0 Then open query

Further note that I have another table, zstblInformation with a field: TMIDataFolder that holds the default location of files for import and move. The files are imported in the function fncImportTMIData()

 

 

Function fncAutomatedImport()

On Error GoTo AI_err

 

Dim val As Variant

Dim fso As New FileSystemObject

Dim objFolder As folder

Dim objFile As File

Dim colFiles As Variant

Dim folder As folder

Dim path As String

val = False

CurrentDb.Execute “DELETE * FROM zstbl_Import_Error_Messages WHERE ErrorMessageDate=#” & Date & “#”

‘Initialize path.

path = DLookup(“TMIDataFolder”, “zstblInformation”)

‘Get object.

If fso.FolderExists(path) = False Then

MsgBox “TMI Data File Import Folder does not exist.  Please create it: ” & path

Exit Function

ElseIf fso.FolderExists(path & “Backup_TMI_Files\”) Then

MsgBox “TMI Data File Import Backup Folder does not exist.  Please create it: ” & path & “Backup_TMI_Files\”

Exit Function

Else

Set objFolder = fso.GetFolder(path)

End If

Set colFiles = objFolder.Files

For Each objFile In colFiles

Debug.Print ” – fncAutomatedImport: ” & objFile.Name

If fncImportTMIData(path & objFile.Name) = True Then

If fso.FileExists(path & “Backup_TMI_Files\” & objFile.Name) Then

fso.DeleteFile (path & “Backup_TMI_Files\” & objFile.Name)

End If

fso.MoveFile path & objFile.Name, path & “Backup_TMI_Files\”

End If

Next

 

 

If nz(DCount(“ErrorMessage”, “qryImportErrors”)) > 0 Then

DoCmd.OpenQuery “qryImportErrors”

End If

 

val = True

 

AI_exit:

 

fncAutomatedImport = val

Exit Function

AI_err:

CurrentDb.Execute “INSERT INTO zstbl_Import_Error_Messages ( ErrorMessage, ErrorMessageDate, pathandfile ) ” & _

“SELECT ‘Error importing  during fncAutomatedImport’ AS ErrorMessage, #” & Date & “# AS ErrorMessageDate, ‘” & path & objFile.Name & “‘  AS pathandfile”

Resume Next

 

End Function

Posted in Access 2007 problems/solutions | Leave a comment

Using a subtable for criteria to a main table report

Here’s the scenario:

You have 2 related tables:  Projects and Machinery.  You wish to see a listing of projects where the location of the Machinery is in China.  Yet there are multiple machinery items in multiple locations for Projects, and you only wish to see a unique listing of projects.

For instance, in the example below, there are two pieces of machinery for ‘Project A’.  MachineryID 1 is in China and MachineryID 2 is in Africa.  Both of these are related to ‘Project A’.

The situation:

DCPost715

The solution:

Use the ‘In’ Clause to create a subquery in the filter for the report.  This sounds complex but is actually quite simple once you get the syntax correct of the In Clause.  Here’s how I did this:

strFilter = strFilter & ” AND ProjectID In (SELECT ProjectID FROM tbl_Machinery WHERE MachineryLocation = ‘” & cboMachineryLocation & “‘)”

Here’s the complete code for the preview button above.

Private Sub cmdPreview_Click()

Dim strFilter As String

If Not IsNull(cboProjectType) Then

strFilter = ” AND ProjectType = ‘” & cboProjectType & “‘”

End If

If Not IsNull(cboMachineryLocation) Then

strFilter = strFilter & ” AND ProjectID In (SELECT ProjectID FROM tbl_Machinery WHERE MachineryLocation = ‘” & cboMachineryLocation & “‘)”

End If

If Len(strFilter) > 5 Then

strFilter = Right(strFilter, Len(strFilter) – 5)

End If

DoCmd.OpenReport “rpt10Project Listing”, acViewPreview, , strFilter

End Sub

Posted in Uncategorized | Leave a comment

Sending list box results to a temporary table for reporting

Situation:  You’ve got a list box of surgeons.  You want to run a report and as a criteria to the report, you want to filter by the surgeons selected in the list box.  How do you do this?

Here’s the list box of surgeons with a couple selected:

lstSurgeon

lstSurgeon

Note:  multi select property = ‘simple’ to allow multiple selections

So, in code, before running the report we send the surgeon names to a temporary table:

‘send surgeons to temp table
Dim I as integer
i = 0
CurrentDb.Execute “delete * from trpttmp_Surgeons”
For i = 0 To Me.lstSurgeon.ListCount – 1
If Me.lstSurgeon.Selected(i) Then
Debug.Print Me.lstSurgeon.Column(0, i)
CurrentDb.Execute “INSERT INTO trpttmp_Surgeons ( Surgeon ) SELECT ” & Me.lstSurgeon.Column(0, i) & ” AS Surgeon”
End If
Next i

DoCmd.OpenReport “rpt10Surgery Scores”, acViewPreview, , “txtSurgeons IN (SELECT [surgeon] FROM trpttmp_Surgeons)”

This results in the following table being cleared and filled:

trpttmp_Surgeons

Note the use of the IN Clause in the openreport command.  If you don’t understand the use of the IN clause, go here:

http://office.microsoft.com/en-us/access-help/in-operator-HP001032243.aspx?CTT=5&origin=HP001032287

It’s not the greatest explanation, though…

 

Posted in Uncategorized | Leave a comment

Complex Reporting

(and building a source form to understand how the report results are derived)

Sometimes you need to build a really complex report. I had to do that for one of my Fortune 500 clients. They were trying to automate a 20 hour/mo excel process. The final result was a push of a button solution.

 

But now came the really tough part. Did they believe the results of the report? Well, I can almost guarantee you that each month there are a few numbers in the report that they don’t agree with. I can further guarantee you that the report is correct and that their numbers are wrong. What’s the cause of the discrepancy? Could be a billion different reasons and indeed that’s the case. It’s almost always something to do with the data they are receiving from one of their vendors to create the report.

So what do you do when you’ve built a super complex report and constantly have to justify your numbers? Well, here’s what I did.

  1. Break your reporting process into a series of staging table.
  2. The create a form for checking each staging table for accuracy.
  3. For the queries that build and massage/scrub the queries, add buttons to the form that opens these queries in design view
  4. Label your process as necessary to provide guidance on what the heck is going on
  5. Flow chart the entire process to make it easy to understand from a bird’s eye view

Here’s an example of the above.

Here is how the report is built at the touch of a button:

The client opens a report form, supplies a desired month and year, and chooses to preview the report.

At this screen, before running the report, they can make changes to the report data or review elements of the report prior to running (the 3 buttons on the left, one of which opens the above form)

Here’s the code that runs in the background:

MsgBox “Reminder: Enter SMWE Information first”

‘GoTo skip0

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘Medical

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CurrentDb.Execute “delete * from SSN_Detail”

CurrentDb.Execute “delete * from trpt_CIGNA_Med_Headcount”

CurrentDb.Execute “delete * from trpt_CIGNA_Med_Headcount_Retiree_Only”

CurrentDb.Execute “delete * from trpt_CIGNA_Med_Headcount_Non_Retiree_Only”

 

CurrentDb.Execute “qryMk_SSN_Detail”

‘update and remove duplicates from th eSSN_Detail table – this accounts for changes in status and tier

CurrentDb.Execute “DeleteDupStatusQueryfromSSN_DETAIL”

CurrentDb.Execute “DeleteDupTierChgQueryfromSSN_DETAIL”

 

CurrentDb.Execute “qapp_CIGNA_Med_Headcount”

CurrentDb.Execute “UPDATE trpt_CIGNA_Med_Headcount INNER JOIN tlkp_CIGNA_Plans ON (trpt_CIGNA_Med_Headcount.stat_c_nm_s = tlkp_CIGNA_Plans.stat_c_nm_s) AND (trpt_CIGNA_Med_Headcount.comm_x = tlkp_CIGNA_Plans.comm_x) ” & _

“SET trpt_CIGNA_Med_Headcount.ROPlan = ‘RO Only’ ” & _

“WHERE RetireeOnly=True and InsuranceCompany = ‘CIGNA'”

‘desire to aggregate the USTs execept for UST – IWSE

CurrentDb.Execute “UPDATE trpt_CIGNA_Med_Headcount SET trpt_CIGNA_Med_Headcount.Company = ‘UST’ ” & _

“WHERE Company Like ‘*ust*’ AND Company<>’UST – IWSE'”

 

CurrentDb.Execute “qapp_CIGNA_Med_Headcount_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Med_Headcount_Non_Retiree_Only”

skip0:

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘Dental

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CurrentDb.Execute “delete * from tblHC_Dental”

 

CurrentDb.Execute “qryMK_HC_Dental”

‘update and remove duplicates from the HC_Dental table

‘this accounts for changes in status and tier

CurrentDb.Execute “qryHC_Dental_Del_Status”

CurrentDb.Execute “qryHC_Dental_Del_Tier”

 

‘aggregate UST companies

CurrentDb.Execute “delete * from tblHC_Dental_Agg_UST”

CurrentDb.Execute “INSERT INTO tblHC_Dental_Agg_UST ( Company ) ” & _

“SELECT tblHC_Dental.*, tblOrgCodes.Company ” & _

“FROM tblHC_Dental INNER JOIN tblOrgCodes ON tblHC_Dental.jborgcd_c = tblOrgCodes.Org_Code”

CurrentDb.Execute “UPDATE tblHC_Dental_Agg_UST SET tblHC_Dental_Agg_UST.Company = ‘UST’ ” & _

“WHERE Company Like ‘*ust*’ AND Company<>’UST – IWSE'”

 

‘bring in ROPlan info and create staging table

CurrentDb.Execute “delete * from trpt_CIGNA_Dental_Retiree_Only”

CurrentDb.Execute “delete * from trpt_CIGNA_Dental_Non_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Dental_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Dental_Non_Retiree_Only”

 

‘GoTo skip1:

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘Vision

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

CurrentDb.Execute “delete * from tblHC_Vision”

CurrentDb.Execute “qryMK_HC_Vision”

‘update and remove duplicates from the HC_Vision table

‘this accounts for changes in status and tier

CurrentDb.Execute “qryHC_Vision_Del_Status”

CurrentDb.Execute “qryHC_Vision_Del_Tier”

 

‘aggregate UST companies

CurrentDb.Execute “delete * from tblHC_Vision_Agg_UST”

CurrentDb.Execute “INSERT INTO tblHC_Vision_Agg_UST ( Company ) ” & _

“SELECT tblHC_Vision.*, tblOrgCodes.Company ” & _

“FROM tblHC_Vision INNER JOIN tblOrgCodes ON tblHC_Vision.jborgcd_c = tblOrgCodes.Org_Code”

CurrentDb.Execute “UPDATE tblHC_Vision_Agg_UST SET tblHC_Vision_Agg_UST.Company = ‘UST’ ” & _

“WHERE Company Like ‘*ust*’ AND Company<>’UST – IWSE'”

 

‘bring in ROPlan info and create staging table

CurrentDb.Execute “delete * from trpt_CIGNA_Vision_Retiree_Only”

CurrentDb.Execute “delete * from trpt_CIGNA_Vision_Non_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Vision_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Vision_Non_Retiree_Only”

 

 

 

 

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘FSA

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CurrentDb.Execute “delete * from tblHC_FSA_Agg_UST”

CurrentDb.Execute “qapp_CIGNA_FSA”

 

CurrentDb.Execute “UPDATE tblHC_FSA_Agg_UST SET Company = ‘UST’ ” & _

“WHERE Company Like ‘*UST*’ AND Company<>’UST – IWSE'”

 

 

CurrentDb.Execute “delete * from trpt_CIGNA_FSA_agg”

CurrentDb.Execute “qapp_CIGNA_FSA_agg”

 

CurrentDb.Execute “delete * from trpt_CIGNA_FSA_agg_Crosstab”

CurrentDb.Execute “qapp_CIGNA_FSA_agg_Crosstab_app”

 

skip1:

‘build the report

 

CurrentDb.Execute “delete * from trpt_CIGNA”

 

‘append the line items from the Benefit Options and Rates table

CurrentDb.Execute “INSERT INTO trpt_CIGNA ” & _

“SELECT tlkp_CIGNA_Benefit_Options_And_Rates.* ” & _

“FROM tlkp_CIGNA_Benefit_Options_And_Rates ” & _

“WHERE tlkp_CIGNA_Benefit_Options_And_Rates.Order Is Not Null”

 

‘aggregate the UST info

CurrentDb.Execute “UPDATE trpt_CIGNA SET Company = ‘UST’ ” & _

“WHERE (((trpt_CIGNA.Company) Like ‘*UST*’) AND ((trpt_CIGNA.Company)<>’UST – IWSE’))”

 

 

 

‘now update all of the headcount values and perform calculations

‘Medical

CurrentDb.Execute “qupd_CIGNA_Med_Non_Retiree”

CurrentDb.Execute “qupd_CIGNA_Dental_Non_Retiree”

CurrentDb.Execute “qupd_CIGNA_Vision_Non_Retiree”

CurrentDb.Execute “qupd_CIGNA_FSA_Non_Retiree”

 

CurrentDb.Execute “qupd_CIGNA_Med_Retiree”

CurrentDb.Execute “qupd_CIGNA_Dental_Retiree”

CurrentDb.Execute “qupd_CIGNA_Vision_Retiree”

 

‘append SMWE data

CurrentDb.Execute “INSERT INTO trpt_CIGNA SELECT trpt_CIGNA_SMWE.* FROM trpt_CIGNA_SMWE”

 

 

DoCmd.OpenReport Me.OpenArgs, acViewPreview

 

Here’s a page from the actual report:

 

Posted in Access 2007 problems/solutions | Leave a comment

Specific Port in Connection String to SQL

When creating a project that will use SQL Server as the back end, the default port used is 1433. You need to have your firewall and router forwarding incoming requests on that port. However, if you need to use a different port, you need to specify it in your string. The way you do that is in the IP setting. Where you would type IP=99.999.999.99 for a specific port you type IP=99.999.999.99,5000. The comma after the IP address separates the IP from the Port assignment. The port can be assigned after the comma.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Leave a comment

Access linked to SQL

Be sure to set a default value for bit fields in SQL. Access does not like it when you do not. Access will place a default value into the fields if they do not have one and as a result, all the records will be locked. The value that Access loads is not posted back to the SQL Server so Access has changed all the records but the records are still dirty. So all records will be locked. No updates can take place. The only solution if you do get to this point is to delete the problem field from SQL Server entirely. Later, after you see that Access has unlocked the records, you can return to SQL Server and add the field along with a default value.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , | Leave a comment

64 Bit

Be aware that 64 bit operating systems along with 64 bit MS Access could spell trouble for your code. It will spell trouble for any libraries that you may hav e obtained along the way. There is a nearly total absence in the market of 64 bit versions of libraries. I would warn clients ahead of time if you can. Look through your client list and those that you know have unique libraries for whatever reason, contact them and let them know they will lose that function if they make the upgrade. The idea of 64 bit is wonderful. The reality of it is harsh.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , , , | Leave a comment

Improve Db Performance in a Network Environ

Installing the following code into the first form that opens in a database will increase the database performance in a network environment, especially with Windows 7.

Option Compare Database
Â
Public Function PrintISAMStats()
    ‘ Print the values returned by the ISAMStats function
    ‘ to the Debug window.
  Â
    Debug.Print “Number of disk reads: “, DBEngine.ISAMStats(0)
    Debug.Print “Number of disk writes: “, DBEngine.ISAMStats(1)
    Debug.Print “Number of reads from cache: “, DBEngine.ISAMStats(2)
    Debug.Print “Number of reads from read-ahead cache: “, DBEngine.ISAMStats(3)
    Debug.Print “Number of locks placed: “, DBEngine.ISAMStats(4)
    Debug.Print “Number of release lock calls: “, DBEngine.ISAMStats(5)
    Debug.Print
End Function
Â
Public Function ResetISAMStats()
    ‘ This procedure resets the values returned by the
    ‘ ISAMStats function to zero.
  Â
    Dim intI As Integer
    ‘ Reset each value.
    For intI = 0 To 5
        DBEngine.ISAMStats intI, True
    Next
End Function
Â
Public Function UpdateISAM()
    DBEngine.SetOption dbMaxBufferSize, 50000
    DBEngine.SetOption dbMaxLocksPerFile, 500000
    ‘DBEngine.SetOption dbFlushTransactionTimeout, 5000
  Â
End Function
Â
Public Function runtest()
‘UpdateISAM
ResetISAMStats
Dim curtime As Date
curtime = Now
Â
DoCmd.OpenForm “frmprc10claims”
PrintISAMStats
Â
Debug.Print “Number of seconds:” & DateDiff(“s”, curtime, Now())
Â
Â
End Function

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , , | Leave a comment