This month, Doug Steele shows one way to add shortcuts to your application, demonstrates how to handle embedded quotes in SQL statements, and provides a quick documenter to list the objects in your application.
My applications get installed on users’ computers, and I’d like to make it as easy as possible for my users to start my applications. Is there an easy way to add a shortcut to a user’s computer that will start my application without having to use an installation program?
Shortcuts are just files on the computer (they have an extension of .lnk, although that extension doesn’t show up in File Explorer, even if you’ve told it to show the extension). It’s possible to create the shortcut on your machine and just copy the file to other machines. However, since the path to the application is hard-coded into the link, copying the file assumes that everyone will put your application in exactly the same place. As well, sometimes your users may not be adept at navigating to where their program shortcuts are, so you may wish to automate where the link is placed.
One way to automate the link creation plan is to take advantage of the CreateShortcut method of the WshShortcut object that’s available in the Windows Script Host (WSH). While the objects that I’m going to use here are part of the WSH, they can be used from Access (as this code shows) like any other object installed on your computer (or your user’s computer).
In the example that follows, I’m going to use late binding to invoke WSH. This means you don’t have to set any references to get the code to work (and also minimizes the possibility of versioning problems). Programs that use early binding (picking up their objects at runtime by selecting them in the References list and using explicit object names in Dim statements) run faster than programs with late binding (not using the References list and using the Object datatype in Dim statements) because late binding programs don’t know anything about their objects until runtime. However, installation programs are typically only run once, so speed isn’t a primary consideration. You’ll also note, if you type in my code, that late binding means that you give up IntelliSense support.
The first thing you need to decide is where you want to place a shortcut: on the desktop, in the Start menu, in the Programs menu structure in the Start menu, in the Startup folder, or in any combination of those places. You also need to decide whether you want the shortcut to be only for the specific user, or for all users of the machine.
WSH gives you an easy method to determine where the shortcut needs to be in each of the cases that I mentioned: Just use the WshSpecialFolders collection. For example, to determine where the current user’s Desktop is located, you instantiate an instance of the WshShell object, and then look for the Desktop folder in the WshSpecialFolders collection:
Dim objWshShell As Object Dim strDesktop As String Set objWshShell = CreateObject("WScript.Shell") strDesktop = objWshShell.SpecialFolders("Desktop")
The WshSpecialFolders collection contains objects with the following names. Hopefully it’s obvious what location is pointed to by each object:
- AllUsersDesktop
- AllUsersPrograms
- AllUsersStartMenu
- AllUsersStartup
- Desktop
- Programs
- StartMenu
- Startup
The WshSpecialFolders collection also contains objects for other locations, but I think it’s unlikely you’ll want to put a shortcut in any of them. For the record, though, the other locations are: Favorites, Fonts, MyDocuments, NetHood, PrintHood, Programs, Recent, SendTo, and Templates.
While it’s possible to create a shortcut to an Access application that simply points to the MDB file itself, a “proper” shortcut contains the full path to the Access executable (msaccess.exe) and passes the full path to the MDB file to the msaccess program. This method also lets you pass to msaccess any option switches that you wish.
Putting the full path to the executable in your link could be a problem, since you may not know where your users have installed Access on their machines. Fortunately, Access can tell you where it exists through the SysCmd function by passing the constant acSysCmdAccessDir to the function:
Dim strProgLocn As String strProgLocn = SysCmd(acSysCmdAccessDir) & _ "msaccess.exe"
Now you need the full path to the MDB file that contains your application. One way to determine the complete path to the MDB is to use the Name property of the CurrentDb object:
Dim strDBLocn As String strDBLocn = CurrentDb.Name
If you’re using Access 2000 or higher, you can also use the CurrentProject object:
Dim strDBLocn As String strDBLocn = CurrentProject.FullName
You also may want to determine the folder where the application resides, so that you can set the WorkingDirectory property of the shortcut. If you know the full path to the database, it’s relatively easy to pull the folder out of the MDB’s path:
Dim strDBLocn As String Dim strDBPath As String strDBLocn = CurrentDb.Name strDBPath = Left$(strDBLocn, _ Len(strDBLocn) — Len(Dir$(strDBLocn)))
Alternatively, you can (again) use the CurrentProject object:
Dim strDBPath As String strDBPath = CurrentProject.Path
Now that you know how to determine all of the components necessary to create a shortcut, here’s how to put the pieces together. The following will create a shortcut to the application on the current user’s desktop:
Dim objWshShell As Object Dim objWshShortcut As Object Dim strProgLocn As String Dim strDBLocn As String Dim strDBPath As String Dim strDesktop As String Set objWshShell = CreateObject("WScript.Shell") strDesktop = objWshShell.SpecialFolders("Desktop") strProgLocn = SysCmd(acSysCmdAccessDir) & _ "msaccess.exe" strDBLocn = CurrentDb.Name strDBPath = Left$(strDBLocn, _ Len(strDBLocn) — Len(Dir$(strDBLocn))) Set objWshShortcut = objWshShell.CreateShortcut( _ strDesktop & "\Smart Access 2004-05.lnk") With objWshShortcut .TargetPath = strProgLocn .Arguments = Chr$(34) & strDBLocn & Chr$(34) .WorkingDirectory = strDBPath .WindowStyle = 4 .Save End With Set objWshShortcut = Nothing Set objWshShell = Nothing
When you create the shortcut using Set objWshShortcut = objWshShell.CreateShortcut(…), you’re not only telling WSH where to put the shortcut (the contents of strDesktop in the previous code), but also what name to give the shortcut (“Smart Access 2004-05”).
I used Chr$(34) to put double quotes around the database name, to handle the possibility that there may be embedded spaces in the path to the database. If you don’t do this, Windows will assume that the name of the database ends wherever the first space is in the path.
If you want to learn more about what you can do using WSH, there’s a reference to all of the objects, methods, and properties available to you through WSH at http://msdn.microsoft.com/library/en-us/script56/html/wsoriWSHLanguageReference.asp (or try going to www.microsoft.com/scripting and navigating through the Documentation section until you find Windows Script Host).
I’m building SQL strings in code, but I’m having problems handling text that includes quotation marks.
See the section on Handling Quotations in code here
Is there an easy way to list all of the objects in my database? I know that the Database Documenter exists, but it produces far more paper than I want.
It’s always possible to write code that uses DAO to return a list of objects in the application. Access 2000 and newer versions expose additional objects that make this even easier. Another alternative is to use ADOX to query the catalog. However, sometimes simpler is better.
There’s a hidden system table, MSysObjects, in every database that can be queried to determine a list of high-level objects (tables, queries, forms, reports, macros, and modules). If all you want is a list of objects and you don’t care about, say, what fields are in each table, a query against that system table should suffice.
For example, a query to retrieve all of the tables in the database would be something like this:
SELECT [Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4)<>"MSys") AND (Type In (1,4,6)) ORDER BY [Name]
Why that specific WHERE clause? Tables are stored in the catalog as type 1, and linked tables are stored as type 6. Temporary tables are prefixed with ~, while the system tables all start with MSys.
How can you know what specific type values you need for each object? They’re not documented anywhere; you need to examine the MSysObjects table to be able to figure it out. I’ve listed the relevant values that I’ve determined in Table 1.
Table 1. Object type values in MSysObjects.
Object | Type |
Tables | 1, 4, 6 |
Queries | 5 |
Forms | -32768 |
Reports | -32764 |
Macros | -32766 |
Modules | -32761 |
If you’ve got a query that returns a Recordset of the objects of interest to you, you can use that query as the RecordSource of a report. You can easily create a series of subreports, one for each object type of interest to you, and a report that contains each of those subreports. You also have the option of writing a slightly more complicated query as the RecordSource, and doing everything in a single report. The accompanying Download has examples of both approaches.
By simply importing this report (and all of the subreports, if applicable) into your application, you can easily produce a report of all of the objects in your application. Alternatively, you can write a little application that dynamically resets the RecordSource of each subreport to something like this:
SELECT [Name] FROM MsysObjects IN 'C:\Program Files\MyApp.MDB' WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4)<>"MSys") AND (Type In (1,4,6)) ORDER BY [Name]
You can then query the objects in another database. (Be aware, though, that security can make you unable to connect to the system tables in other databases.)
Which raises another question: Is it a good idea to rely on undocumented features in Access for information?
Long-time readers of Smart Access will undoubtedly recognize the name Michael Kaplan. A few years ago, Michael posted an article about the wisdom of relying on undocumented system tables. You can read what he had to say at www.trigeminal.com/usenet/usenet017.asp. One of the comments he makes is that it’s reasonable to rely on specific existing values within the tables, since changing those values would make it extremely difficult to be able to open older version databases in newer versions of Access. Furthermore, now that Microsoft has essentially stopped development on the Jet engine, I’d think there’s even less risk in using these undocumented values. However, only you can make the decision as to whether you’re comfortable using undocumented features.
Thanks to Jeff Conrad, self-confessed “Access Junkie,” for the original idea.