Finding data holes and filling them in Microsoft Access

Finding data holes and filling them in Microsoft Access

So you are running 20 lines of production and you have 3 shifts a day. But some times you don’t run a shift due to a variety of reasons. You decide that during these missed shifts you want to record a shift that SHOULD have run and give that shift a downtime code. You want to use ‘No Demand’ as your downtime code which means that there was no reason to run on that day on that production line during that shift.
By having this ‘No Demand’ data added to your production run data, you’ll be able to more accurately track your efficiency, downtime, and more.

So how do you identify where these holes are in your data and how do you fill these holes? You’ve got 20 machines times 3 shifts a day. That’s 60 potential holes to fill.

Here’s how I approached. Note that the two currentdb.execute “…SQL Code Here…” statements (at the end of the procedure) are creating records. One in the production table and one in a tsub downtime table. Call me at 804 928 4111 with any questions if you want to get this code working for you.
Create a table of all possible production runs for a given period of time:

Historical Shifts Table

 

In code I create this table with a currentdb.execute SQL statement. I put this inside of a For/Next loop that is defined by the most recent date in the table up until today. Therefore I will only have to add the most recent days everytime I run this code.

dtmLastDate = DMax(“ProductionDate”, “tbl_Historical_Shifts”)
iOutStandingDays = Date – dtmLastDate

If iOutStandingDays = 0 Then Exit Sub

CurrentDb.Execute “delete * from tbl_Historical_Shifts”

For i = 1 To iOutStandingDays

CurrentDb.Execute “INSERT INTO tbl_Historical_Shifts ( lineid, ShiftNumber, ShiftOccured, Productiondate ) ” & _
“SELECT tlkp_Lines.lineid, tlkp_Shifts.ShiftNumber, 0 AS ShiftOccured, #” & dtmLastDate + i & “# AS Productiondate ” & _
“FROM tlkp_Lines, tlkp_Shifts”

Next

Note above that I assume the ‘ShiftOccured’ field is false. Therefore I assume that none of the shifts actually ran.

I then run an update query comparing this historical table to the actual production table:

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘fill in whether or not a production record exists
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
CurrentDb.Execute “UPDATE tbl_Historical_Shifts INNER JOIN tbl_Daily_Production ON (tbl_Historical_Shifts.ProductionDate = tbl_Daily_Production.productiondate) AND (tbl_Historical_Shifts.lineid = tbl_Daily_Production.lineid) AND (tbl_Historical_Shifts.ShiftNumber = tbl_Daily_Production.productionshift) SET tbl_Historical_Shifts.ShiftOccured = True”
Which looks like this in design mode:

Design view of sql

At this point, if you reviewed the historical table for 3/14/17, you would find that a few of the shifts did not occur:

Historical Shifts Table

