Text or Hyperlink

This is a topic of discussion that I have participated in the forums a number of times in the past and will probably continue to engage in the future. I have decided to write an article to summarize my opinions on this subject. Essentially, the dilemma is whether to use a Text or a Hyperlink field to store a “link” to a file or folder or website, etc.

First of all, let me clarify that I am not exclusively in one camp or the other. Rather, this article just attempts to bring up points and counterpoints regarding the advantages and disadvantages of using one or the other in your database.

Using a Hyperlink Field

When designing a table in Access, using a Hyperlink field may be the easiest choice when trying to store “link” data. For example, let’s say you are creating a table for clients and you want to include a field that points to a network folder where you store all the documents relating to each particular client. Using a hyperlink field makes this easy because it comes with built-in functionalities that alleviates the developer from writing code to make it work. For example, by right-clicking on the hyperlink field and selecting Hyperlink > Edit Hyperlink, the user is presented with a dialog box for building the hyperlink. For our scenario, the user can navigate to the folder location and then click ‘OK’ to store the link to that folder in the table. Once the link is stored in the table, the user can simply click on the field to open a folder window to gain access to all the documents for that client.

Advantages

The main advantage of using a hyperlink field is its ease of use and creation. The hyperlink builder is very intuitive and Access does all the work for you. There’s no need to write any code to open the folder window or email client or whatever the program the link requires. It just works!

Another huge advantage of using a hyperlink field is that it can store “any” link. That means you can have one field that can store links to a file or folder path, a website, an email address, an FTP site, etc. Access should reconize the protocol specified in the hyperlink and then execute the appropriate program to open the link. This again would be a big task for the developer to determine the appropriate action to take and program to execute based on the protocol embedded in the link.

The last advantage of using a hyperlink field is that you can “display” a short text in place of the actual link to help the user understand better what the link is pointing to. For example, instead of seeing the full path to the folder such as \\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals, you can simply display something like “Client Proposals.” Clicking on the link will automatically take the user to the correct folder path.

Disadvantages

Unfortunately, that last advantage of the hyperlink field can also be considered as a disadvantage by other developers. For anyone not familiar with how hyperlink fields are stored in the table, it actually has three components separated by hash marks: the display text, the hyperlink address, and the hyperlink subaddress. So, in the above example, the hyperlink address is the full folder path, and the display text is “Client Proposals,” which is all the users see when looking at the data.

The problem appears when the developer starts to use the value in the hyperlink field in code. For example, if we add an unbound textbox on a form bound to a table with the hyperlink field and use the following as its Control Source: =[HyperlinkFieldName], you will see the actual value stored in the table instead of just the hyperlink address, which is what we would normally want. For the above example, the unbound textbox will display the following value: Client Proposals#\\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals#. Notice the hash tags separating the other components of the hyperlink field? If the user sees that, it could cause some confusion. Also, grabbing the hyperlink address using code by just referencing the field’s value could cause unexpected results. For this reason, some developers prefer to use a text field instead.

Using a Text Field

As I have mentioned above, a hyperlink field may have many advantages for beginners but most experienced developers will try to avoid the pitfalls and additional maintenance that may be brought on by using it and opt to use a text field instead. A text field doesn’t disguise what is stored in the table and it’s easy enough to simulate a hyperlink using a text field.

Advantages

I already mentioned the main advantage of using a text field – what you see is what you get. What is being displayed in the field is exactly what is stored in the table, no surprises. Because of that, the developer is then free to manipulate the data using code. The simplest approach to simulate a hyperlink using a text field is by using the following code:

Application.FollowHyperlink HyperlinkFieldName

Disadvantages

This may not matter to seasoned developers but the first disadvantage of using a text field to simulate a hyperlink is the need to use code. As you can see above, it’s not really hard to do that. However, if you can remember one of the advantages of a hyperlink field, if there’s a situation where the “link” data stores various types of protocols, then the code to execute the hyperlink can become vastly complicated. For example, simply using the FollowHyperlink method when the link only contains the following data will not work because it is missing the proper protocol: thedbguy.blog.com. The developer may have to include additional logic to the code to assign and use the appropriate protocol for the link in question. These protocols might include types such as ftp, news, gopher, etc.

