Monday, June 18, 2018

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

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

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.

Wednesday, May 23, 2018

Backstage View

A while back, I posted a demo showing how to hide the Backstage View in Access 2010 (click here to see the original article). This particular technique allows developers to somewhat restrict users from making unwanted modifications to their database application.

Unbeknownst to me, Microsoft made some changes to the Ribbon schema in Access 2013 and 2016. Although the reference to the schema is the same for all versions since Access 2010, which is

<customUI xmlns="">

it seems there were some changes made to the schema document for newer versions of Access. Fellow MVPs Daniel Pineault and Tom Wickerath brought this issue to my attention and provided a simple technique for identifying errors in the Ribbon XML at runtime. If you go to the Options settings, you can select Client Settings and put a check mark in the box for "Show add-in user interface errors" under the General heading (see image below).

When I did this, I started seeing the problems in my Ribbon XML (see image below for an example).

Just as a reminder, here's the Ribbon XML I used in Access 2010.

  <tab idMso ="TabInfo" visible="false"/>  
  <tab idMso ="TabRecent" visible="false"/>
  <tab idMso ="TabNew" visible="false"/>
  <tab idMso ="TabPrint" visible="false"/>
  <tab idMso ="TabShare" visible="false"/>
  <tab idMso ="TabHelp" visible="false"/>
  <button idMso="FileSave" visible="false"/>
  <button idMso="SaveObjectAs" visible="false"/>
  <button idMso="FileSaveAsCurrentFileFormat" visible="false"/>
  <button idMso="FileOpen" visible="false"/>
  <button idMso="FileCloseDatabase" visible="false"/>
  <button idMso="ApplicationOptionsDialog" visible="false"/>
  <button idMso="FileExit" visible="true"/>

As a result of this revelation, I have decided to update my demo and also include a separate version of the demo for use with Access 2013 and 2016. The new Ribbon XML I ended up using for Access 2013 and 2016 looks like this:

  <tab idMso ="TabInfo" visible="false"/> 
  <tab idMso="TabOfficeStart" visible="false"/>
  <tab idMso ="TabRecent" visible="false"/>
  <tab idMso ="TabSave" visible="false"/>
  <tab idMso ="TabPrint" visible="false"/>
  <tab idMso ="TabHelp" visible="false"/>
  <button idMso="FileSave" visible="false"/>
  <button idMso="ApplicationOptionsDialog" visible="false"/>
  <button idMso="FileCloseDatabase" visible="false"/>
  <button id="exit" label="Exit" onAction="=RibbonAction('exit')"/>

As you can see from the above updated XML, I had to remove some of the old controls because they caused errors in 2013 and 2016. Hopefully, this article can help you update your Backstage Ribbon XML for use with Access 2013 and 2016. You can download the updated demos from my website.

Please note failing to update the Backstage Ribbon XML will not render your customized Access 2010 database application useless when opened using Access 2013 or 2016. All the incompatible controls will just be ignored by Access when rendering the Backstage View. Furthermore, if the user does not even have the Client Settings set to show user interface errors, then no warnings will show up at all to indicate there's any problem.

Monday, March 26, 2018

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.


Thursday, December 7, 2017

A Recordset Bug

While trying to help a MSDN Forum user recently, I came across a potential bug when using a Recordset to loop through the records in a query. If you are interested in reading the discussion thread, you can find it using this link:

The poster was trying to build a Text file from the table records for an external application. His requirement was to combine all the field values and all the records into one long text data. Naturally, when someone asks to combine all table records, I typically recommend this SimpleCSV() function. Unfortunately, the function did not work for him as it produced weird characters in the output string. See image below...

String result with some weird characters.

I have never run into this issue before, so I started doing some research. It turns out, when you use a Recordset with a calculated column where multiple fields are concatenated to combine the data, the calculated column only looks normal if the resulting value is less than 255 characters.

To demonstrate this issue, I created a demo file, which you can download from my website.

In the demo, I created a table with two Text fields, both containing exactly 255 characters. When we use a Recordset to pull just Field1, the result looks okay. But when we pull both Field1 and Field2 into one column in a Recordset, then we run into this issue. Interestingly enough, I also found out that using DLookup() does not produce a problem.

I brought this issue to Microsoft's attention, and it was confirmed by the Access Team. They also promised to work on a fix for future release. In the meantime, if you must combine multiple fields using a Recordset, I recommend pulling all the fields individually when you create the Recordset and then just loop through the fields and concatenate them from within your procedure.

I hope you find this article helpful. As always, any comment is welcome.

Thank you very much for reading.

Monday, September 25, 2017

Get PDF Form Field Names

Some of you may be familiar with my Fillable PDF Demos, which allow the user to fill a PDF form with data from an Access database. The demos use two different approaches for accomplishing this. One solution uses an XML approach, which works even if users only has a PDF reader installed on their machines. The other solution requires users to have a full version of Adobe Acrobat because it uses the Acrobat API to fill the form and also save it using a different name.

The hardest part of using these demos is determining the names of the fields to fill out in the PDF document, especially if the PDF form was created by someone else. To help with this, the demo page includes a link to a website where one can upload a fillable PDF file, and a list of all form fields used in the PDF file is presented in return.

Although having a website to help determine PDF form field names is convenient, some readers have asked me if there is a way to duplicate this functionality using VBA. As a result, I have updated the demo page and added a third demo file to demonstrate exactly how to get the names of PDF form fields using VBA. However, this solution uses Acrobat API and JavaScript object, so a full version of Adobe Acrobat is required to use this demo.

To try out this demo, click on the image below to reach my demo page and select the third download link on the left navigation bar.

I hope you find this demo useful and please let me know if you have any questions or run into any issues using it.