Access Driven Web Sites

You can control your web content with an Access Database. That’s how we run our website(s)!

So, here’s the datacontrolllc.com website showing 3 lists of clients: Subcontracting / Build-Fixing / Integrating

Access Database Providing Content to a Web Page

Access Database Providing Content to a Web Page

We’re going to concentrate on the first list of clients: Subcontracting.  Notice that the last client in the list is URS Corporation.

Now take a look at our Microsoft Access Database that controls this website’s content:

Access Database showing Data that is displayed on Web Site

Access Database showing Data that is displayed on Web Site

You’ll notice that we 12 have clients listed with sort orders 1,2,3,4,4,5,6,7,8,8,9,and 10.  The website is programmed (that is the list box on the website is tied to a query in a SQL Server database) to show the first 10 clients based on their sort order.  Notice that Bear Technologies in the Access database has a sort order of 9.  But because there are two clients with a sort order of 4 ( ShermanHoward and ACSG ) and two clients with a sort order of 8 (Ensafe and URS Corporation); Bear Technologies with a sort order of 9 doesn’t make it into the top 10.

So let’s change this.  We’re going to change Ensafe’s sort order to 908 in the Access database and then refresh the website page.

Changing Access Data through a Form/Table that links to SQL Server

Changing Access Data through a Form/Table that links to SQL Server

Refreshing web page:

Web List Content coming from Access Database

Web List Content coming from Access Database

How does the Access database change the data in the website?  Notice the linked tables in the Access database.  If we hover over the tblClients table we see that it is actually a SQL Server table located on our web server.

Access Table is Actually a Link To SQL Server on the Web

Access Table is Actually a Link To SQL Server on the Web

We built our web page using a WordPress template, then thoroughly modified the entire site with PHP code against a SQL Server Database.  We link our Access Database to the SQL Server database as well.  So when we want to update the content of the website, we simply open the Access database and make changes.  Why is this important?  Well, besides the obvious advantage that it is super simple for us to make quick changes to content, Google ranks websites higher if the website is constantly updating the pages.

Guess what?  If we change the sort order in our Access database so a different list of clients is 1 – 10, then we’ve changed our website’s data so Google will rank us higher.  And how easy is it to simply change sort orders?

Now let’s take a look at the client data for Bear Technologies on the website:

Web Content that needs to be improved through Access Database

Web Content that needs to be improved through Access Database

This is important content for Google to see to rank us higher.  It is very relevant to Access Databases and conveys information on how we implement and subcontract to other clients.  However, I notice that there are a bunch of weird ‘?’ symbols.  Let’s go change that from the Access database side:

Access Database using Zoom Feature and HTML tags for line breaks <br><br>

Access Database using Zoom Feature and HTML tags for line breaks

We can’t see the special characters from the zoom in Access.  But we know the first one occurs after ‘…using a web browser…’  So apparently our website doesn’t like the ‘-‘ character.  We’ll remove this and refresh the screen.  We’ll also put in a bunch of <br>,<br> line break tags to space out the text better.

Access Database using Zoom Feature and HTML tags for line breaks <br><br>

Access Database using Zoom Feature and HTML tags for line breaks

Here are the results of our changes:

Web Content Modified from Access Database

Web Content Modified from Access Database

A little better but not perfect yet.  But I hope you now see how you can tie your website’s content to an Access Database.  Have your website’s content retrieve from either a SQL Server or MySQL Database on the web server.  Then connect your Access database to that same SQL Server/MySQL database and voila!

Here’s a video of me make one of the changes above…

 

Posted in Access and SQL Server, Access Code Examples, Access Database on Web | Comments Off on Access Driven Web Sites

Combination Form Filters

I’m often asked by clients to perform combination filters. That is, not just a single filter on a form.  But combining multiple filters that interact with one another.

For instance, in the Marketing Database I’ve built for datacontrolllc.com, I have an email blast functionality that allows me to send bulk emails to my existing clients.  I use this to offer monthly discounts for various services.  On this email blast form I’ve got a couple of filters that I’d like to use in combination with one another.

Combination Filters - Email Blast Form

On this form I have two filters, both highlighted in green (our company’s convention for filters).  I’ve filtered the form by a single filter, the name/email filter using a wildcard filter of ‘data’ that targets, firstname, lastname and email.  Notice I’ve not used the second filter, email sequence.

After typing in a value in the name/email filter, the database automatically filters the form.   I approach this with an [EventProcedure] on the after update Event for each of the filter Controls.

Combination Filters - Filter control properties

Notice that I’ve taken the time to name this control: ‘txtFiltername’.  Naming this control in such a way will help me when I write code for this control, and then year’s later when I might modify the code, the name of ‘txtFiltername’ will make a lot more sense to me than ‘text18’

