List Table Fields

I recently needed a way to list all the table fields from an Access database project, so I could export it into Excel and format it for the user's review (just to make sure I covered all the user's requirements). Unfortunately, all I found, looking around for a utility to do it, was a way to print a report showing all the fields. Unless I simply miissed it, I couldn't find one that exports it to Excel for further consumption. So, I decided to create this demo.

This demo is similar to my other "list" demos where you get to browse for the database file and clicking a button will populate a temporary table with all the information gathered from the selected database. You can then export the table's data into Excel, as desired. The only difference with this new version is that it should be able to handle a password-protected file. If the selected file is password protected, the user is presented with a prompt to enter the password.

To download this demo, please click on the image below. As usual, if you have any comments or suggestions for improvements or found any bugs with this demo, please do not hesitate to let me know. I would really appreciate it.

(click on the above image to download the demo file)

Thank you for reading!

Happy Halloween to Gremlins and Zombies

About three years ago, I wrote this blog article on Combobox Gremlins. In perfect timing for the upcoming Halloween, I received a follow up comment from Colin Riddington trying to explain the reason for the issue I described in that article. He was so inspired by this phenomenon that he decided to post his own version of the same puzzle at Access World Forums.

So, in the spirit of Halloween we are about to celebrate tomorrow, I have decided to set the story straight, since I forgot to update the original blog with the solution to the puzzle. First, I must apologize to Philipp Stiefel, who helped me figure it out back then, because when I migrated my blog site to a new web host, his comment did not make it to the new site.

Philipp suggested the original table had a lookup field at one point but was then removed. However, the Row Source property for the field in the table still contains the Value List used by the original designer (it was an inherited database, I did not make it). If you download my original demo and execute the following code in the Immediate Window, you will see the Value List.

?CurrentDb.TableDefs("Searchtable").Fields("Appropiation").Properties("RowSource").Value

At the time, Philipp's explanation made sense to me and I just left it at that. It wasn't until Colin's follow up comment did I finally decide to find out the main reason why this is happening. At this point, I was lucky enough to be able to ask one of the Microsoft Access engineers (Shane Groff) for an explanation on this subject and this is what he said:

-QUOTE-

Phil is right about the original gremlin, it is easy to reproduce:

1) Create a table with a text field
2) Set the Display Control property to 'Combo Box', then set Row Source Type to Value List, and Row Source to a;b;c or whatever list items you want
3) Now change Display Control back to Textbox
4) Save and close the table
5) Make a new form, add a ComboBox, bind the form to your table, and the combo to your gremlin field
6) Switch to form view

Result: Your combobox has values, even though the don't appear anywhere in the table or form properties.

Even if you now switch the Display Control in the table back to Combo Box and the Row Source Type to Value List, you won't see the values (although if you save at that point, I think we clear the property).

This value is stored as a property on the table column, and we don't clear it when you change the Display Control property, so it still get's inherited by the form Combo Box (unless you  Inherit Value List to No).

-END QUOTE-

One other thing that Philipp told me (and reemphasized by Shane above) was setting the Inherit Value List property of the Combobox to No will remove the Gremlins (or Zombies) from the Form's Combobox; however, the Row Source property of the field in the table is still there (so, setting the property back to Yes will bring them back). To permanently remove the Row Source property from my Gremlins demo, the following code needs to be executed:

CurrentDb.TableDefs("Searchtable").Fields("Appropiation").Properties.Delete ("RowSource")

So, the above revelation seems to be one more reason to avoid using lookup fields at the table level. In fact, the owner of The Access Web is deciding to update the page on The Evils of Lookup Fields in Tables to include this information.

I hope this article helps explain what was going on with my Gremlins demo and Colin's Zombies puzzzle. I want to thank all who helped figure out this puzzle.

Happy Halloween to everyone!

RegEx vs Character Loop

