How to check if an Access database object exist

The Usual Approach

The idea for this function came to me while responding to a question in the forums on how to check if a query exist. The usual approach is to assign the query's name to a QueryDef object and trap the error, if the query does not exist. For example:

Public Function QueryExists(QueryName As String) As Boolean
'thedbguy@gmail.com
'11/5/2021
Dim qdf As DAO.QueryDef
On Error Resume Next
Set qdf = CurrentDb.QueryDefs(QueryName)
If Err = 0 Then
    QueryExists = True
Else
    QueryExists = False
End If
Set qdf = Nothing
End Function

A Better Approach

Now, the above approach of using On Error Resume Next and then checking for errors is a very common approach. However, someone in the forums mentioned that this is not a very "clean" approach, because it leaves an Error Object hanging in the Errors Collection. They mentioned that their preferred method is to loop through the QueryDefs collection and check for the name of the query. For instance:

Public Function QueryExists_1(QueryName As String) As Boolean
'thedbguy@gmail.com
'11/5/2021

Dim qdf As DAO.QueryDef
Dim blnReturn As Boolean

For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = QueryName Then
        blnReturn = True
        Exit For
    End If
Next

QueryExists_1 = blnReturn

End Function

An Even Better Approach

Now, that makes a lot of sense, doesn't it? However, another smart person said: "Why not just use the MSysObjects table to check if the query exists?" Well, isn't that genius? So, inspired by that comment, I decided to create the following function as another option for checking if an Access database object exist or not. It goes something like this:

Option Compare Database
Option Explicit

Public Enum dbgObjectType
    dbgTable = 1
    dbgQuery = 5
    dbgForm = -32768
    dbgReport = -32764
    dbgMacro = -32766
    dbgModule = -32761
    dbgODBCLinkedTable = 4
    dbgOtherLinkedTable = 6
    
End Enum

Public Function ObjectExists(ObjectName As String, ObjectType As dbgObjectType, _
                                Optional DatabasePath As String) As Boolean
'thedbguy@gmail.com
'11/5/2021

Dim blnReturn As Boolean

If DatabasePath = "" Then
    blnReturn = DCount("*", "MSysObjects", "[Name]='" _
        & ObjectName & "' AND [Type]=" & ObjectType)
    
ElseIf Dir(DatabasePath) = "" Then
    'MsgBox "Cannot find " & DatabasePath, vbInformation, "Info!"
    Debug.Print "Cannot find " & DatabasePath
    blnReturn = False
    
Else
    blnReturn = CurrentDb.OpenRecordset("SELECT Count(*) FROM [;Database=" _
        & DatabasePath & "].MSysObjects WHERE [Name]='" _
        & ObjectName & "' AND [Type]=" & ObjectType)(0)
    
End If

ObjectExists = blnReturn

End Function

As you can see, I have decided to use an enumeration and allow the user to specify which object they want to search. Also, the above function allows the user to search objects in an external database. I just thought these additional features might help make the function a lot more flexible and allow the developer to use it in multiple situations. So, now, the above approach does not rely on the Error object and also skips going through a loop to search for a single object.

Extension

Having the above function available, we can even extend the idea by creating smaller helper functions designed to search for a specific object. Here are some examples:

Public Function TableExists(TableName As String, Optional DatabasePath As String) As Boolean

TableExists = ObjectExists(TableName, dbgTable, DatabasePath)

End Function


Public Function FormExists(FormName As String, Optional DatabasePath As String) As Boolean

FormExists = ObjectExists(FormName, dbgForm, DatabasePath)

End Function

I hope the above information was useful, and I welcome any comments, recommendations, or criticisms.

As always, thanks for reading. Cheers!

Listbox Selected Property and the Form Load Event

Here's something new I just learned from a recent forum discussion: A Listbox's AfterUpdate event will fire when VBA is used to select an item on the list using the Form's OnLoad event. What? Did you all know that? I wasn't aware of it until now.

Typically, the AfterUpdate event of a control does not fire when the value of that control was updated using code. If you want the code behind the AfterUpdate event to execute when you update a control using VBA, you will have to specifically call it. For example:

Call ListboxName_AfterUpdate

However, as I was saying, there is a specific scenario (I know of only one, so far) where a Listbox's AfterUpdate event will fire, without specifically calling it, when the Listbox is manipulated using VBA code. Here's the deal...

