Introduction

Welcome! FTP, API Data, & Group Search

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Access Developer Level 32. In this course we will work with FTP in Access to upload files to a web server with one click, retrieve values from a web API using VB for live currency conversion, and set up a group search to find customers by group membership. We will also discuss the proper use of the OnDelete event and creating friendly error messages for duplicate values. Additional lessons include exporting reports as HTML, using Google Drive for sharing files, managing currency conversion rates, and filtering customers by group using parameter queries and forms.

Navigation

Prev: D32
Next: Lesson 01

Keywords

Access Developer, FTP files to web server, export report as HTML, web API currency conversion, VB currency utility, update currency rates online, customer groups search, OnDelete event, friendly error messages, group filter, XML HTTP object, FindBetween f

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Access Developer Level 32 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we will learn how to FTP files up to a web server with one click. This is great if you have information that you want to share on your website, such as a calendar or a product list. I will teach you how to click one button and then have Access export the report or whatever other data you want, and then automatically FTP it up to your website.

Then we will see how to get values from a web API using just VB and without using the web browser control. We will build a little currency conversion utility where we can convert from, let's say, US dollars to Euros or to Canadian dollars. We will have up-to-the-minute currency conversion rates. We will click one button, get data, and it will go out to the web, grab the conversion rates, and bring them into our database.

Then finally, we will do a group search. Now, we put customers into groups in previous classes back in the expert series. Now I am going to show you how to build a search form where you can say, here is a list of groups, I want to see all the customers that are in any of those groups, or I might want to see all of the customers who are in all of those groups. That is a tricky situation to pull off and we will cover that in today's class.

Plus, we will learn a whole lot more stuff. We will learn how to properly use the OnDelete event, which is something that I have kind of avoided because I do not like it, but I will show you how to do it in this class. We will see how to pop up friendly error messages instead of the default system error message when the user does something like try to add a duplicate value to a field that is indexed with no duplicates.

All that is coming up in this class.

Of course, this is the 32nd level of the Access Developer Series. First comes Beginner, Expert, Advanced, and then Developer 1 through 31. I recommend you take all those classes before taking this one. I strongly recommend you do not skip levels. There is lots of stuff that is covered in those extra levels, even if you think you do not need them. I have a whole page on not skipping levels. Go read this.

I will be using Access 365. It is currently October of 2021, so it is roughly equivalent to Microsoft Access 2019. Of course, I strongly recommend the Access 365 subscription because you have always got the most up-to-date version of Access available.

If you have any questions about the material covered in today's class, just scroll down to the bottom of the page and post your question there. If you have general Access questions, you can post them in the Access forum. Do not forget to check out my kind of almost sort of daily podcast about Microsoft Access. It is absolutely free. Visit the TechHelp page.

Let us take a look at exactly what is covered in today's class.

Lesson 1 today is a free bonus lesson. We are going to be taking it from my TechHelp videos. We are going to learn how to export a report as an HTML document.

Lesson 2 is another free bonus lesson today. We will cover part 2, the extended cut of my TechHelp 2 export reports as HTML. In this lesson, we will make a shortcut to a Google Drive folder where you can make it public and share it with anybody.

In lesson 3, I am going to show you how to automatically FTP any file up to a web server with one click. We will create a command line FTP script and we will perform the FTP operation inside Visual Basic. With one click, you will be able to export a report as an HTML document, FTP it up to your web server, and we will launch the page after that to make sure it works.

Lesson 4 is another free bonus lesson taken from one of my TechHelp videos. I will show you how to convert currency from one type to another, whether it is US dollars, Canadian dollars, Euros, whatever. We will create a lookup table and we will do the conversion in a query.

Lesson 5 is from the convert currency TechHelp extended cut. We will learn how to create a table for multiple conversion rates. We can then display all the currencies or just one currency at a time. We will put a currency selector on the customer form so we can pick that customer's preferred currency and then display both US dollars and their currency on their invoice. That is all covered in lesson 5.

In lesson 6, we are going to learn how to update our currency conversion rates from the web. We are going to use the XML HTTP object to connect to a web API over the internet. We are going to get the response text from that service, which will have all the currency rates in it. We will use the FindBetween function, which is the function I wrote, to pull out that information from the string that gets returned. Then we will update the rates in our currency table. All that is covered in lesson 6.

In lesson 7, we are going to be dealing with customer groups. We are going to put customers in groups based on certain tags. Then we will build a parameter query to do a simple wildcard search on them.