Conclusion and Disclaimer

Understanding that a hyperlink field really contains three components helps the developer get a better handle in manipulating the link data. But, if the purpose of the hyperlink field is just to store websites, email addresses, or file/folder paths, then using a text field will probably save you the frustration of dealing with the surprises that comes with a hyperlink field.

As I mentioned in the beginning, I am not really recommending to use one over the other but merely trying to bring up possible arguments that you may hear when you get into a discussion regarding this topic with others.

Oh, and by the way, if you were wondering how to grab just the hyperlink address from a hyperlink field as if it was a text field, you can try the following code:

Split(PlainText(HyperlinkFieldName),"#")(1)

As usual, any comments are welcome. Cheers!

Query Join Basics

Recently, I have noticed a few developers, new to Access, showing up in the forums and were confused by the different JOINs that can be used in a query when retrieving basic information from their tables. This blog is my attempt of clarifying some of the basics regarding the different choices when joining or linking tables together in a query.

As I previously mentioned in my blog on Cartesian Query, there are basically three types of joins we can use when using multiple tables in a query:

  1. INNER JOIN – returns all matching records between the joined tables
  2. LEFT OUTER JOIN – returns all records from the “left-side” table and only the matching records from the “right-side” table (opposite of “right outer join”)
  3. RIGHT OUTER JOIN – returns all records from the “right-side” table and only the matching records from the “left-side” table (opposite of “left outer join”)

INNER JOIN

When two tables are linked using an INNER JOIN in a query, they might look like this:

INNER JOIN

In this example, the line between Field1 in Table1 and Field1 in Table2 will instruct Access to return all records from Table1 and Table2 where the values in Field1 between the two tables are a match. If Table1 had 15 records in it, and Table2 had 20 records in it but only 12 records match between the two, then the query will only show those 12 records that were a match.

LEFT OUTER JOIN

If we had wanted the query to return all the records from Table1 with all the matching records from Table2, we could use a LEFT OUTER JOIN. To create a LEFT OUTER JOIN, we would start with an INNER JOIN and then right-click on the “join line” between the two tables and select “Join Properties.”

Join Properties

The following ”Join Properties” dialog window will show up:

Join Properties