As demonstrated in this forum discussion, the OP used the Selected property to "highlight" an item in a Listbox during the Form's Load event. Interestingly enough, this causes the form to fire the Listbox's AfterUpdate event as well. Typically, using the Selected property does not update the value of a Listbox, which means the AfterUpdate event shouldn't fire. But for some reason, this is not the case during the Form's Load event. It turns out, setting the Selected property of an item in a Listbox during the Form's Load event also updates the Listbox's Value property. However, there is a twist... If we used the "normal" way of updating the Value of the Listbox, then the AfterUpdate event does not fire. To clarify, we can use any of the following methods to change the Value of the Listbox.

Me.ListboxName = "SomeValue" 'if it's bound to a Text column
Me.ListboxName = 1 'if it's bound to an ID column
Me.ListboxName = Me.ListboxName.ItemData(2)

So, the puzzle is, what is going on here? Why does it happen when we use the Selected property? Also, why does it only happen during the Form's Load event? Once the Form is fully opened, using the Seleted property merely highlights the item. It does not update its Value, and the AfterUpdate event does not fire. Equally, using any of the methods shown above to modify the Value of the Listbox at this point also does not fire the AfterUpdate event.

If you have any ideas or logical explanation for this, I would really like to hear it. If I find out the answer somewhere else, I will definitely let you know.

Thank you for reading!

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.

How to Retrieve the Hard Disk's Serial Number

I found myself looking for a way to retrieve the serial number of the computer's hard drive as a way of preventing unauthorized copies of an Access app. I have done it before but couldn't remember how. To my surprise, I found several ways to accomplish this task. I decided to post a couple of those techniques here to help anyone else who may find himself or herself looking to do the same thing in the future.

Before we start, the first thing we need to realize is hard disk drives have more than one serial numbers. One of the serial numbers we can retrieve is the one assigned by the hard disk manufacturer. This serial number should stay consistent throughout the life of the equipment. The other serial number available to us is the logical serial number assigned by the operating system when a disk is formatted. The value for the logical serial number may change if the disk is reformatted. Each technique presented below depends on which serial number you are interested in retrieving.

Physical Disk Drive Serial Numbers

The following function uses Windows Management Instrumentation (WMI) to create a connection to the local computer. The "WinMgmts" moniker is used to create a WMI object. Once a WMI object is instantiated, we can use the InstancesOf method to query the machine for system information.

Public Function HDSerial() As String
'6/14/2017
'thedbguy@gmail.com
'Returns the hard disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objWMI As Object
Dim objWin32 As Object
Dim objPM As Object
Dim strSN As String

Set objWMI = GetObject("WinMgmts:")
Set objWin32 = objWMI.InstancesOf("Win32_PhysicalMedia")

For Each objPM In objWin32
    strSN = strSN & (";" + objPM.SerialNumber)

Next

HDSerial = Trim(Mid(strSN, 2))

errExit:
    Set objPM = Nothing
    Set objWin32 = Nothing
    Set objWMI = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

Logicl Disk Drive Serial Numbers

The above function used the "Win32_PhysicalMedia" collection to reference all the physical drives connected to the computer. We can now modify the above function using "Win32_LogicalDisk" to get a collection of all logical or mapped drives connected to the computer for the current user.

Public Function LDSerialWMI(Optional DriveLetter As Variant) As Variant
'6/14/2017
'thedbguy@gmail.com
'Returns the logical disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objWMI As Object
Dim objWin32 As Object
Dim objLD As Object
Dim strSN As Variant

Set objWMI = GetObject("WinMgmts:")
Set objWin32 = objWMI.InstancesOf("Win32_LogicalDisk")

If IsMissing(DriveLetter) Then
    For Each objLD In objWin32
        DriveLetter = objLD.DeviceID
        strSN = strSN & (";" + DriveLetter + objLD.VolumeSerialNumber)

    Next

Else
    For Each objLD In objWin32
        DriveLetter = Left(DriveLetter,1) & ":"
        If DriveLetter = objLD.DeviceID Then
            strSN = ";" & objLD.VolumeSerialNumber

        End If    
    Next

End If

LDSerialWMI = Trim(Mid(strSN, 2))

errExit:
    Set objLD = Nothing
    Set objWin32 = Nothing
    Set objWMI = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

However, there is a more straightforward way to get the serial number of a specific logical disk drive. The following function uses the File System Object.