In lesson 8, we are going to create an actual group table. Pick a group from a list of groups. We can have customers in multiple groups. Then on the customer list form, we will make a combo box where we can filter based on the group selected and clear that filter.

In lesson 9, we are going to set up a search form for our groups. We will set up a search table. Then we will deal with some OnDelete issues that we have never talked about before. I will show you how to make some friendly error messages if you put duplicate values in. Then at the top part, we will see how to show customers that are in any group listed and show any customer that is in all of the groups that are listed. That is the top part. We will get to that in this lesson.

Thanks for watching.

Quiz

Q1. What is the main benefit of automating FTP uploads from Access?
A. It allows you to quickly share Access data on your website with one click
B. It increases database security by encrypting files
C. It compresses and archives files automatically
D. It tracks changes in your data over time

Q2. When retrieving values from a web API in Access, what method is used to avoid the web browser control?
A. Using VB and the XML HTTP object
B. Embedding an Internet Explorer window
C. Exporting data to Excel first
D. Relying on third-party plugins only

Q3. What does the currency conversion utility demonstrated in the video allow users to do?
A. Convert between different currencies using up-to-the-minute exchange rates
B. Export Access tables to PDF format
C. Automatically print invoices
D. Send automatic payment reminders

Q4. Why is group search with filter options in Access considered challenging?
A. Some customers may belong to multiple groups, requiring complex queries to filter by 'any' or 'all' group selections
B. Access does not allow multiple filters on forms
C. Only one group filter can be applied at a time
D. The OnDelete event cannot be used with groups

Q5. What is advised regarding taking previous levels before Access Developer Level 32?
A. Students should complete all previous levels as each covers important foundational material
B. Skipping straight to Developer 32 is encouraged for advanced users
C. Only the Beginner series is required
D. Advanced series is optional for Developer classes

Q6. In one of the lessons, what is demonstrated regarding exporting reports?
A. How to export a report as an HTML document
B. Changing reports directly into SQL code
C. Exporting reports to PowerPoint slides
D. Encrypting reports before export

Q7. What does the OnDelete event generally allow you to handle in Access?
A. Actions taken when a record is deleted, such as displaying custom error messages
B. Printing reports after deletion
C. Duplicating records automatically
D. Sending deleted records to Excel

Q8. Which method is shown for updating currency conversion rates in the database?
A. Using a web API and parsing the response with a FindBetween function
B. Manually entering exchange rates every day
C. Importing rates from a CSV file only
D. Copying rates from email notifications

Q9. On the customer list form, what does adding a combo box for groups enable?
A. Filtering customers based on selected group(s) and clearing the filter easily
B. Printing group reports in bulk
C. Automatically assigning groups on data entry
D. Exporting customer data to group-specific files

Q10. What is the advantage of making a Google Drive folder public and sharing link shortcuts as shown in the lessons?
A. Anyone can access and view the exported files without needing login credentials
B. Files are encrypted and only visible to the owner
C. It allows direct editing of the database by viewers
D. It improves the speed of Access queries

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on several advanced topics for Microsoft Access developers. I am Richard Rost, your instructor, and in this lesson, we will be exploring ways to automate certain tasks and enhance your Access applications.

First, I will show you how to upload files to a web server using FTP with a single button click. This approach is particularly useful if you need to share information, like calendars or product lists, directly on your website. You will learn how to export data from Access to a report, and then automate the upload process to your website, making data sharing seamless and efficient.

Next, I will walk you through how to retrieve values from a web API using only Visual Basic, without needing the web browser control. As an example, we will build a currency conversion tool that allows you to convert between currencies like US dollars and euros or Canadian dollars. The tool will fetch real-time conversion rates from the web, and with a button click, you can import up-to-date values directly into your database.

After that, the class will focus on setting up a group search for your customer records. We previously organized customers into groups in the expert series. Now, I will demonstrate how to create a search form that lets you view all customers who belong to any of a selected list of groups, or, more complex, to find customers who are members of all those groups at once. This type of search can be tricky to set up, but by the end of the lesson, you will understand how to put it together for your own applications.

You will also learn some important additional skills. I'll explain the proper use of the OnDelete event, which is something I have not spent much time on before because it has some pitfalls, but I will cover the correct way to use it in this class. I will also demonstrate how to replace the default system error messages with your own more user-friendly pop-up messages, especially in situations where users try to enter duplicate values into fields that require unique entries.

Remember, this is lesson 32 in the Access Developer Series. The proper order is Beginner, Expert, Advanced, and Developer levels 1 through 31. I strongly advise that you take all prior lessons before starting this one, even if you think you do not need them. A lot of foundational concepts are covered in previous lessons, and skipping levels might leave gaps in your understanding. I encourage you to check out my page on why you should not skip levels.

