Microsoft Access Code Snippets and Other Useful Bits

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)

Conclusion

These are but a few of the important functions that I keep around but space
doesn’t permit me to describe them all. Access developer friends of mine could
add to the list, as probably could most readers of this article. Virtually
everything above was, at one time, a gift from someone who answered my cry for
help in a newsgroup post. I owe a debt of gratitude to them all. Happy coding!

»


See All Articles by Columnist
Danny Lesandrini