As is often the case with database management, we invariably have to clean up the data at some point. This demo was inspired by a thread discussion at UtterAccess where the original poster was looking for a way to remove unwanted characters from table records. (Here's the link to the UA thread, for additional information on the requirement.)

So, the requirement is easy enough, and as we all know in Access, there's always more than one way to reach a solution for any particular problem. That was also the case in this situation.

The two approaches I am comparing in this demo for removing unwanted characters from a string field in a table are (a) using regular expressions and (b) looping through the characters of the string. Both approaches will perform the job as required, but I was just curious if one particular approach would be faster than the other.

(click on the above image to download the demo file)

There may be other ways to test this theory other than the way I did it in this demo. For example, we could probably test the amount of time it would take to create a new table based on the result of cleaning up the data. As it stands right now, I just simply open and close a query containing a function call to each approach.

I encourage you to download the test file (it's only 526 KB) and try it out for yourself. Different configurations should give us different results. Please share your results if you do decide to give it a try and give us your conclusions as well.

I hope someone could find this little experiment somewhat useful in your projects or learning experience.

Thank you for reading...

UPDATE:
I wasn't sure if simply opening and closing the queries calling each function is enough to test for speed, so I added a couple more processes within each approach. You can download v1.1 using this link, if you want to see if it makes any difference in your outcomes. Cheers!

Retrieve Database (Extended) Properties

If you've ever wondered how to programmatically retrieve the information from the Summary tab when you click on "View and edit database properties" from the Info tab in Backstage View, then continue reading.

An example of the Summary tab information is shown in the image below.

If you try to loop through the CurrentDb.Properties collection, these particular properties are not present. For this reason, I consider them to be as "extended" database properties. To retrieve these extended properties, we can use a DAO object.

Here's an example code for specifically retrieving the extended properties found in the Summary tab.

Public Function GetSummaryInfo() As String
'6/10/2018
'thedbguy@gmail.com

Dim db As DAO.Database
Dim dbContainer As DAO.Container
Dim dbDocument As DAO.Document
Dim dbProperty As DAO.Property

Set db = CurrentDb()
Set dbContainer = db.Containers("Databases")
Set dbDocument = dbContainer("SummaryInfo")

For Each dbProperty In dbDocument.Properties
    Debug.Print dbProperty.Name & ": " & dbProperty.Value
Next

'cleanup
Set dbDocument = Nothing
Set dbContainer = Nothing
Set db = Nothing

End Function

The above technique can also be applied to retrieve some of the extended properties found in the other tabs (like Contents, for example). To see a demo file using this technique to examine the database properties for any Access database file, click the image below.

click on the above image to download the demo

I hope you find this article useful. Please let me know if you find any bugs in the demo.

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files in a folder or directory, I thought this was challenging enough for me to give it a try.

The original poster's request was to allow the user to search the folder for files matching a keyword and a list of specific file extensions. Also, the search should include all files inside subfolders.

Many of the examples I have seen use a loop to go through the folder structure and list all the files in them. Since I am a bit fascinated with recursion, I thought I would give it a try using a different approach. As a result of my attempt, I have created this demo.

Click on the above image to download the demo.

To use this demo, the user selects a starting folder path and enter an optional search keyword or list of file extensions to search. The search keyword is used to match the file names, while the list of file extensions will return only the files with matching file extensions.

The demo presents the result of the search using a Listbox. Double-clicking on an item in the Listbox will attempt to open the file for viewing. This version of the demo (v1.0) currently uses a Value List in the Row Source of the Listbox, which limits the number of files allowed to be displayed. The demo can be modified to store the file names in a table to allow the Listbox to display more items. I might do this in future versions of the demo.

In the meantime, I hope you find this demo useful, or at least, find the approach a little interesting.

As usual, I would appreciate any comments or feedback regarding this demo.

Cheers!

Collect Signatures in Access Demo

If you create database applications, you are probably happy with storing everything as electronic data. However, there may be occasions when you might need to collect hand-written information.


Some Access database developers like to use tablets or smart phones to make their database applications somewhat portable. And if their business requires a hand-written signature from a client, these devices are already equipped with an input device for collecting the signature.


This demo uses an ActiveX control to allow the user to sign a form. The signature information is then stored with their data and displayed on a form using two different techniques.

You may download the demo file at UtterAccess or My Website.

Temporary Database

In an earlier blog, I mentioned about writing an article on how to use a temporary database for manipulating data that does not cause the front end to increase in file size. Unfortunately, I got side-tracked and forgot about it until recently when someone reminded me about what I said and requested that I write the article now. I apologize for the long delay and hope that this article and the attached demo will help clarify what I meant back then.

To recap, it is sometimes unavoidable to use temporary tables as a working space to manipulate the data before it can be consumed to represent its final form. However, doing so results in the size of the database file to increase almost exponentially each time this happens. It then becomes necessary to perform a compact and repair at a regular interval to keep the file size to a minimum and prevent reaching the 2GB limit.

As an alternative, the working data can be moved to a separate and temporary database file, so the size of the front end file will not be affected. That is what this demo file will try to illustrate. Please click on the image below to download the file.

Click here to download the demo...



How it works.

The demo contains a table with the data that is required to be manipulated. Instead of creating a temporary table, the demo creates a temporary database in the same folder as the front end file. It then copies the data from the local table into the temporary database. A linked table is then created to establish a connection between the front end and the temporary database. As the linked table is created, a security warning may display. This can be avoided by creating the temporary database in a Trusted Location. When the user closes the database, the temporary database file can be manually deleted. The demo automatically deletes the temporary database file prior to creating a new one.

Please let me know if you have any questions. Thank you.

Original post date: July 31, 2015

Sending out Email using CDO

I know there are a few demos already available showing how to send out an email from Access using SMTP and CDO, but I received a request in the forums recently to simplify ("dumb down") the process. So, to that respect, the following demo just shows the very basics on how to use CDO (Collaboration Data Objects) using one form where all the essential elements for setting up the email object is contained in one subroutine. Click on the image below to go to the download page. Enjoy!

Click here to download the demo...

As usual, please let me know if you have any comments or questions. Thank you.

Original post date: December 23, 2014

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.



Click here to download the demo

As usual, any comment is welcome and appreciated.

Original post date: January 13, 2014

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 here to download the demo

Please let me know if you have any questions.

Original post date: December 25, 2013

Should I use Make-Table or Append Query?

I have been involved in a few discussions over what causes database bloat more when it comes to using temporary tables: using a Make-Table query or just deleting the records and appending new ones?

If you are highly concerned about database bloat (the database file size steadily increases with each use) caused by using temporary tables, then the best approach is to avoid using temporary tables altogether. However, the need to use temporary tables cannot be avoided sometimes. In those cases, there are two main approaches to creating and populating a temporary table.

Make-Table Query

The simplest approach is to use a Make-Table query to dynamically create the temporary table. Of course, you will have to delete the temporary table after you're done with it because Access will throw an error when it tries to create a table that already exists.

Delete and Append Query

The other approach is to create your temporary table's structure beforehand and just populate it at runtime. With this approach, you will first run a Delete query to empty out any remaining data in the temporary table before you execute an Append query to populate the table with the necessary data.

So which one causes more bloat?

I have always thought that creating and recreating database objects over and over would cause database bloat more than just deleting and adding new data. However, some experts swear that it's actually the opposite. So, to settle the score, I have decided to create a little experiment. To see the result of my little experiment, skip to the end. To try it out for yourself, click on the image below to download the demo file I used for this experiment. By the way, the demo version was created using Access 2007.

Click here to download the demo...



Is there a better approach?

Actually, there is... If you regularly use temporary tables in your database projects, you can avoid the issue of database bloat by using a temporary database instead of temporary tables. I will elaborate more on this approach in my next blog; but essentially, you can create a temporary database with all the temporary tables in it and just link to them from within your current database. Once you are done processing the data, you can simply delete the temporary database file since you will create a new one next time you need it.

My test results

Based on my little experiment, I was able to confirm that both methods (make-table and append query) result in database bloat. And what's interesting to me was that both methods produced about the same amount of bloat, as you will see in the results table if you run the experiment yourself. What I did find significant in my experiment was that using a make-table query ran so much faster than using an append query. So, based on this experience, I will probably change my tune and recommend using the make-table approach over the append query.

Additional info

Please note that I used the term "database bloat" very loosely in this article. Some experts do not consider creating records on purpose as database bloat because we all know that adding records is supposed to increase the file size of the Access database.
If you have any comments or questions, please let me know.

Original post date: June 24, 2013

Rearrange Record Sort Order

Sometimes, the sort order of table records cannot be easily determined by simply using the values in its fields. In those cases, it might be necessary to add a "sort order" field where the user will explicitly define the record's sort order within the table.


I've had a couple of requests in the forums for a demo on how to easily rearrange or reorder the records based on a "sort order" field value using a GUI. So, I have decided to create a demo based on a couple of methods I have seen while using SharePoint.


Method 1:
When creating or editing a Standard or Datasheet List View in SharePoint, you can set the order of the columns using a combobox. Selecting a number from the combobox sets that columns position in the View from left to right.


Method 2:
When you modify the Global or Quick Launch Navigation for a Site in SharePoint, you can select a Heading or a Link and then use the "Move Up" or "Move Down" links on the small menu bar to change the order of the Navigation Items.


My Demo:
Using the above methods as my inspiration, I created this demo for rearranging records sort order using a form. To download the demo from my website, click on the image below.

Click here to download the demo.

As always, any comment or recommendation is greatly appreciated.

Original post date: March 22, 2013