Here’s the code that performs the combination filter:

Combination Filters - Code

Email me at ‘jjaeger@datacontrolllc.com’ and I’ll email back this code so you don’t have to copy it.

The first two sub procedures: ‘txtFilterName_AfterUpdate’ and ‘frmFilterEmailSequence’ are the Event Procedures from the two filter controls.  These sub procedures simply call another sub procedure: subFilter.

The sub procedure ‘subFilter’ does all of the work.  Let’s start with the end of the sub procedure:

Me.FilterOn = False
Me.Filter = strFilter
Me.FilterOn = True

This is how you get a form to filter using code.  First you clear the current filter, then you give the filter property of the form a value (in this case a string variable: ‘strFilter’) and then you turn the filter on.

Now let’s take a look at the beginning of the procedure:

Dim strFilter As String
strFilter = “”

Here we establish the strFilter variable and give it a default value: ”

If Not IsNull(frmFilterEmailSequence) Then strFilter = ” AND frmFilterEmailSequence=” & Frame28
If Not IsNull(txtFiltername) Then strFilter = strFilter & ” and (ContactLName like ‘*” & txtFiltername & “*’ OR ContactfName like ‘*” & txtFiltername & “*’ OR Email like ‘*” & txtFiltername & “*’)”

Here we give ‘strFilter’ a value by inspecting our two filter controls.  Notice that put an ‘ AND ‘ statement in front of both strFilter values.

Lastly, we expect to have ‘ AND ‘ in front of our filter so remove the and with a ‘RIGHT’ function:

If strFilter <> “” Then strFilter = Right(strFilter, Len(strFilter) – 5)

Notice that trick we use in the txtFiltername section of code.  We use ‘(‘ and ‘)’ to combine OR statements and we use ‘Like’ and ‘*’ operators to perform wildcard functions.

 

Posted in Access Code Examples, Access Database Advanced Forms, Access Database Filters | Comments Off on Combination Form Filters

Access and Word Integration

Recently we’ve been asked by a number of clients including Roofing Consulting Services (Consultants, Richmond, VA), KnoxMarine (Surveyors, Charleston, SC) and Hirschler Fleischer (Attorney, Richmond, VA) to integrate Access with Word. What these companies have in common is that they produce reports to their clients and they want to both standardize the reports across their company and save their report writers time in producing these reports. A side benefit is that all of the necessary data to create the report is now consolidated in the Access Database. Lastly, because we are using Access to determine where Word Template files are held and where the destination reports go (and how they are named), Access is actually functioning as a light document management tool for the entire report creation and management process.