For example the production line BBHAY31 on shift 3 and the BBTUB on shift 2.
So now I create a recordset based on the above table and scroll through that recordset identifying the ‘0’ values in ShiftOccured.
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘ scroll through the shifts where no production record exists. Build a production record with no demand
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
Set rs = CurrentDb.OpenRecordset(“select * from qry_Missing_Shifts WHERE productiondate >=#” & dtmLastDate & “#”)

Do Until rs.EOF
‘build a production record
CurrentDb.Execute “INSERT INTO tbl_Daily_Production ( productiondate, productionshift, starttimeofrun, total_hours ) ” & _
“SELECT #” & rs!ProductionDate & “# AS productiondate, 0 AS productionshift, #12/30/1899 1:0:0# AS starttimeofrun, 8 AS total_hours FROM tbl_Daily_Production”
lngNextProductionID = DMax(“lineid”, “tbl_daily_production”)

‘build a no demand downtime record
‘CurrentDb.Execute “INSERT INTO tsub_Production_Downtime ( productionid, dtcode, explanation, hour1minutes, hour2minutes, hour3minutes, hour4minutes, hour5minutes, hour6minutes, hour7minutes, hour8minutes, totalminutes ) ” & _
“SELECT ” & lngNextProductionID & ” AS productionid, No Demand’ AS dtcode, ‘automated’ AS explanation, 60 AS hour1minutes, 60 AS hour2minutes, 60 AS hour3minutes, 60 AS hour4minutes, 60 AS hour5minutes, 60 AS hour6minutes, 60 AS hour7minutes, 60 AS hour8minutes, 480 AS totalminutes”
rs.MoveNext
Loop

Here’s the entire sub procedure. If you wanted to run this sub procedure, simply create a button on a form (or tie the sub procedure to a form’s on load event if you’d prefer) and call as such:

Creating Button

Private Sub cmdFillHoles_Click()

subCreateNoDemandRecords

End Sub

I then put this sub procedure (subCreateNoDemandRecords) in a global module so it may be called from anywhere in the database including this new button.

Sub subCreateNoDemandRecords()
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘ Create No Demand Records Where necessary
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
Dim dtmLastDate As Date
Dim i As Integer
Dim iOutStandingDays As Integer
Dim rs As Recordset
Dim lngNextProductionID As Long

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘build a list of all potential days/shifts since the last time this was done
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
dtmLastDate = DMax(“ProductionDate”, “tbl_Historical_Shifts”)
iOutStandingDays = Date – dtmLastDate

If iOutStandingDays = 0 Then Exit Sub

CurrentDb.Execute “delete * from tbl_Historical_Shifts”

For i = 1 To iOutStandingDays

CurrentDb.Execute “INSERT INTO tbl_Historical_Shifts ( lineid, ShiftNumber, ShiftOccured, Productiondate ) ” & _
“SELECT tlkp_Lines.lineid, tlkp_Shifts.ShiftNumber, 0 AS ShiftOccured, #” & dtmLastDate + i & “# AS Productiondate ” & _
“FROM tlkp_Lines, tlkp_Shifts”

Next
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘fill in whether or not a production record exists
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
CurrentDb.Execute “UPDATE tbl_Historical_Shifts INNER JOIN tbl_Daily_Production ON (tbl_Historical_Shifts.ProductionDate = tbl_Daily_Production.productiondate) AND (tbl_Historical_Shifts.lineid = tbl_Daily_Production.lineid) AND (tbl_Historical_Shifts.ShiftNumber = tbl_Daily_Production.productionshift) SET tbl_Historical_Shifts.ShiftOccured = True”

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘ scroll through the shifts where no production record exists. Build a production record with no demand
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
Set rs = CurrentDb.OpenRecordset(“select * from qry_Missing_Shifts WHERE productiondate >=#” & dtmLastDate & “#”)

Do Until rs.EOF
‘build a production record
CurrentDb.Execute “INSERT INTO tbl_Daily_Production ( productiondate, productionshift, starttimeofrun, total_hours ) ” & _
“SELECT #” & rs!ProductionDate & “# AS productiondate, 0 AS productionshift, #12/30/1899 1:0:0# AS starttimeofrun, 8 AS total_hours FROM tbl_Daily_Production”
lngNextProductionID = DMax(“lineid”, “tbl_daily_production”)

‘build a no demand downtime record
‘CurrentDb.Execute “INSERT INTO tsub_Production_Downtime ( productionid, dtcode, explanation, hour1minutes, hour2minutes, hour3minutes, hour4minutes, hour5minutes, hour6minutes, hour7minutes, hour8minutes, totalminutes ) ” & _
“SELECT ” & lngNextProductionID & ” AS productionid, No Demand’ AS dtcode, ‘automated’ AS explanation, 60 AS hour1minutes, 60 AS hour2minutes, 60 AS hour3minutes, 60 AS hour4minutes, 60 AS hour5minutes, 60 AS hour6minutes, 60 AS hour7minutes, 60 AS hour8minutes, 480 AS totalminutes”
rs.MoveNext
Loop

MsgBox “Done!”

End Sub

Posted in Access Code Examples, Manufacturing Access Database, Microsoft Access Solutions | Tagged , , | Comments Off on Finding data holes and filling them in Microsoft Access

Calculating travel time between two locations

You need a way to automatically calculate the time to get from one location to another location. You might use this for giving your drivers directions to your clients. Or expand to be an efficient route because you are going to check all the distances between multiple points and go to the close one next, then the next most closest one.

So create a table with 2 columns.  StartLocation, DestinationLocation.  Then create a query based on this table and bring down both columns.  Then create a third column like this: TravelTime:getGoogleTravelTime(StartLocation, DestinationLocation)

Create a module and paste in the following code.  Note that you should not need to set any library references.

That’s it!

Option Compare Database

 

Const strUnits = “imperial” ‘ imperial/metric (miles/km)

‘You need a way to automatically calculate the time to get from one location to another location.  You ‘might use this for giving your drivers directions to your clients.  Or expand to be an efficient route ‘because you are going to check all the distances between multiple points and go to the close one next, ‘then the next most closest one.

 

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””

‘ Author of this module: desmond oshiwambo

‘ original reference found here: https://desmondoshiwambo.wordpress.com/2013/06/20/how-to-get-google-travel-time-and-distance-in-vba/

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

 

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

‘ Example of calling function:  ?getGoogleTravelTime(“rolling creek place, glen allen,va 23059″,”bar harbor maine”)

’12:51

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

 

 

 

Function CleanHTML(ByVal strHTML)

 

    ‘Helper function to clean HTML instructions

    Dim strInstrArr1() As String

    Dim strInstrArr2() As String

    Dim s As Integer

    

        strInstrArr1 = Split(strHTML, “<“)

        For s = LBound(strInstrArr1) To UBound(strInstrArr1)

        strInstrArr2 = Split(strInstrArr1(s), “>”)

       If UBound(strInstrArr2) > 0 Then

            strInstrArr1(s) = strInstrArr2(1)

       Else

            strInstrArr1(s) = strInstrArr2(0)

       End If

    Next

     

    CleanHTML = Join(strInstrArr1)

   

End Function

 

 

Public Function formatGoogleTime(ByVal lngSeconds As Double)

‘Helper function. Google returns the time in seconds, so this converts it into time format hh:mm

 

Dim lngMinutes As Long

Dim lngHours As Long

 

lngMinutes = Fix(lngSeconds / 60)

lngHours = Fix(lngMinutes / 60)

lngMinutes = lngMinutes – (lngHours * 60)

 

formatGoogleTime = Format(lngHours, “00”) & “:” & Format(lngMinutes, “00”)

End Function

 

 

Function gglDirectionsResponse(ByVal strStartLocation, ByVal strEndLocation, ByRef strTravelTime, ByRef strDistance, ByRef strInstructions, Optional ByRef strError = “”) As Boolean

On Error GoTo errorHandler

‘ Helper function to request and process XML generated by Google Maps.

 

Dim strURL As String

Dim objXMLHttp As Object

Dim objDOMDocument As Object

Dim nodeRoute As Object

Dim lngDistance As Long

 

Set objXMLHttp = CreateObject(“MSXML2.XMLHTTP”)

Set objDOMDocument = CreateObject(“MSXML2.DOMDocument.6.0”)

 

strStartLocation = Replace(strStartLocation, ” “, “+”)

strEndLocation = Replace(strEndLocation, ” “, “+”)

 

strURL = “http://maps.googleapis.com/maps/api/directions/xml” & _

            “?origin=” & strStartLocation & _

            “&destination=” & strEndLocation & _

            “&sensor=false” & _

            “&units=” & strUnits   ‘Sensor field is required by google and indicates whether a Geo-sensor is being used by the device making the request

 

‘Send XML request

With objXMLHttp

    .Open “GET”, strURL, False

    .setRequestHeader “Content-Type”, “application/x-www-form-URLEncoded”

    .send

    objDOMDocument.loadXML .responseText

End With

 

With objDOMDocument

    If .selectSingleNode(“//status”).Text = “OK” Then

        ‘Get Distance

        lngDistance = .selectSingleNode(“/DirectionsResponse/route/leg/distance/value”).Text ‘ Retrieves distance in meters

        Select Case strUnits

            Case “imperial”: strDistance = Round(lngDistance * 0.00062137, 1)  ‘Convert meters to miles

            Case “metric”: strDistance = Round(lngDistance / 1000, 1) ‘Convert meters to miles

        End Select

        

        ‘Get Travel Time

        strTravelTime = .selectSingleNode(“/DirectionsResponse/route/leg/duration/value”).Text  ‘returns in seconds from google

        strTravelTime = formatGoogleTime(strTravelTime) ‘converts seconds to hh:mm

        

        ‘Get Directions

        For Each nodeRoute In .selectSingleNode(“//route/leg”).childNodes

            If nodeRoute.baseName = “step” Then

                strInstructions = strInstructions & nodeRoute.selectSingleNode(“html_instructions”).Text & ” – ” & nodeRoute.selectSingleNode(“distance/text”).Text & vbCrLf

            End If

        Next

        

        strInstructions = CleanHTML(strInstructions) ‘Removes MetaTag information from HTML result to convert to plain text.

        

    Else

        strError = .selectSingleNode(“//status”).Text

        GoTo errorHandler

    End If

End With

 

gglDirectionsResponse = True

GoTo CleanExit

 

errorHandler:

    If strError = “” Then strError = Err.Description

    strDistance = -1

    strTravelTime = “00:00”

    strInstructions = “”

    gglDirectionsResponse = False

 

CleanExit:

    Set objDOMDocument = Nothing

    Set objXMLHttp = Nothing

 

End Function

 

 

Function getGoogleTravelTime(ByVal strFrom, ByVal strTo) As String

‘Returns the journey time between strFrom and strTo

 

Dim strTravelTime As String

Dim strDistance As String

Dim strInstructions As String

Dim strError As String

 

If gglDirectionsResponse(strFrom, strTo, strTravelTime, strDistance, strInstructions, strError) Then

    getGoogleTravelTime = strTravelTime

Else

    getGoogleTravelTime = strError

End If

 

End Function

 

 

Function getGoogleDistance(ByVal strFrom, ByVal strTo) As String

‘Returns the distance between strFrom and strTo

‘where strFrom/To are address search terms recognisable by Google

‘i.e. Postcode, address etc.

 

Dim strTravelTime As String

Dim strDistance As String

Dim strError As String

Dim strInstructions As String

 

If gglDirectionsResponse(strFrom, strTo, strTravelTime, strDistance, strInstructions, strError) Then

    getGoogleDistance = strDistance

Else

    getGoogleDistance = strError

End If

 

End Function

 

 

Function getGoogleDirections(ByVal strFrom, ByVal strTo) As String

‘Returns the directions between strFrom and strTo

‘where strFrom/To are address search terms recognisable by Google

‘i.e. Postcode, address etc.

 

Dim strTravelTime As String

Dim strDistance As String

Dim strError As String

Dim strInstructions As String

 

If gglDirectionsResponse(strFrom, strTo, strTravelTime, strDistance, strInstructions, strError) Then

    getGoogleDirections = strInstructions

Else

    getGoogleDirections = strError

End If

 

End Function

 

 

 

Posted in Uncategorized | Tagged , , | Comments Off on Calculating travel time between two locations

Beginning a new Database

I’m about to begin the development of a database for the Navy (Military Naval Base Client in Maryland). The purpose of the database is to track inventory on the military base.

I’ve already had interviews with the client and gotten a statement of work.

So where to begin?

Microsoft Access Database: Using a database template

Well my first step is to take a database template I’ve developed for myself and copy/paste it into a ‘navy’ folder on my C: drive. This gets me the typical navigation most of my databases use (a main menu), a report criteria screen, a split database with relinking features, and an administration screen for managing users and their permissions.  Here’s the Main Menu I often use that’s in my template:

main-menu-example

If you’d like a copy of this template, contact me at jjaeger@datacontrolllc.com

I realize this is sort of ‘cheating’ because you probably don’t have a similar template to start with. So then what’s the next step?

I then make a list of the objects the user desires. The forms and the reports. Also the import and export processes they desire.

For this Inventory database for instance, the user desires barcode scanners as inputs in addition to typical data entry. They also want barcode labels as outputs.

Microsoft Access Database Scoping a Project
Here’s a list of the primary objects this client desires:

Repository for documents and drawings that can be linked to items
·         Work Orders
·         PMI’s
·         Assets
§  Purchase Order’s
§  Purchase card
§  Service orders
§  Receiving
§  Parts Inventory
·         Asset List/Repairs
·         Facilities Daily Log
·         Facilities Equipment
·         Test Equipment
·         Keys Log
·         Required Documents
·         System Administration
·         Flexible reporting
–          Bar code scanning capability (vendor suggested scanner)
–          Automatic email notification, using MS Outlook, ie. sending work orders to technicians

Microsoft Access database task list

From the above list of objects I’ve created a task list. I’ll then use the task list throughout the project to communicate with my client.

navy-inventory-database

Microsoft Access Database table creation process
From the list of objects above I then begin cranking out data entry forms. The first step is to build a table that lists all of the necessary forms. Please note that in my discussions with the client to scope the project and get to the statement of work, we were mocking up each of the above objects. In this particular project, they mostly handed me hard copies of what they were currently doing, such as work orders.

Microsoft Access Database Form Creation Process
Most of the data entry forms will be single form view with record filtering/finding at the tops of the forms. Most of the drop down lists will lead to lookup tables and maintenance forms for same. During this process, I need to make a decision on whether or not to use an ‘ID’ field for the maintenance table and then have the processing form/table I’m building use the ID field as well. Or I can simply list the values in the lookup table without an ID and the processing form/table will use the same name of the field.

Next Steps

So the exercises above will lead me to a rough draft of all fields/tables/processing forms/maintenance forms.  I will usually have uncovered a number of issues that I need to bring to the attention of my client after these steps.  In my next blog I’ll go into more detail on some of the form creation issues I typically run into.

Posted in Access Code Examples, Inventory Database, Microsoft Access Solutions, Military Database, Navy Database, Uncategorized | Tagged , , , | Comments Off on Beginning a new Database

Manpower resource scheduling and communication with an Access Database for commercial contractors

I recently wrapped up database development for a commercial mason, Masonomics, here in Richmond, VA.  Masonomics is a large masonry outfit that works on a variety of government, educational, and commercial projects across Virginia.   They regularly run 10 or more projects simultaneously and employ 300 folks on those projects.

Figuring out where manpower resources should be tomorrow is a big deal to these guys.  Maybe it’s a big deal to your organization too?

So we built an Access database that tracks current projects and foremen and the desired manpower resources for those projects.  We store and manage the cell phone numbers for the foremen and the employees/subcontractors that Masonomics employs.  We use those cell numbers to send texts to the employees letting them know where to be the next days.

At the end of the day, around 5:00PM, it is a superintendent’s responsibility to look at all the projects he/she is responsible for and manage the manpower resources for those commercial masonry projects throughout Virginia.  That may mean moving masons, laborers, construction machinery operators, etc from one job to another.  This Access database we developed does that.

When the project superintendent moves a manpower resource from one Virginia masonry project to another project, the manpower resource (employee) being moved receives a text message for his assignment tomorrow.  Furthermore the foremen for both the sending and receiving project receive text messages making them aware of the move.

This saves Masonomics management an immense number of evening work hours they used to spending making phone calls.  You can visualize all of these employees across Virginia instantly knowing where they should report tomorrow without 300 phone calls having to be made!

Lastly, every day the entire company is updated on whom is to be at which Virginia construction project.  In the morning, the foremen for the projects are responsible for logging into their mobile phone web page where they see their currently assigned resources.  They mark the necessary resources as (U) Unexcused Absent, (E) Excused Absent, and (T) Tardy.  That information then updates the Access Database (through a SQL Server/Web connection) and the superintendents for Masonomics may then see at a glance where they are having manpower issues as early as 8:00AM.

Please give us a call if you’d like to discuss a similar application for your company!

Commercial mason contracting access database text mobile SQL integration development

Posted in Access Code Examples, construction database, Legal Access Database, Manufacturing Access Database, Microsoft Access Solutions | Tagged , | Comments Off on Manpower resource scheduling and communication with an Access Database for commercial contractors

Populating a combo box with a new value

I really am not a fan of the code examples out there for adding an item to a drop down list.  I’d like to give you a simple example of how to approach.

So here’s the scenario:

You’ve got a drop down (cboItemID) of Items (tbl_Items) based on an item table with a key field of itemid.

You want to enter an item into the drop down list that’s not in the tbl_Items table. So you want to add the item to the item table, and then the drop down will accept the new item.

Here’s how I did it:

1 – Set drop down (combo box) LimitToList property to ‘Yes’

2 – Set the drop down On NotInList property to [event procedure] and enter the following code:

Private Sub cboItemID_NotInList(NewData As String, Response As Integer)

    Dim intReply As Integer

    intReply = MsgBox(NewData & " is not in the list of Items. Do you wish to add it?", vbYesNo, "Add Item?")

    If intReply = 6 Then
        CurrentDb.Execute "INSERT INTO tbl_Items ( Item_Description ) SELECT '" & NewData & "' AS Item_Description"
        Response = acDataErrAdded
    Else
        cboItemID = Null
        Response = acDataErrContinue
    End If
 
End Sub

 

Combo box notinlist limit to list property

Please note that I made the assumption that cboItemID is a 2 column combo box with ItemID as the first column and Item_Description as the second Column.

If you have any question on this code, please feel free to email me or give me a call. I’m happy to help out.

Jack Jaeger
Data Control
jjaeger@datacontrolllc.com
804 928 4111

Posted in Access Code Examples, Legal Access Database, Manufacturing Access Database, Microsoft Access Solutions | Tagged , , , , , , , , , , , , | Comments Off on Populating a combo box with a new value

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 | Comments Off on Specific Port in Connection String to SQL

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 , | Comments Off on Access linked to SQL

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 , , , | Comments Off on 64 Bit

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 , , | Comments Off on Improve Db Performance in a Network Environ