As you can see, the initial join was set to include only the records that match between the two tables (Join Type #1). To do a LEFT OUTER JOIN, we just need to select Join Type #2 and click the OK button. Once the Join Properties dialog window closes, the query designer should update the relationship line between the two tables with an arrow head pointing to Table2.

Left Outer Join

At this point, running the query will return all 15 records from Table1 and show 12 of them with matching values from Table2 and show Null values for the three records with no matching  values from Table2.

RIGHT OUTER JOIN

Creating a RIGHT OUTER JOIN is the same as the above process; but this time, we will select Join Type #3. This join type will return all records from Table2 and display any matching records from Table1. The arrow will now also point to Table1.

Right Outer Join

Running this query will return all 20 records from Table2 and show 12 of them with matching values from Table1 and show Null values for the eight records with no matching values from Table1.

Please note that if you view the SQL statement of the query when using either LEFT or RIGHT outer joins, the SQL statement does not actually use the keyword “OUTER.” Instead, you will just see either “LEFT JOIN” or “RIGHT JOIN.”

I hope that helps explain the basics between the different join types in an Access query. They should not be confused with the table joins used in the Relationship Window because, although they may look similar, that is actually somewhat a separate concept.

One final note regarding Access Query Joins, Jet SQL (the SQL language used by Access) does not support a FULL OUTER JOIN that full RDBMS like SQL Server does. To return all the records from both tables in Access, matching or not, you may have to use a UNION QUERY.

Google Maps API

I know that there are already plenty of demos available on the Internet for using Google Maps in an Access database, so what could be different with this one? Well, most of the Google Maps demo I’ve seen involve using the web browser control to display the map in an HTML page that uses Google Maps API through JavaScript. That’s because most Google Maps implementation in Access require a dynamic map.

But when a recent project required me to incorporate Google Maps on a report for printing, I found out that the usual approach of using a web browser control will not work. As a result, I had to search for an alternate solution. To see what I have discovered, click on the image below.

As usual, any comment is welcome and appreciated.

Fillable PDF API Demo

In response to requests from some of you who have seen my original Fillable PDF Demo at UtterAccess, I have decided to post an Acrobat API version to show the difference between this approach as compared to using the XFDF method.

Please remember that this method is only useful if you have the full version of Adobe Acrobat installed on your machine. One advantage of this method is that you are able to save the filled out form as a PDF file.

Click on the above image to download the demo from my website. Please let me know if you have any questions.

Merry Christmas!

Update Query with Append

I can’t say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will both update existing data in the table and add new records at the same time.

The Scenario

The member stated that he has a master table of data in Access and receives updated information every month in an Excel file. The Excel file contains the same information as the master table but with user changes to the data and possibly new information as well. The challenge is to update the Access table with the user changes and append the additional information as new records – at the same time.

The Usual Solution

The first step, usually, is to link to the Excel spreadsheet so we can access the data from within Access. We can then create an UPDATE query that joins the master table with the linked table to apply the latest changes to the data. However, this doesn’t include the new information from the linked table.

As a second step, we might also create an APPEND query to grab all the new information from the linked table and add them to the master table. We would then need to execute both queries, one at a time, to complete the task of updating the master table.

The Combined Solution

But, since the challenge was to perform both the update and append actions in one query, we had to use a different technique. I can’t say that this is a new technique because I’m sure it’s been around for a while, and most of you probably already know it. I just thought of writing it down here as a possible reference for me in the future.

So, how do we execute an UPDATE and APPEND query at the same time? Well, we also start out by linking to the Excel spreadsheet to access the data. We then create one query that joins the master table to the linked table. However, in this case, we will use an OUTER JOIN to return all the records from the linked table.

As a result, the database engine will update existing data and append new records at the same time. Here’s an example of how the query’s SQL statement might look like:

UPDATE tblMaster
RIGHT JOIN tblExcel
ON tblMaster.FieldName=tblExcel.FieldName
SET tblMaster.FieldToUpdateOrAdd=tblExcel.FieldToUpdateOrAdd

Pop-up, Modal, or Dialog Forms

This article is an excerpt, reprinted with permission, from the book “Professional Access 2013 Programming.”

Pop-up, Modal, or Dialog Forms
Access forms can be opened within your application in three different window modes: Popup, Modal, or Dialog. These can be set manually using the property sheet or programmatically using VBA or macros. Each mode behaves differently and can be used for different purposes. You can also combine the modes to have even greater control over the way the user can interact with the form and the entire application when the form is open.

Pop-up
A pop-up form opens and stays on top of all other open forms within your application (actually, the pop-up form opens outside of the Access application window). Although it is on top of other objects, the pop-up form does not necessarily have to have the focus. This can be quite helpful when you want to present the user with some reference information that needs to be available while they are using or entering data on another form. A pop-up form also allows the user to access the ribbon buttons and menus.

You can create a pop-up form by setting its PopUp property to Yes. This property is often used in conjunction with setting other properties such as Modal, Cycle (of records) and Border Style.

Modal
A modal form opens and keeps the focus until it is closed (or hidden). When a modal form opens, the user will not be able to use other forms or even go to the ribbon (it won’t even let you click the Exit button to quit Access). This mode is useful when you want to control the user’s workfl ow by opening each modal form in sequence based on the task they’re doing.

You can create a modal form by setting the form’s Modal property to Yes. If you use both the PopUp and Modal properties, you can make the form behave both as a pop-up (stays on top) and a modal (keeps the focus) form by setting them both to Yes.

Dialog
A dialog form opens on top of all the other forms and also keeps the focus until it is closed (or hidden). There is one major difference with a dialog form’s behavior as compared to a form with its PopUp and Modal properties set to Yes. When a form is opened in dialog mode, all code execution is also “suspended” until the dialog form is closed. This type of behavior is very useful when you need user input before proceeding with the next logic in your code. Some examples of dialog forms are the “warning” messages from Access or the MsgBox() and InputBox() functions.

You can only open a form in dialog mode using code (VBA or macro). The form can be opened in dialog mode regardless of the PopUp and Modal property settings. You can use either of the following lines of code to open a form in dialog mode,

DoCmd.OpenForm “FormName”, , , , , acDialog
or:
DoCmd.OpenForm “FormName”, WindowMode:=acDialog

(The above excerpt can be found in Chapter 17, Page 262 of the book “Professional Access 2013 Programming.”)

Professional Access 2013 Programming

I am very proud to say that I had the privilege to work with the authors Teresa Hennig, Ben Clothier, George Hepworth, and Dagi (Doug) Yudovich on their latest book “Professional Access 2013 Programming as a contributor and technical editor. This book was written by developers for developers. It covers all the new features of Access 2013 including the new “Access App” paradigm as well as plenty of tips for client database development.

The book goes in detail in creating a web application using Access 2013 with Office 365/SharePoint 2013 demonstrating how powerful this new feature could be and proving that Access Apps can be used in real-world business situations. My favorite part of the book is learning the new data types and macro actions in Access 2013. I never had any interest in creating Access 2010 web databases because they were so limited and hard to implement, but the new Access 2013 web application is more powerful and fun to create. The authors wrote the chapters in a manner that was easy to follow - I had a working Access App after reading just the first few chapters.

The book also includes download files and demos that you can immediately implement to enhance your own database projects. I highly recommend this book to any developer. Whether you’re involved in creating Access web solutions or not, this book will serve as a good resource and a valuable part of your toolkit.

SimpleCSV()

I know that there are already plenty of examples out there on how to concatenate records or child records into a delimited string, but I also see this request often enough in the forums that I wanted to have one place to point them to for a possible solution.

The following is just a simple function that most users and developers can add to their database to quickly produce a delimited string of records they would like to combine. I purposely made it simple because all the bells and whistles, such as sorting the list or accepting listboxes as arguments, are already available on other websites, so there’s really no need for me to reinvent the wheel for those.

To use this function, just pass a “SELECT” SQL statement and an optional delimiter to it. The power of this simple function is really in the creativity of your SQL statement. Please note that there are no error handlers, so you’ll have to add your own.

As always, I appreciate any comments you may have. Thank you.

Public Function SimpleCSV(strSQL As String, _
			Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records
' in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Concatenate the first (and should be the only one)
' field from the SQL statement
With rs
	Do While Not .EOF
		strCSV = strCSV & strDelim & .Fields(0)
		.MoveNext
	Loop
	.Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

What’s the opposite of DSum()?

A member recently posted a request in the forums for a function that does the opposite of DSum(). The intent was to have a query with a running total but using subtraction instead of addition. I never heard of anyone needing to do something like this before, so it was no surprise that I couldn’t find a built-in function that does the opposite of DSum() or Sum() to offer the member.

I really don’t know if anyone would ever need to do this again, but I just thought to share what I came up with in case it may be useful to someone else. The following function is very basic and does not include any error handling. Hopefully, you can modify it to suit your needs. I would happily accept any comments or recommendations to improve this function to better help those who may want to use it.

Public Function DDiff(strField As String, strDomain As String, _
    Optional strCriteria As String) As Variant

'accessmvp.com/thedbguy
'7/26/2013

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim varDiff As Variant

strSQL = "SELECT [" & strField & "] FROM [" & strDomain & "] "

If strCriteria > "" Then
    strSQL = strSQL & " WHERE " & strCriteria
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
    If Not (.BOF And .EOF) Then
        varDiff = .Fields(strField)
        .MoveNext
    End If
    Do While Not .EOF
        varDiff = varDiff - .Fields(strField)
        .MoveNext
    Loop
   .Close
End With

DDiff = varDiff

Set rst = Nothing
Set dbs = Nothing

End Function


To see the discussion that started it all, including a pure SQL solution, click here.

Event-Driven Programming

This topic may seem to come from out-of-nowhere, but I promised a member at UtterAccess that I will elaborate on this subject from our discussions in the forums.

What is Event-Driven Programming?

In a nutshell, event-driven programming (EDP) is a coding methodology where the flow of the program is determined by “events.” It is a different approach than procedural programming where the flow of the program is dictated by the steps specified as written in the program and object-oriented programming (OOP) where the flow of the program relies on the interaction between objects and their properties and methods.

Now, it so happens that the majority of Access VBA programming is an example of EDP (it also has some aspects of OOP mixed in), and that’s what this topic is all about. I just wanted to describe how Access uses EDP to help understand why we have to do certain things when writing our VBA procedures.

EDP Components

Event-driven programming requires three components:

  1. Event Handler
  2. Event Binding (or Wiring)
  3. Main Loop

Event Handler

The first step in developing an event-driven program is to write a series of subroutines or procedures that will be executed once an event occurs. Some examples of events that can occur in a Graphical User Interface (GUI) application are clicking on a command button, opening a form, and entering data in a textbox. Access allows three types of code as event handlers as seen in figure 1.

Fig 1. Choose Builder

 Access event  handlers can be a macro (saved or embedded), an expression (usually a function call), or a procedure. As with many programming environments, Access also provides an event template when using the Code Builder so that all you have to do is write the code for the event. Here’s an example of an event procedure using the template that Access provided:

Private Sub txtDateReported_BeforeUpdate(Cancel As Integer)
   If Me.txtDateReported > Date Then    
      MsgBox "Employee report date cannot be in the future.", _
         vbInformation, "Invalid!"
      Me.txtDateReported.Undo   
      Cancel = True
   End If
End Sub

Event Binding

After writing the code for handling an event in your program, the next step is to bind it to the specific event that the code is supposed to handle. I prefer to call this process as “wiring” the event. The method of wiring an event to its event handler in Access will depend on which code builder you used for creating the event handler. If you didn’t use the Code Builder, you can select a name of a Macro from the dropdown in the event property to wire that event to a saved macro. If you used the Macro Builder, the event property that you are wiring (binding) will have “[Embedded Macro]” displayed. If you used the Expression Builder, the complete expression preceded by an equal sign will be displayed. And lastly, if you used the Code Builder, the event property will display “[Event Procedure].”

It is important to note that “wiring” an event is an important step in making sure the program runs as expected. Even if you have written the event handler to do all the necessary tasks when an event occurs, if it is not wired to that event, then the event handler will never be executed. An event in Access is not wired if the event property is empty. Remember, even if an event handler is present in the code module, it will not be called if the event property is blank.

Main Loop

The final piece of the puzzle is called the “main loop” because it’s a function that coninually monitors for events and is responsible for calling the appropriate event handler when an event fires. Fortunately for us, Access already provides this function so we don’t have to worry about it.

The main point of this article is to explain that you, as the programmer, can write as many event handlers as you like and has the option to wire any of them to the event you want to handle. And unless you “wire” the event to its event handler, the application may not run as expected. Please note that Access will automatically “wire” the event handlers you’ve written if you used the “build” button next to each event property. And as was mentioned in the UtterAccess thread, you can also go straight to the VBA Editor window and write a procedure based on an Access event template, and Access will automatically wire that event handler to the appropriate event based on the template you used.

See the UtterAccess discussion that started it all.

Return to top