Public Function LDSerialFSO(DriveLetter As String) As Variant
'6/14/2017
'thedbguy@gmail.com
'Returns the logical disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objFSO As Object
Dim objDrv As Object
Dim strSN As Variant

DriveLetter = Left(DriveLetter,1) & ":"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDrv = objFSO.GetDrive(DriveLetter)

If objDrv.IsReady Then
    strSN = objDrv.SerialNumber
Else
    strSN = Null
End If
    
LDSerialFSO = strSN

errExit:
    Set objFSO = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

There is an important difference between the two functions for logical disk drive presented above. Using WMI returns the serial number as HEX; whereas, using FSO returns the serial number as a Long Integer. However, you can use the Hex() function to either convert the return value from the LDSerialFSO() function to HEX or modify the LDSerialFSO() function to return the serial number as HEX.

I hope you find this post helpful. As usual, please feel free to submit your comments to let me know how I can improve these functions. Thank you!

How to set Compact on Close on BE

The "Compact on Close" setting in the Current Database Options will automatically perform a Compact and Repair (C&R) each time the user closes the database file. Leaving this setting on could keep the file size of the database to a minimum, but it could also result in corruption if the operation is interrupted in the middle of the C&R process. Instead, it is a more common practice to check the file size of the FE and only set the "Compact on Close" option when the file size reaches a predetermined threshold.

Setting the "Compact on Close" option for the FE is a simple matter of executing the following code (as shown in my previous blog):

Application.SetOption "Auto Compact", True

Turning it off would then look like this:

Application.SetOption "Auto Compact", False

However, someone asked me recently how to set the "Compact on Close" setting for the BE from the FE. Obviously, using the above code only works for the FE, so I decided to create the following function. Just pass the filepath to the BE database and an optional True (to set Compact on Close) or False (to unset it) to the function.

Public Function SetAutoCompact(strDB As String, Optional bool As Boolean = True) As Byte
'8/18/2016
'Source: http://www.accessmvp.com/thedbguy
'Sets the Compact on Close flag of an external database

Dim objApp As Object
Dim objAcc As Object

If Dir(strDB) = "" Then
    MsgBox "Cannot find external DB file " & strDB, vbInformation, "Warning"
    SetAutoCompact = 1

Else
    Set objApp = CreateObject("Access.Application")
    Set objAcc = objApp.DBEngine.OpenDatabase(strDB)

    On Error Resume Next
    objAcc.Properties("Auto Compact") = bool
    If Err.Number = 0 Then
        On Error GoTo 0
    
    Else
        objAcc.Properties.Add objAcc.CreateProperty("Auto Compact", dbBoolean, bool)

    End If

    MsgBox "Finished setting 'Compact on Close' for " & strDB, vbInformation, "Done!"
    SetAutoCompact = 0
End If

Set objAcc = Nothing
Set objApp = Nothing

End Function

As previously mentioned, the above function should be used after checking the file size of the BE against a predetermined threshold. Also, compacting the BE over a network connection is not recommended.

As always, any comments are welcome. Thank you.

How to check if ACCDE?

There was a question at MSDN Forums the other day on how to determine if an Access database file was compiled to an ACCDE. I found this question somewhat interesting, because I never had a need to determine whether a database was a true ACCDE file or not.

When I deploy a compiled database, I often rename the file extension to ACCDR to force Access into Runtime Mode. I use the following code to check if the database is running in Runtime Mode and exit the application if it is not.

SysCmd(acSysCmdRuntime)

However, I could not find an equivalent SysCmd constant to check for ACCDE, and I am not sure if there is a property for it as well. So, I came up with the following function, which seems to do the job.

Public Function IsACCDE() As Boolean
On Error GoTo errHandler

Application.VBE.CodePanes(1).Show
IsACCDE = False

errExit:
    Exit Function

errHandler:
    IsACCDE = True
    Resume errExit
    
End Function

I am not sure if it is the best way to perform the ACCDE check, but I just thought I would share it and see if you can tell me if there is a better way. Thank you!

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents of a text file into your database. This task may seem challenging at first because it involves an external file, but it doesn't have to be hard.

The OPEN Statement

There are several methods to read a text file into an Access database. VBA has a built-in OPEN statement, which enables input or output access to an external file. Here's an example VBA code to initialize a text file for input:

Open "C:\FolderName\FileName.txt" For Input As #1

The above code creates an object (#1) with a reference to the text file, which then allows its contents to be read in code. For example, the following code reads a line of text from the file into a variable:

Line Input #1, VariableName

After we are done reading its contents, we need to close the connection to the file with the following:

Close #1

Here's an example code snippet to read an entire text file into memory:

Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String

intFile = FreeFile()

strFile = "C:\FolderName\FileName.txt"

Open strFile For Input As #intFile

Do While Not EOF(#intFile)
     Line Input #intfile, strInput
     strText = strText & strInput
Loop

Close #intFile

Please notice the use of the FreeFile() function to retrieve the next available file number rather than assigning a specific file number with the OPEN statement. It avoids possible mistakes of referring to the wrong file in case you are trying to manipulate multiple files at the same time.

The File System Object

My preferred method to read a text file into Access is to use FSO (File System Object). It adds an external reference (overhead) to the application, but I think the process will run a bit faster, and the code will also be a bit cleaner.


For instance, compare the following function, using FSO, to the one above, using the OPEN statement:

Public Function ReadTextFile(strFile As String) As String
'3/10/2016
'http://accessmvp.com/thedbguy

Dim fso As Object
Dim objFile As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strFile) Then
     Set objFile = fso.OpenTextFile(strFile, 1) '1=ForReading, 8=ForAppending
     ReadTextFile = objFile.ReadAll
End If

Set objFile = Nothing
Set fso = Nothing

End Function

The above function uses "late binding," so a reference to the File System Object is not necessary. However, you can use "early binding" if you prefer.


I hope you find the above methods useful. Please do not hesitate to let me know which method you prefer and why (or why not the other). Cheers!

Extract Data Using Regular Expressions

Earlier, I wrote this article for extracting e-mail addresses from a memo field using regular expressions. What it was able to do is return all strings matching a particular pattern. Recently, I had a request to enhance the code to return all strings "inside" a particular pattern. See the discussion thread at UtterAccess. As a result, the following modified function takes a pattern and uses the Replace method to return only the string inside the "boundary" identified within the pattern.

Public Function ExtractData(strData As String, _
    Optional strDelim As String = ";") As String
'http://accessmvp.com/thedbguy
'2/9/2016
'Extracts data using regular expressions

Dim regEx As Object
Dim regExMatch As Object
Dim var As Variant
Dim strMatches As String

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "\[(\w+)\]"
    Set regExMatch = .Execute(strData)
    For Each var In regExMatch
        strMatches = strMatches & strDelim & .Replace(var, "$1")
    Next
End With

ExtractData = Mid(strMatches, Len(strDelim) + 1)

Set regEx = Nothing
Set regExMatch = Nothing

End Function

To use the above function, make sure to replace the "pattern" identifying the boundaries and the string pattern you're trying to extract. For example, as discussed in the UtterAccess thread, the above function looks for a single word inside a pair of square brackets. You can also use the above function to extract the body of an HTML page by using the following pattern: "<body>(.+[\s\S]+)</body>".

I hope you find the above function useful. As usual, all feedback are welcome. Thank you!

Extract Email Address

This topic is really about Regular Expressions. If you do not know, Regular Expressions are text strings used as a search pattern for matching  a group of characters within a larger string. You might think of them as "wildcard" characters. However, they can do more than just match characters in a string because you can embed "rules" within the search pattern that the search engine will use to evaluate a match.

For example, if you use a wildcard character in an Access query, you might use something like this:

WHERE LastName Like "a*"

In this example, the "*" is a wildcard character that tells the database engine to match all records with last names beginning with the letter "a." But what if you wanted to return all records with last names that begin with the letter "a" or the letter "b?" You might use something like this:

WHERE LastName Like "a*" OR LastName Like "b*"

So far, so good. But what if you wanted all records with last names that begin with the letters "a" through "g?" You would have a very long WHERE clause if you continue with the above syntax.

Enter Regular Expressions

So, to help with that kind of situation, Access allows us to use limited forms of regular expressions in queries. For example, to return records with last names that begin with the letters "a" through "g," we can use the following criteria:

WHERE LastName Like "[a-g]*"

Much simpler, right? However, like I said earlier, the Access' database engine only gives us limited regular expression capabilities. To take full advantage of regular expressions, we'll need to use VBA and an external regular expression engine. Fortunately, there is a library that is available to us as a reference called "Microsoft VBScript Regular Expressions x.x"
To show how powerful regular expressions are, the following code example shows how it can be used to extract an email address from a Memo field. I used this example as a response to a question in the MSDN forums. The user wanted a way to extract the email address from a Memo field that contains data similar to the following:

Lastname: Doe
Firstname: John
Email address: john.doe@acme.com
Company: Acme Co.

First, a disclaimer: I don't claim to be an expert in regular expressions. In fact, I rarely use them. I just know that if the situation is right, they can be a very powerful tool to use. I would encourage you to learn more about regular expressons to enhance your applications.
Here's a code example that uses a regular expression object to look for an email address within a string.

Public Function ExtractEmailAddress(strData As String) As String
'http://accessmvp.com/thedbguy
'6/19/2015
'Extracts the first email address within a string
'regex email pattern source: http://www.regular-expressions.info/email.html

Dim regEx As Object
Dim regExMatch As Object

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .IgnoreCase = True
    .Pattern = "\b[0-9A-Z._%+-]+@[0-9A-Z.-]+.[A-Z]{2,4}\b"
    Set regExMatch = .Execute(strData)
    If regExMatch.Count > 0 Then
        ExtractEmailAddress = regExMatch(0).Value
    Else
        ExtractEmailAddress = "No email found!"
    End If
End With

Set regEx = Nothing
Set regExMatch = Nothing

End Function

Notes: The above code example uses late binding, and it will only return the first email address found, if any. To see a modified version of the above code that returns all email addresses found as a delimited string, please visit my website.

I hope this short article piques your interest into regular expressions, and perhaps, I will be learning a thing or two from you very soon. Thank you for reading.

Original post date: June 28, 2015

Requested type library or wizard is not a VBA project

Do you use Navigation Forms in your database projects and have seen this error message?



If so, please read on to hear about my experience running into this issue and learn what I had to do to fix the problem.

After avoiding to use Navigation Forms since it's introduction with Access 2010, I recently found myself without any choice because our IT department decided to disable all custom ribbons on our network for security reasons. Please don't get me wrong, I personally don't think there's anything particularly wrong with Navigation Forms. On the contrary, I think they are a huge improvement over the old Switchboard, but I never used the Switchboard much either (only on occasions). So, when the custom ribbons came out in Access 2007, I thought it was a better use of space for a menuing system and decided to design all my user interface using custom ribbons. However, when our IT system was recently upgraded to Office 2010, my Access 2007 custom ribbons stopped working because of our new network security configuration. So, I decided to give Navigation Forms a try.

There may be other instances where the subject error message can occur, but I ran into it when I decided to use a Navigation Form with 2-Level Horizontal Tabs.



Now, please note that just by creating a Navigation Form with 2-Level Horizontal Tabs won't necessarily produce the error mentioned above. Instead, this problem may only reveal itself when you compile your project into a ACCDE file. In other words, you might not realize that there's a problem while testing your project using the full version of Access until you deploy a compiled version or your database application to your users.

What is causing this?

Interestingly enough, some people may not even run into this issue at all. What causes this problem is when you are using any VBA code behind your Navigation Form. For example, you'll run into this problem if you have a Load Event procedure to hide certain tabs depending on the user's security level or if you have an Open Event procedure to change the label captions on your Navigation Form. Unfortunately, just deleting all the codes will not be enough. The mere fact that you have the "Has Module" property set to "Yes" will also cause this problem.



So, how do I fix it?

Of course, the easy answer is to just set that property to "No." And when you do that, Access will give you this warning, which you'll need to pay close attention to:



As you can see from the bolded part of that warning message, setting the Has Module property to No will "delete" all the VBA code you have behind that form. So, if you have any code behind the form, I suggest that you save them outside of the form for future reference before you change this setting.

If you *must* have some code run behind your Navigation Form, I recommend that you use Macros instead. You can take all the VBA procedures you removed from the form and convert them into macros to be used in your Navigation Form. Using macros to execute your form logic will not produce the error that using VBA procedures does. Unfortunately, there is no utility to convert VBA into macros, so you'll have to do that process manually.

Like I said earlier, I am not sure what other situations could result in this error, but I hope the solution presented here will somehow help fix those problems as well.

Original post date: May 27, 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

SimpleCSV()

I know 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 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 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

Original post date: August 21, 2013