Virginia, Henrico County Public Schools Facilities/Projects/Asset Management Database

We’re particularly proud to work with one of the best counties in the United Stated (and the home of Data Control, LLC!) We have built and implemented an Asset / Facility / Project tracking database for Henrico County School Systems.

If you’d like a demonstration of this database to see if it would work for your County, then please call us and set up a shared view session to see a demonstration.

The Processing Choices:

Main Menu

Vehicle Data Entry:

Vehicles

The Reports:

Reports

The Facility Data Report:

Facility Data Report

Posted in Announcements/News, Government Database, Microsoft Access Solutions, Uncategorized | Tagged , , , , , | Leave a comment

FTP files using Microsoft Access

I have an import/export client that runs their entire operation with Microsoft Access as a backbone application. This company has been in Fastcompany for a couple of years now.

Today I had the opportunity to go back and slightly modify some FTP code to download/upload transaction files.
They sell their merchandise through Amazon, Walmart, Houzz, and more. Today we added Hayneedle.
So I thought I might share the code necessary to perform an FTP transfer.

Please note that this code assumes you have a ‘zstblInformation’ table set up containing the FTP credentials:
strWayfairFTPServer = DLookup(“WayfairFTPSite”, “zstblInformation”)
strWayfairFTPUser = DLookup(“WayfairFTPUser”, “zstblInformation”)
strWayfairFTPPassword = DLookup(“WayfairFTPPassword”, “zstblInformation”)
strWayfairFTPFolder = DLookup(“WayfairFTPFolder”, “zstblInformation”)

Give me a call at 804 928 4111 if you have problems implementing this code.
This code does not require a library to be installed in Access.

Option Compare Database
Option Explicit

‘Open the Internet object
Private Declare PtrSafe Function InternetOpen _
Lib “wininet.dll” _
Alias “InternetOpenA” _
(ByVal sAgent As String, _
ByVal lAccessType As Long, _
ByVal sProxyName As String, _
ByVal sProxyBypass As String, _
ByVal lFlags As Long) As Long

‘Connect to the network
Private Declare PtrSafe Function InternetConnect _
Lib “wininet.dll” _
Alias “InternetConnectA” _
(ByVal hInternetSession As Long, _
ByVal sServerName As String, _
ByVal nServerPort As Integer, _
ByVal sUsername As String, _
ByVal sPassword As String, _
ByVal lService As Long, _
ByVal lFlags As Long, _
ByVal lContext As Long) As Long