For these tutorials, I am working with Access 365 as of October 2021, but the content is also relevant for Access 2019. I highly recommend using the Access 365 subscription so you can always work with the latest version.

If you have specific questions about today's material, feel free to post them in the comment section at the bottom of the page. For general Access questions, use the Access forum on my site. Also, check out my nearly daily podcast about Microsoft Access, which you can find on the TechHelp page. It's absolutely free.

Let me give you an outline of the lessons covered in this class:

Lesson 1 is a free bonus lesson, originally from my TechHelp video series, where I cover exporting reports as HTML documents.

Lesson 2 adds an extended tutorial, again from TechHelp, showing how to create a shortcut to a Google Drive folder to share those exported files with anyone.

Lesson 3 teaches you how to use command line FTP scripts with Visual Basic to upload files automatically to your web server after export. Once the file is uploaded, we will open up the web page to verify that everything worked as intended.

Lesson 4 is another bonus TechHelp lesson where I cover currency conversion between USD, Canadian dollars, euros, and more, using a lookup table to do the conversion right in your queries.

Lesson 5, also based on the TechHelp extended cut, takes the currency conversion further. You will create a table to store multiple conversion rates, display different currencies or just one at a time, and add a selector for customers' preferred currency right on their form. You will also see how to display both US dollars and their preferred currency on invoices.

Lesson 6 focuses on keeping those currency rates current by connecting to a web API via the XML HTTP object. We will parse the data returned, pull out just what we need using the FindBetween function I wrote, and apply the new rates to your currency table.

Lesson 7 deals with managing customer groups by assigning tags, then building a parameter query to perform flexible wildcard searches within those groups.

Lesson 8 explains how to use a dedicated group table, so customers can belong to multiple groups. You will create a combo box on your customer list form to filter based on selected groups and see how to clear these filters when needed.

Lesson 9 is all about setting up a powerful group search form. You will create a search table, address OnDelete event issues for better error handling, and see how to produce custom error messages for duplicate entries. You will also learn how to display all customers in any of a list of groups or only those belonging to all of the groups you specify.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Exporting a report as an HTML document
Creating a Google Drive public folder shortcut
Automatically FTPing a file to a web server
Creating a command line FTP script in Access
Performing FTP operations in VBA
Exporting and uploading an HTML report with one click
Launching the uploaded page to verify the FTP
Currency conversion using a lookup table
Converting values between US dollars, euros, and Canadian dollars
Building a currency conversion query
Assigning customers to groups with tags
Creating a parameter query for group searches
Creating a customer group table
Filtering customers by group with a combo box
Creating a group search form
Implementing a search table for groups
Using the OnDelete event in Access forms
Displaying friendly error messages for duplicate values
Filtering customers in any or all selected groups

Article

In this tutorial, I am going to walk you through some powerful techniques you can add to your Microsoft Access applications. These methods will help you share information easily on your website, bring in live data from the internet, and efficiently search your customer or client groups. We will also build user-friendly error messages and explore some tricks for managing data and forms in Access.

Let us start by exporting reports from Access as HTML documents. This is really helpful if you want to share, for example, a calendar or product list on your website. To do this, you can use the built-in capabilities of Access to export any report as an HTML file. Here is a simple example of how to export a report called "ProductList" to an HTML file using VBA:

DoCmd.OutputTo acOutputReport, "ProductList", acFormatHTML, "C:\Temp\ProductList.html", True

This command tells Access to output the "ProductList" report to an HTML file in your C:\Temp directory. The last argument, True, will open the exported file automatically for you to view.

Now, to make this shared file available to others on your website quickly and automatically, you can use FTP (File Transfer Protocol) to upload that file to your server with the click of a button in Access. To do this, we first create a small text file with our FTP commands. Save the following lines (with your own FTP credentials) as a file called UploadScript.txt:

open your.ftp.server.com
username
password
put C:\Temp\ProductList.html /public_html/ProductList.html
quit

Next, we can use VBA to execute the Windows command-line FTP program and supply it with our script. Here is the VBA code:

Shell "cmd /c ftp -s:C:\Temp\UploadScript.txt", vbNormalFocus

This command runs the FTP process, tells it to follow the commands in the UploadScript.txt file, and handles the upload for you. You can automate this entire process with a single button click: first export the report, then run the script above to upload the file.

To go even further, suppose you want to launch the uploaded file in your web browser to make sure it is on your server. In VBA, you can use:

Application.FollowHyperlink "http://yourwebsite.com/ProductList.html"

Now, let us move on to working with live currency conversion rates from the web. Suppose you need to show different currencies on your invoices, pick preferred currencies for your customers, or keep your pricing up to date. You can use a web API to get real-time currency rates in your Access database.

For this, you will use the Microsoft XML HTTP object to make a request to a public currency API (for example, exchangerate-api.com or exchangeratesapi.io). Here is the VBA code to get response text from a URL:

Dim objHTTP As Object
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", "https://api.exchangerate-api.com/v4/latest/USD", False
objHTTP.send
Dim apiResponse As String
apiResponse = objHTTP.responseText

This puts the response from the API into the apiResponse string. Usually, this response is in JSON format. To extract a specific currency rate, say for Euros, you can use a function like this:

Function FindBetween(strSource As String, strStart As String, strEnd As String) As String
Dim iStart As Long
Dim iEnd As Long
iStart = InStr(strSource, strStart)
If iStart > 0 Then
iStart = iStart + Len(strStart)
iEnd = InStr(iStart, strSource, strEnd)
If iEnd > 0 Then
FindBetween = Mid(strSource, iStart, iEnd - iStart)
End If
End If
End Function

Suppose the response includes "EUR":0.85 within the JSON string. You can call:

Dim euroRate As String
euroRate = FindBetween(apiResponse, """EUR"":", ",")

You can now update a table in Access that keeps your currency rates current. Once your rates are in the table, it is easy to join them with your invoices or price lists and display, for example, both US dollars and Euros on your forms and reports.

If you allow your customers to pick their preferred currencies, just add a field on their form, like PreferredCurrencyID, and link it to your currency table. When viewing an invoice, you can show both the local and preferred values, using a calculated control that applies the latest rate.

Now let us discuss searching in groups. Imagine you have customers assigned to different groups or tags, like "VIP", "Wholesale", or "Newsletter". You may want to filter your customer list to see only those in a certain group, or pull up customers who belong to several groups at once.

First, you need a table of groups and a many-to-many relationship between customers and groups. This requires a join table, say CustomerGroups, with CustomerID and GroupID fields. On your customer list form, you can add a combo box or list box to let you select one or more groups.

To filter customers by a selected group, you can use a query like:

SELECT Customers.*
FROM Customers INNER JOIN CustomerGroups ON Customers.CustomerID = CustomerGroups.CustomerID
WHERE CustomerGroups.GroupID = [Forms]![YourSearchForm]![cboGroup]

This will show only customers in the group selected in your combo box.

For more advanced searching, say you want to see customers who are in ALL of a set of selected groups, the logic is a little more complex. Here is an example: suppose you select three groups and want customers who are in every selected group. You can use a query like this:

SELECT Customers.CustomerID
FROM Customers INNER JOIN CustomerGroups ON Customers.CustomerID = CustomerGroups.CustomerID
WHERE CustomerGroups.GroupID IN (1,2,3)
GROUP BY Customers.CustomerID
HAVING Count(DISTINCT CustomerGroups.GroupID) = 3

Replace (1,2,3) with the IDs of your selected groups, and the final "3" with however many groups you have chosen. This query returns only the customers who are present in all the selected groups.

Let us also look at handling errors and OnDelete events. Often, Access shows users a generic system error when they, for example, try to enter a duplicate value in a unique field. This is not friendly, so we can intercept such errors and show helpful messages.

To do this, use VBA's error handling in a form's BeforeUpdate event. For instance, suppose a user tries to add an email address that already exists. You can write:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox "That email address is already in use. Please enter a different one.", vbExclamation
Response = acDataErrContinue
End If
End Sub

Error 3022 is the "duplicate value in index" error. By trapping it and showing your own message box, you keep users informed in a friendly way.

Similarly, you may want to control what happens when users try to delete records. You can use the OnDelete event to confirm deletion or prevent it if necessary. For example:

Private Sub Form_Delete(Cancel As Integer)
If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Confirm Delete") = vbNo Then
Cancel = True
End If
End Sub

This makes the delete action safer and gives users a chance to change their minds.

By mastering exporting to the web, connecting to live external data, group searching, and error handling, you will make your Access databases more robust and much more user-friendly. All these techniques use tools that are built into Access or standard VBA, so you will not need third-party add-ons.

Take your time, experiment with the code samples, and adapt the methods to your own projects. With just a bit of VBA and some careful planning, you will dramatically expand what Access can do for you and your users.