Sunday, August 26, 2018

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.


http://www.accessmvp.com/thedbguy/downloads/theDBguyRegExSpeedTestDemoV1.0.zip
(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!

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
'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.

http://www.accessmvp.com/thedbguy/demos/getdbprops.asp
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="http://schemas.microsoft.com/office/2009/07/customui">

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.

<backstage>
  <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"/>
 </backstage>

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:

<backstage>
  <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')"/>
 </backstage>

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.

http://www.accessmvp.com/thedbguy/demos/listfiles.asp
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!