‘Get a file using FTP
Private Declare PtrSafe Function FtpGetFile _
Lib “wininet.dll” _
Alias “FtpGetFileA” _
(ByVal hFtpSession As Long, _
ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, _
ByVal fFailIfExists As Boolean, _
ByVal dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean

‘Send a file using FTP
Private Declare PtrSafe Function FtpPutFile _
Lib “wininet.dll” _
Alias “FtpPutFileA” _
(ByVal hFtpSession As Long, _
ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean

‘Close the Internet object
Private Declare PtrSafe Function InternetCloseHandle _
Lib “wininet.dll” _
(ByVal hInet As Long) As Integer
Public Sub subPerformFTP(pPathAndFile_To_Send As String, pServerName As Variant, pLogonName As String, pLogonPassword As String, pRemoteDirectory As Variant, pRemoteFileName As String, pUPLOAD_OR_DOWNLOAD As String)

Dim strRemoteDir As String
Dim strRemoteFile As String
Dim strLocalFile As String

Dim strRemoteDir2 As String
Dim strRemoteFile2 As String
Dim strLocalFile2 As String

On Error GoTo Err_f_PerformFTP

DoCmd.Hourglass True

FTPClient.ServerName = pServerName
FTPClient.UserName = pLogonName
FTPClient.Password = pLogonPassword

FTPClient.OpenFTP
FTPClient.OpenServer

Select Case pUPLOAD_OR_DOWNLOAD

Case Is = “Upload”
strRemoteDir = pRemoteDirectory
strRemoteFile = pRemoteFileName
strLocalFile = pPathAndFile_To_Send
FTPClient.PutFile strRemoteDir, strRemoteFile, strLocalFile, “ASC”

Case Is = “Download”
strRemoteDir2 = pRemoteDirectory
strRemoteFile2 = pRemoteFileName
strLocalFile2 = pPathAndFile_To_Send
FTPClient.GetFile strRemoteDir2, strRemoteFile2, strLocalFile2, “ASC”

End Select

FTPClient.CloseServer
FTPClient.CloseFTP

Exit_f_PerformFTP:
DoCmd.Hourglass False
Exit Sub

Err_f_PerformFTP:
MsgBox Err.Number & ” ” & Err.Description, vbCritical, “Error in f_PerformFTP module”
Resume Exit_f_PerformFTP

End Sub

Public Sub subFTPTestWayfair()

Dim strPathFileForUpload As String
Dim strFileForUpload As String

Dim strWayfairFTPServer As String
Dim strWayfairFTPUser As String
Dim strWayfairFTPPassword As String
Dim strWayfairFTPFolder As String

strPathFileForUpload = “Z:\Operations\Wayfair\Inventory\Jackzzz.xlsx”
strFileForUpload = Format(Date, “yymmdd”) & ” Wayfair Inventory FOR FTP.csv”

strWayfairFTPServer = DLookup(“WayfairFTPSite”, “zstblInformation”)
strWayfairFTPUser = DLookup(“WayfairFTPUser”, “zstblInformation”)
strWayfairFTPPassword = DLookup(“WayfairFTPPassword”, “zstblInformation”)
strWayfairFTPFolder = DLookup(“WayfairFTPFolder”, “zstblInformation”)

‘subPerformFTP(pPathAndFile_To_Send As String, pServerName As Variant, pLogonName As String, pLogonPassword As String, pRemoteDirectory As Variant, pRemoteFileName As String, pUPLOAD_OR_DOWNLOAD As String)

‘subPerformFTP strPathFileForUpload, strWayfairFTPServer, strWayfairFTPUser, strWayfairFTPPassword, “Inventory”, strFileForUpload, “Upload”
subPerformFTP strPathFileForUpload, strWayfairFTPServer, strWayfairFTPUser, “PASSWORD”, “inventory”, “jackzzz.xlsx”, “Upload”
MsgBox strPathFileForUpload & ” has been FTP’d.”

End Sub

Public Sub subGetAllFTPFiles(pServerName As Variant, pLogonName As String, pLogonPassword As String, pRemoteDirectory As Variant, pLocalDirectory As Variant, pFileType As Variant)

Dim strRemoteDir As String
Dim strRemoteFile As String
Dim strLocalFile As String

Dim strRemoteDir2 As String
Dim strRemoteFile2 As String
Dim strLocalFile2 As String

On Error GoTo Err_f_GetAllFTPFiles

DoCmd.Hourglass True

FTPClient.ServerName = pServerName
FTPClient.UserName = pLogonName
FTPClient.Password = pLogonPassword

FTPClient.OpenFTP
FTPClient.OpenServer
FTPClient.ClearFileNames
FTPClient.GetFileNames pRemoteDirectory, pFileType

Dim strFileName As String
Dim iFiles As Integer
iFiles = 0
iFiles = FTPClient.FileNames.Count

Dim i As Integer

If iFiles > 0 Then
i = 1
While i <= iFiles
strFileName = FTPClient.FileNames(i)
FTPClient.GetFile pRemoteDirectory, strFileName, pLocalDirectory & “\” & strFileName
i = i + 1
Wend
End If

FTPClient.CloseServer
FTPClient.CloseFTP

Exit_f_GetAllFTPFiles:
DoCmd.Hourglass False
Exit Sub

Err_f_GetAllFTPFiles:
MsgBox Err.Number & ” ” & Err.Description, vbCritical, “Error in f_FindAllFTPFiles module”
Resume Exit_f_GetAllFTPFiles

End Sub

Posted in Access 2007 problems/solutions, Access Code Examples, Microsoft Access Solutions | Tagged , , | Leave a comment

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 , , | Leave a comment

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 , , | Leave a comment

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 , , , | Leave a comment

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 , | Leave a comment

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 , , , , , , , , , , , , | Leave a comment

Are You Going to Access Day?

If you have been reading this blog, or if you are a talented up and coming developer who wants to attend a workshop with some of the Access geniuses of today, then you may be interested by an event called Access Day being held in Irvine, California.

Hosted by Armen Stein, an Access MVP, the event is a one day conference about everything Access related.  There will be developers, book authors, and even some of the programming team from Microsoft behind Access.

For more details, visit www.accessday.org

Posted in Announcements/News | Leave a comment

Importing an Excel Spreadsheet Into Access

Often times, it is far easier to input raw data into a spreadsheet.  Because of the table like format, copying and pasting can be done quickly and efficiently.  Add to that the ability to copy formulas down thousands of rows with a quick double-click (if you know where), and you can create the data much faster than you might be able to in Access.

However, for tracking that data, analysing it, and reporting it, Microsoft Access is the much better tool.  Beyond that, Access allows for relationships between data, which comes in very handy for analyzing when specific products were added to specific orders for a specific customer.  Tracking that in Excel is possible, but would be a nightmare to maintain.

So, what do you do with that data that is so easily entered into Excel when you need the analyzing power of Access?  Really, you have two options here.  One option is to link to the Excel spreadsheet. The other is to import the Excel spreadsheet.

Linking to an Excel spreadsheet from within Access is a viable option, but is not one I recommend on a normal basis.  The problem for me is that you are splitting up the data into multiple sources.  If that excel spreadsheet were to get moved or deleted, the data would be unusable and anything in the Access database that is depending on it would stop working correctly.  Plus, Excel doesn’t have all the bells and whistles of an Access table, such as preventing duplicate data from being entered into a field that is being used as an index.  In Access, you can, and normally would, define that Product 1 is named “Sample.”  In this case, you do not want another product to have a number 1 and perhaps you do not want any other product to be named “Sample.”  You can do this easily from within Access, but not in a linked Excel spreadsheet.

The better option in most cases is to import the spreadsheet and store the data within an Access table.  You can then set that table to have the indexes in place to make sure duplicates are not imported into the table.  When an import attempts to do so, Access will throw off a warning and the data will not be imported.

Importing data from Excel into Access is a fairly simple process, if you only want to do it one time.  At the top of the screen, using 2007+, in the External Data Ribbon, in the Import & Link section, you will see the button for Excel.  If you read my post on splitting a database, this should be familiar to you, except in that case you were linking from Access.  The Excel button will run you through a wizard, where you will find the correct Excel spreadsheet and be able to make sure that the fields being imported are of the correct data type (i.e. dates are not being classified as text and so on).

A word of caution, before you begin the import process.  Blank cells do not mean empty cells.  The import wizard will import any fields that it believes are populated.  If you ever entered information into cell H2 in the Excel spreadsheet, even if you erased it later, Access will import the entire H column as a field.  In order to avoid this, before you run the import, select the entire column by left clicking the column letter at the top, right click the column letter, and select “Delete.”  This will delete the entire column, but the columns will rename themselves, so in the sample above, H would be deleted, I would become H, J would become I and so forth.  This works for multiple columns, just select the first column, hold the shift key, and select the last column.  It also works for rows.

Importing an Excel spreadsheet can also be done programmatically through VBA code, but I will be posting that next week, so stay tuned!

Posted in Uncategorized | Leave a comment