DBA > Articles

Microsoft Access Code Snippets and Other Useful Bits

By: Danny Lesandrini
To read more DBA articles, visit http://dba.fyicenter.com/article/

Over the course of some years, a programmer collects useful code snippets into a library for reuse. Danny Lesandrini shares a few of his indispensable Microsoft Access code snippets, a few interesting ones and one dangerous one.

Over the course of some years, a programmer collects useful code snippets into a library for reuse. In this article, I cover a few indispensable ones, a few interesting ones and one dangerous one. Below is a list of the functions included in the download. We'll review a number of them in the article that follows.

* * GetMachineName GetLoginName
* LinkTablesMDB
* LinkTablesSQL
* ForcePause
* IsLoaded
* ParseObjectName
* SaveForm
* LoadForm
* CompileModules
* EditControls
* Zoolander (CopyOverMe)

Computer and Login
GetLoginName() is a function that qualifies as indispensable. If you're going to do any auditing or personalizing according to user, you'll need to know who the user is. This function grabs the Windows User Name for the current login session. Closely related but less useful is the GetMachineName() function, which retrieves the name of the computer on which the code is running.

Since these functions get their data from the operating system, a Windows API call must be made. To do this, you need to add the following declarations to the module where the VBA functions will be created. They are long and clumsy and will no doubt wrap oddly in the HTML of this page. Just paste them and don't worry about what it all means.

Private Declare Function apiGetComputerName Lib "kernel32"

Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Private Declare Function apiGetUserName Lib "advapi32.dll"

Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Next, you create an Access function wrapper to implement these API calls. This code, too, is a little confusing and frankly I can't explain it to you. I might have been able to a dozen years ago when I first copied and used it myself, but suffice it to say that it works.

Public Function GetComputerName() As String
On Error Resume Next
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen) If lngX <> 0 Then
GetComputerName = Left$(strCompName, lngLen)
Else
GetComputerName = "Unknown"
End If
End Function
Public Function GetLoginName() As String
On Error Resume Next
Dim lngen As Long
Dim lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngen = 255
lngX = apiGetUserName(strUserName, lngen)
If lngX <> 0 Then
GetLoginName = Left$(strUserName, lngen - 1)
Else
GetLoginName = "Guest"
End If
End Function

You can test these functions from the Immediate Window. If not already open, press Ctl+G and type the names of the functions preceded by a question mark (VBA shorthand for "Print") and executed by pressing the ENTER key. When I do this, I get my login name, dannyl and my computer name, PHARMATECH007.

?GetLoginName
dannyl
?GetComputerName
PHARMATECH007
Link Tables

If you have split your data off into its own MDB file or if your data is in SQL Server, the Access application must link to the data. It's always good to have a relinking function and there are a couple of ways to do that.

First I'll discuss what's labeled LinkTablesMDB(). This method identifies the tables one at a time, deletes the existing link and recreates it using this DoCmd.

TransferDatabase.
When I use this method, I usually keep a list the tables to be linked in a local table. I build a recordset of table names and loop through it, deleting and recreating the table links. In the download, instead of using a recordset, the TableDefs collection is walked and each linked table is first deleted and then recreated. The problem with this approach is that if the recreation fails, the delete cannot be rolled back and in effect, the system "forgets" it was ever linked to that table. That's why keeping a list of tables to link to is important if this method is employed.

A safer way to accomplish the same thing is to simply refresh the connection property of the linked table. This way, if it fails, the table link remains. It may not be pointed to a valid data source but it serves as metadata and remains intact for future link attempts.

I use this method when linking to SQL Server because it's very intuitive. You begin with a connection string property, which identifies the SQL Server, the Database and the user credentials. Next, you again loop through the TableDefs collection and process all tables that have a non-null connection string property. Simply assign the correct string to the property and refresh the TableDef link.

strConn = "ODBC;DRIVER={SQL Server};SERVER=SANDBOX; " & _
"DATABASE=MOM;UID=PharmUser;PWD=*******" For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = strConn
tdf.RefreshLink
End If
If Err.Number > 0 Then
MsgBox Err.Description, vbInformation, "Error"
LinkTablesSQL = False
Err.Clear
End If
Next

While I don't show it in this snippet, this process may also be used to refresh the connection string of Pass Through queries in Access that point to SQL Server using an ODBC connection. If your data points to an MDB file, then the connection string will look like this, and the code is identical:
strConn = " ;DATABASE=C:DevelopmentTargetDatabase.mdb"
Object Functions
It used to be that Access objects would get corrupted for no particular reason at all. When that happens, there are a number of things one could do to recover the object and this technique is my favorite. For forms and reports, you can save them as text files, delete the offending object and reload it from the text file you just created. The commands to SaveAsText and LoadFromText are shown below and are very simple. For some reason, doing this can sometimes correct strange issues with forms and reports.

Application.SaveAsText acForm, sForm, "C:" & sForm & ".txt"
Application.LoadFromText acForm, sForm, "C:" & sForm & ".txt"

If you like to clean up things like label names and other form or control properties, then the following functions will be of interest to you. I use this code often because it saves me time. In the example below, all the labels of a report are examined to see if they are named according to the default for new label controls and if so, it renames them.

Access, by default will name them Label0, Label1, etc. This code uses their caption text to create a meaningful name. It removes the spaces and colon characters and trims the string to a manageable 32 characters. One could just as easily use this code to set the label's fore color or to set other control or form properties. As I said, I use this a lot.

Public Function EditControls(ByVal sReport As String) As Boolean
On Error Resume Next
Dim rpt As Access.Report
Dim ctl As Access.Control
Dim strName As String
DoCmd.OpenReport sReport, acViewDesign
Set rpt = Reports(sReport)
For Each ctl In rpt.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, 5) = "Label" Then
strName = Replace(ctl.Caption, ":", "")
strName = Left(Replace(strName, " ", ""), 32)
ctl.Name = "lbl" & strName
End If
End If
Next DoCmd.Close acReport, sReport, acSaveYes
End Function

Another function I included in the download but will not reproduce here is the ParseObjectName() function. It's really just string manipulation, splitting a camel case name into a human readable string with spaces. Below are some examples of how it converts object names to readable strings:

tblCustomers ==> Customers
qryCustomersForTheCurrentYear ==> Customers For The Current Year
tblCustomerOrderDetail ==> Customer Order Detail

It's not earth shattering code but nice to have around when you need it. Zoolander

Finally, we come to my favorite, the Zoolander code. I call it this because it reminds me of that scene in the movie Zoolander where Derek is facing off with Hansel on the runway. Hansel, I believe, performs a trick where he removes his underwear without taking his pants off. Very dangerous!

Well, this code is equally dangerous but fun. In short, it allows you to copy over the file you are currently running with a different file. In other words, say you want to perform an update on the user interface, client portion of an application. With the click of a button, you can replace the current Client.MDB with a new Client.MDB while it's running the code that does the replacement.

In simple cases, this will work without any issues. It's a fast and simple way to allow the user to click a button and get the latest version of an application. However, I ran into problems with applications that used 3rd party controls or linked to MDB library files. They simply became corrupt and had to be manually replaced. Thus, I stopped using this code myself but I keep it around for emergencies … and so I can relate the Zoolander story.

Again, this requires a Windows API call to pull it off. Below is the API declaration, formatted for easy reading. Once that is included in the module, you can call it with the proper arguments: [Source File], [Target File] and [False] to force success even though the file already exists.

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long lngResult = apiCopyFile(strSource, strTarget, False)

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/