We accomplish this in Access by using a combination of file system objects (you’ll need to register the library in the access modules) and the Word Object Library (you’ll also need to register this.

We extensively use bookmarks throughout the various Word documents and create .doc or .docx ‘template’ files. These are NOT the Word Template files. They are standard word document files we name ‘Report 1 TEMPLATE.docx’ for example. If the user wants to change their template files, they simply open them and change them, taking care not to bother the highlighted bookmarks.

Here’s an example of extensive bookmarking in a Word document that Access will use to fill in data:

Access Database vba automation of Word through bookmarks

Access Database vba automation of Word through bookmarks

We then create code in Access to copy the template file and name the destination document and place the document where it belongs, often in a file folder with a client id number. Then we scroll through the document replacing the bookmarks with either text from the access database or images from the Access database (actually, Access stores the path and file of the image, not the image itself). Here’s an example of this code…

Function fncLetterToMerge(pLetterToMerge As String, id As Long)

‘pass in a field name in zstblinformation which represents the path to the file to be created

On Error GoTo c12_err

Dim rs As Recordset
Dim strPathAndFileToTemplate As String
Dim strPathAndFileToCreate As String
Dim xENCLOSUREx As String
Dim xGeneralNotesx As String
Dim intAssignedToID As Integer

DoCmd.RunCommand acCmdSaveRecord

If IsNull(DLookup(“PathToReports”, “jobs”, “id = ” & id)) Then
MsgBox “Please supply a path to reports for this file”
Exit Function
End If

‘strPath = DLookup(“defaultexportfolder”, “zstblInformation”)
strPathAndFileToTemplate = DLookup(“defaulttemplatefolder”, “zstblInformation”) & “\” & pLetterToMerge
strPathAndFileToCreate = DLookup(“PathToReports”, “jobs”, “id = ” & id) & “\” & DLookup(“[Job Number]”, “Jobs”, “id = ” & id) & ” – ” & pLetterToMerge

If Right(strPathAndFileToCreate, 4) = “docx” Then
strPathAndFileToCreate = Left(strPathAndFileToCreate, Len(strPathAndFileToCreate) – 14) & “.docx”
Else
strPathAndFileToCreate = Left(strPathAndFileToCreate, Len(strPathAndFileToCreate) – 13) & “.doc”
End If

‘Select Case pLetterToMerge
‘Case Is = “FileNotesMrg”

‘End Select

If FileExists(strPathAndFileToCreate) Then
intReply = MsgBox(“This file already exists. Do you wish to delete and replace with a new version?”, vbOKCancel, “Warning”)
If intReply = 1 Then
Kill strPathAndFileToCreate
Else
Exit Function
End If
End If

intAssignedToID = DLookup(“idtbl_employees”, “tbl_Employees”, “initials = ‘” & DLookup(“[assgn to]”, “jobs”, “id = ” & id) & “‘”)

BuildTemplate:
Dim oWord As Object ‘Word.Application
Dim doc As Object ‘Word.Document
Dim objShapes

Dim xFoundx As String
Dim xRecommendx As String

Set oWord = CreateObject(“Word.Application”)
Set doc = oWord.Documents.Open(strPathAndFileToTemplate)
oWord.Visible = True

doc.SaveAs (strPathAndFileToCreate)

With oWord.ActiveDocument

Select Case pLetterToMerge
Case Is = “XXXXX”

Case Is = “KMSC Photo Sheet – TEMPLATE.docx”
If IsNull(DLookup(“datephotostaken”, “jobs”, “id = ” & [id])) Then
MsgBox “Please enter the date the photos were taken on the main form”
Exit Function
End If

.Bookmarks(“xxClaimNumberxx”).Select
If Not IsNull(DLookup(“[claim number]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[claim number]”, “Jobs”, “id = ” & id)

.Bookmarks(“xxJobNumberxx”).Select
If Not IsNull(DLookup(“[Job Number]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[Job Number]”, “Jobs”, “id = ” & id)

.Bookmarks(“xDatex”).Select
If Not IsNull(DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)

.Bookmarks(“xDatex”).Select
If Not IsNull(DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)

‘i = 0
‘Set rs = CurrentDb.OpenRecordset(“select * from trptPhotoSheet where id = ” & id)
‘Do Until rs.EOF
‘ i = i + 1

‘If Not IsNull(rs!PhotoPathAndFile) Then .Bookmarks(“xxPhoto” & i & “xx”).Range.InlineShapes.AddPicture FileName:=rs!PhotoPathAndFile
‘ .Bookmarks(“xxPhoto” & i & “xx”).Select

‘If Not IsNull(rs!PhotoCaption) Then oWord.Selection.Text = rs!PhotoCaption
‘ rs.MoveNext
‘Loop

For i = 1 To 10
If Not IsNull(DLookup(“photo” & i, “Jobs”, “id = ” & id)) Then
.Bookmarks(“xxPhoto” & i & “xx”).Range.InlineShapes.AddPicture FileName:=DLookup(“photo” & i, “Jobs”, “id = ” & id)
‘.Bookmarks(“xxPhoto” & i & “xx”).Select
.Bookmarks(“xxPhotoCaption” & i & “xx”).Select
If Not IsNull(DLookup(“photocaption” & i, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“photocaption” & i, “Jobs”, “id = ” & id)
End If
Next

Posted in Access Automating Word VBA, Access Code Examples, Access Database Advanced Forms | Comments Off on Access and Word Integration

Access Database in the Cloud?

Throughout 2017, we were approached by a number of clients including KnoxMarine (Charleston, SC) and SeniorSmart (Sykesville, MD) to fix their ‘Cloud’ Access databases.  They were both using Microsoft Cloud to hold their Access Database files and were very disappointed with the results.  Primarily because this cloud solution creates a temporary copy of the Access Database on the local workstation, then overwrites the cloud version when the file is closed.

This, of course, means that if two users have the database open simultaneously, when the second user closes their version of the database, the first user’s database changes will be overwritten.

So what is a true Cloud Solution for an Access Database?  Then Answer: SQL Server or MySQL.

What you do is move your tables into SQL Server or MySQL.  This database needs to reside on a server that is on the internet (for instance, The Data Control LLC server we offer for $50/month).  Then modify your Access Database to connect to these tables through the internet.  We’ve done this for API Services (Newport News, VA) and HORWITZ, RICHARDSON & BAKER, LLC (Chicago, IL)

For instance:

 

Public Sub UpdateWebSQLConnections()
On Error GoTo err_Procedure

Dim rs As Recordset
Dim db As Database

Set rs = CurrentDb.OpenRecordset(“Select * from zstjncDataFiles_Tables Where DataFileID = 1”)
Set db = CurrentDb

Dim strWebIP As String
strWebIP = DLookup(“BackEndWebServer”, “zstblInformation”)

While Not rs.EOF
If UpdateSQLConnection(rs!SourceTableName, rs!DisplayTableName, “TCP/IP Address”, “ServerName”, “DatabaseName”, “Password”, “1”) Then
rs.MoveNext
Else
MsgBox “Connecting to SQL Server failed.”
Exit Sub
End If

Wend

    rs.Close
db.Close
exit_Procedure:
Set rs = Nothing
Set db = Nothing
Exit Sub

err_Procedure:
MsgBox Err & “: ” & Err.Description
Resume exit_Procedure

End Sub

If you’d like us to host your database in our cloud, we’ll convert your database for free.  If you want to host your database somewhere else, we’ll give you a price to convert the tables for you and modify your Access Database to use the new tables.

 

Posted in Uncategorized | Comments Off on Access Database in the Cloud?

Managing text communications (with an Access Database)

You can manage your texting using an Access Database. Contact us at www.datacontrolllc.com or jjaeger@datacontrolllc.com to learn more. You can purchase this Access database for $100.00

Posted in Access Code Examples, Microsoft Access Solutions | Tagged , | Comments Off on Managing text communications (with an Access Database)

Banking / Finance Real Estate Loan Portfolio Access Database

So we built this application for 1st Market Bank which has since become Union.  We’ve also sold it to 1st Capital Bank which is now Sterling.

Essentially, the user records all of the relevant information about Customer’s and their loans for real estate construction deals including the development the home is in and then the Bank may run 30 different reports to manage their portfolio.

1st Capital Application 3

The Customer information and small part of the loan information:

1st Capital Application

Example of the Current Outstanding Report:

1st Capital application 2

 

If you’d like a similar database, don’t hesitate to contact us and we can either build you a similar application from scratch, or modify this application to your needs!

 

Posted in Bank database, Uncategorized | Tagged , , , | Comments Off on Banking / Finance Real Estate Loan Portfolio Access Database

Integrating Access with Outlook using automation and vba (Visual Basic for Applications)

So you have a list of emails and you want to keep in contact with folks easily.

I do this by periodically sending out emails to my former clients and offering them incentives to come back and purchase again.  Since I already have their contact information in our Access Database for Invoicing, it makes sense to email them from here as well.

This is an example of an Access Database that interacts with Outlook using Automation and VBA Code.

On the right side, I’ve assigned my contacts and email sequence number:

Email Blast

On the left hand side I’ve created buttons for emailing groups of people based on their email sequence.

The drop down box with ‘Memorial Day Database Consulting Sale’ may be double clicked to open another screen for creating email messages:

email blast message

When you’ve chosen the group of people to email (keep the groups small so you aren’ considered a spammer) and you’ve chosen your message, you may then email the group by choosing one of the ‘Mail to…’ buttons.

This is the code that runs behind the button to send the emails:

=fncSendEmail(1)

Function fncSendEmail(pEmailSequence As Integer) As Boolean
    Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strDate As String
Dim strAttachment As String
    Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(“select * from tblHospitalAssociations where EmailSequence = ” & pEmailSequence & ” and Email<>’Unknown'”)
rs.MoveLast
rs.MoveFirst

‘If rs.RecordCount > 3 Then Stop

Set objOutlook = CreateObject(“Outlook.Application”)

Do Until rs.EOF
‘Stop
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg

Set objOutlookRecip = .Recipients.Add(rs![Email])
objOutlookRecip.Type = olTo
.Subject = txtSubject
.Body = txtMessage
If Not IsNull(txtAttachment) Then
strAttachment = txtAttachment
Set objOutlookAttach = .Attachments.Add(strAttachment)
End If                                                              ‘.Importance = olImportanceHigh  ‘High importance
.Display
.Send
‘CurrentDb.Execute “INSERT INTO tbl_Email_History ( ClientID, EmailSentDate, EmailSubject, EmailBody ) ” & _
“SELECT ” & rs!ClientID & “, Date(), ‘” & txtSubject & “‘, ‘” & txtMessage & “‘”

        End With
rs.MoveNext

Loop

    CurrentDb.Execute “UPDATE tbl_Historical_Emails SET [” & pEmailSequence & “s] = True ” & _
“WHERE EmailDate=#” & txtEmaildate & “#”
MsgBox “Done!”
    fncSendEmail = True
End Function
Posted in Uncategorized | Comments Off on Integrating Access with Outlook using automation and vba (Visual Basic for Applications)

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 , , , , , | Comments Off on Virginia, Henrico County Public Schools Facilities/Projects/Asset Management Database

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 , , | Comments Off on FTP files using Microsoft Access

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