Skip to main content

Bill Mosca's Microsoft Access Database Tools, Code Samples and more for the serious developer.
Home
Access Basics
How To's And Articles
Utilities and Add-Ins
Code Samples
Recommended Books
Reviews
Blog, blah, blah
Misc Downloads & Links
Contact Us
About Us

How To's and Articles

 

I've written these in-depth articles and shorts because just posting the code wasn't enough. The most read article is the first one.

 

 

Deploying and Updating Front End Database Applications

 

 

User Level Security Not Behaving? How to Fix It

  SQL Server Native Client Driver Bug With nvarchar(max) 

 

 

 Leading Dot List

 

 

 Combo Box List Shows Description Only but Text Area Shows Key

  

 Code That Macro

 

 

 SaveAsText/LoadFromText

  

 Build String From MultiSelect ListBox

 

 

 Controlling The Controls On A Custom Toolbar

 

 

 Saving an Email Attachment

 

 

 Using a Value List For an IN Operator of a WHERE Clause

  

 Working with SQL Server Linked Tables: The Hidden Gotchas

 

 

 Let Your Users Do The Sorting

 

 

 Back End Protection

 

 

 Display Database Information

 

 

 Changing a Form's Color Scheme

 

Deploying and Updating Front End Database Applications

 

In the preferred multi-user database set up, the database is split. The tables are contained in what is referred to as the Back End (BE). This file lives in a shared folder to which all users have read/write/edit/delete permissions. This allows each user's instance of Access to create, update and close the record locking file. All other objects (forms, reports, macros, queries, code modules and possibly local static or personal preference tables) are kept in a separate database file called the Front End (FE). Each user has his own copy of the FE on his local hard drive. The FE links to the tables in the BE.

Allowing more than one person to access the FE is a big Bozo No-No. It leads to database corruption and dramatically slows down performance.

Got that? Good!

So now I bet you are saying, "Oh, sure! I've got 132 users, and FE design changes happen almost every week. New reports are added, more functionality is required and who knows what else they want. How can I possibly keep up on deploying new FEs to my users? I don't even work the same shift as some of them!"


Not to worry, Bunky! There are several simple ways to make sure your users always have the version of the FE. One way is to use Tony Toews' Auto FE Updater. You can find it at http://www.granite.ab.ca/access/autofe.htm.


But I prefer my own way of doing things so I wrote a VBScript that does all the work. In fact, you don't even have to deploy the FE at all. Just give each user a desktop shortcut or a hyperlink in an email to the VBS file once. They double-click the shortcut and all the necessary files are downloaded right to their hard drives. When you make a change to the FE all you do is copy it to the shared folder, change the date in the name of a text file whose only purpose in life is to act as the version identifier and change that text file's name in the VBScript.


Is that simple enough?


But I'm getting ahead of myself. Click here to see a sample and we'll get started breaking this all down into digestible pieces.


The script has a two-fold purpose in life. Actually, three if you count making your life easier. The first is to do the initial FE "installation". The second is to check for updated versions download them and swap out the old version.


At the very top of the sample, you'll see the script's header. Use this to document what's going on in case you get hit by a bus and someone else has to take over. You might even want to include this article's URL. I wouldn't mind a bit. The script contains a short list of instructions, but I'll go into it in detail. You'll probably notice right away that VBScript is a lot like VBA so it should be pretty easy to take the plunge and start writing your own scripts. I regularly use Access's VBA editor to write my scripts. They sometimes have to be changed a bit once you put the code into a .vbs file, but why not take advantage of the editor's features? When you are finished with your script copy it into NotePad or any other text editor. Save it with a vbs extension. NotePad will add a txt extension unless you select All files (*.*) as the file type. If that hppens fix it in Windows Explorer.


Note the list of constants on the next lines. These are all global to the script which saves a lot of time when it comes to editing the values. Note also that I never declare the data types. Now you VBA coders are saying, "That is just plain sloppy coding. Shame on you!" To which I say, Ha! You can't declare data types in VBScript. Everything is a variant. As I mentioned, VBScript is a lot like VBA, and some differences are sneaky ones.


I use Option Explicit at the top of the code so all variables must be declared. VBScript is tough enough to debug without having to fight typos, too.


The Constants:
cTXTFILE is the text file that the script uses to determine the latest version. More on that to follow.

cSVRPATH is the path to the server where the FE file lives. It's also where you will put all the dependent files you need like the database's icon, any DLLs you use, images, etc.

cLOCALPATH is the path to the user's copy of the FE. It will also be the path for those other files mentioned above.

cFE is the name of your FE (duh).

cZIP is the name of the zip file in which you put everything so the download doesn't put your users to sleep while they wait for it to finish. Some of the places in which I've worked have T1 connections to their outer lying offices. Downloading a 2 mg file is far better that a 20 mg file.

cICON is the name of your application's icon file for the desktop shortcut to launch the script.

cSCRIPTNAME is the name of your script. You're probably wondering why you would need the name of the script within the code. Be patient. You'll find out.

cSCNAME is the name of the local shortcut to open the FE.

cANIGIF is the name of the animated gif file that will be used so the user doesn't think the whole process hung. Remember the T1 cable I told you about?

cAPPNAME is the user-friendly name of the application and will be used for the desktop shortcut. It is important to remember that if your paths or file names contain spaces, there are places where you will have to use double-double quotes (a string of four double quotes) or Chr(34) on both ends when using the constant. Chr(34) is a double-quote and when concatenated to a string it is read as a literal.


The double quote thing isn't necessary with the FileSystemObject, but it is with the shortcut Target and Aruguments. I've tried to find all the necessary places and do it for you, but if you get a "bad file" or "file not found" error that will probably be why. And the only way you will find out is if you comment out the "On Error Resume Next" lines where it looks like it might be a problem and run the script until it errors out.


So from all that, you can guess that I never put spaces in my folder or file names. The only place I get overruled is with the Program Files folder.


The next three globals are variables used throughout the script. I'll go over them as we come to them.


Before we start going through the code, I need to tell you a couple things. There is only one VBScript file. It lives on the server. That means you only have to edit one script. The user has a desktop shortcut that launches the script. As I said earlier, the script does all the deployment tasks for you.


Scroll down to the very bottom of the script and you will see the starting point. Since we have several procedures, it's only fair that we have to tell the script where to start. Just because it's a common practice in other languages to use "main" as the name of the first procedure, that's what I called mine, but you can call it whatever you want.


Let's look at what is going on in main. This procedure calls several other procedures in the proper order. Main first calls a procedure to make a splash screen. The splash screen gives the users something to look at and let's them know that the application is going to open. If you don't do that, and your application takes a while to download, Joe Accountant might think nothing is happening and click the desktop shortcut again and again and again...


Hopping up to the function named "SplashBox" at the top, we see an Internet Explorer object is created. This is what your user will see:

 

 

I used Tom Lavedas's code to create an IE window because there is no native form in Windows to use in this way as far as I know.


You'll spot a few constants in this function: The application name is used on the window, the server path and animated gif path are used to display the gif so the user can see something is happening.


Now we get to the workings of this script. A WSHShell object is created to be used later. A FileSystemObject is also created. This is what we will use to do our file stuff. Its first job is to check for the text file that is used to determine if the user has the latest version. This is done in a very simple way. The text file has a date suffix added to it. In this example, the text file is named "ProjectMgmt_feV20080815.txt". The date is yyyymmdd. Use whatever format you are accustomed to. This file name says August 8, 2008 was when the last version of the FE was placed on the server for deployment.


If the user had previously downloaded that version, he would have the same text file on his hard drive. A new download probably isn't necessary, but we check to see if the FE also exists in the folder. It might have been accidentally deleted, or if the user needed a new copy to replace a bad one all that need be done is to delete the FE or the text file.


If either file is missing delete the text file. GetDB is called. GetDB checks to see if the folder exists. If not, it creates it using the cLOCPATH constant. Then the text file and the FE zip file are copied from the server. I suppose the text file could be put in the zip file, too, but the file is so small there really is no need to do that especially when you will be renaming it with every version update.


Once we have the two files we need to unzip them. I chose to use the built-in file compressor that comes with Windows XP because all my users have XP. The procedure named Unzip is called from GetDB, passing the local folder path and zip file as the first argument and the local path as the second argument.


Unzip creates a Shell.Application object and uses its Namespace to handle the files within the zipped folder. It goes through each file and if the file already exists in the local folder it is deleted from that folder and the new one replaces it. I'm not sure if deleting the files is necessary, but I ran into occasional errors when I did not.


After the files are unzipped, the zip file is deleted. No use cluttering up someone's hard drive. Maybe you are wondering why I just didn't unzip from the server to the local drive. Remember about the slow network connection? If I could quickly unzip from server to local I wouldn't even need a zip file. The whole point is to speed up the file transfer.


Then the temp file that the compressor uses is deleted. Again, this should not be necessary, but I found I was growing a bunch of temp files that were not getting deleted by the system.


Almost done! Now we'll make a new local FE shortcut. I did this because you never know if a user upgrades his version of Access. If you left the shortcut that targets Access 12 in the folder and there is an upgrade to 2007 the shortcut will no longer work.


If the procedure that makes the new shortcut fails, the user is warned that something is rotten in Denmark and the script cleans up pointers and exits. It would be nice if VBScript used error trapping like VBA, but alas, it does not. Just as there is no "try" for Luke Skywalker, there is no GoTo in VBScript.


Next we make a new desktop shortcut. I did this just in case the script has to be moved to a different location. If we let the original script make the change to the target of the shortcut, we can put the new script anywhere we want and eventually delete the original script. Sure, you might miss a few users who rarely use your application, but a few manual changes are better than a hundred, right?


As we near the finish line, we run the local shortcut that opens the FE. All that's left after that is to clean up the object pointers and we're done.


When you kick this all off you will have to have this minimum list of files in the server folder:
1. The text file with the date suffix in the name. I usually put something like this in the file itself. "Please do not move or delete this file. It is used to determine if you have the latest version of ."
2. The zip file that holds your FE and any other files you need the user to have such as DLLs, icon files, images your app might use, etc.
3. The all-powerful VBScript.
4. Your animated gif (if you choose to use one).
5. The desktop shortcut that you give the user (might as well keep it where it is handy). Put your copy of the latest FE in the zip file. Update the date in the text file name. And you're done.


While it took me a couple thousand words to explain all the ins and outs of my little updater going from the first install to keeping the latest version at your users' fingertips, hopefully it will make your life a whole lot better by letting the script do your version deployment for you. Heck, you don't even have to be in the office when all this happens.


Let me know how it works for you. Just drop me a line at the Contact Us page.

 


User Level Security Not Behaving? How to Fix It 

So you decided to delve into Access User Level Secrutity(ULS) for Access 2003 or earlier? Good for you! While ULS is not bulletproof, it does give you a great way to manipulate what the user can see and do based on his user group. We'll go into that topic in another article.

 

Newbee:
But something went dreadfully wrong. I followed the Security wizard, took away "Admin's" rights and gave that user a password. I took away all the "users" user group rights. I set the permissions for each object type for each group. But for some reason, now every time I open any Access database I have to log in.

 

Here is what might have happened:
You used the default system workgroup file on your PC when you set up security. Because all installations of Access create a default system.mdw, everyone will have a work group file different from yours even though the files have the same name. Theirs will not have a password for "Admin", and Admin will still have full permissions to the database. as will the Users default group. For this reason alone, you should never, ever use the system.mdw file for securing a database!

 

How to fix it:

Go back into the database. Click on Tools>Security>User and Group Accounts. Select Admin. Clear his password. Next, put him back in the Admins group. Now close the database and re-open it. You won't be prompted for a log-in now because Admin is the default user. You might as well go ahead and remove all the custom user groups and user accounts you set up. The only groups you want to retain are Admins and Users. The only account should be Admin who is a member of Admins and Users groups.

 

Now your system.mdw file is back to normal which is what you want. Now you can do it the right way. Find where it is and write down the path. You will need to find it again to re-join it later. Mine is in C:\Documents and Settings\BMosca\Application Data\Microsoft\Access. In case you forgot, it's named System.MDW

 

Open the Workgroup Administrator. If you are using Access 97, there should be a shortcut on your Start menu in the Office 97 menu group. If you are using Access 2000, you'll have to hunt for it. Look in C:\Program Files\Microsoft Office\Office\1033 for a file named WRKGRADM.EXE. Double-click it to open it. If you are using Access 2003 you got lucky again because you can find it in Access. Open Access 2003 and click on Tools>Security>Workgroup Administrator.

 

Create a new workgroup security file (.MDW). I usually give it the same name as the database because I deal with hundreds of different databases all with their own MDW file. And it should be placed on the server in a shared folder that your users have Create/Edit permissions.

 

Make sure you write down the user name, file name, and workgroupID. You will need that information should you have to recreate the MDW file. The last step should bring you back to the Create/Join box of the workgroup administrator wizard. Select Join.

 

Now open Access and start up the security wizard again. When it asks you if you want to create a new one or modify the current one, select the current one. Next, choose "I want to create a shortcut to open my database". That is the important part. That is what will prevent that nagging login for every database you open whether secured of not.

 

Now go through the steps of securing the database. I won't go into that here because Microsoft's Security FAQs article at http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp does that for me. Read it before starting over. You might even want to download a copy of the article from http://support.microsoft.com/kb/207793/en-us and keep it handy.

 

Now your database is secured and the users have to be in your workgroup security file to use it. But you are not quite finished. Open the workgroup administrator again and browse to the default System.mdw. Join it. Close the workgroup administrator. Create a shortcut to your database. It should have a Target that starts with Access and includes the /wrkgrp switch. It will look something like this (all one line and quotes around paths that contain spaces):

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" /wrkgrp \\MyServerName\MyFolder\MyWrkgrpFileName.MDW

 

The Start In line is your Office folder:

"C:\Program Files\Microsoft Office\Office11"

 

The /wrkgrp switch forces the database user to temporarily join the workgroup. When he closes Access, he will be back in the default System workgroup file and won't have to log in to unsecured databases.



SQL Server Native Client Driver Bug With nvarchar(max)


I've been using the plain old SQL Server driver (SQLSRV32.DLL) for linked tables because so far it's worked without a hitch on a SQL 2005 or 2008 back end and an Access 2003/2007/2010 front end. Also, it seems to be on all my users' computers so it one less DLL to install. I guess I've been really lucky because I haven't had any issues with this old driver.


However, wanting to keep up with current drivers in hopes of avoiding connection problems, I used the SQL Server Native Client 10 (SQLNCLI10.DLL) on one of my projects. There was a need for some rather large memo-type fields in one of my tables so I used the nvarchar(max) data type. I got some really weird results. 


The longest entry in a table was 2569 characters long. The last 18 characters were all that showed in the linked table. Nothing else! I created a query and ordering by the column's length using len() gave me the right sort based on the actual length in SQL BE even though the resultset of the query showed the length of what showed in the FE table. Fields less than 255 characters were also truncated to the last handful of characters. For example, a 250-character string was truncated to the last 15.


Why this is going on I haven't a clue. But it is surely something to watch out for in your Access applications with SQL tables.


Leading Dot List

I occasionally get a request to print out a list of phone numbers from a
database. The format is:

Company Contact...............................................(123)555-1234
Company2 Contact2............................................(555)555-2222


The company name and contact are left-justified; the phone number is right-justified. Since the company and contact strings vary in length there is a varying number of dots between them and the phone number. You could use a few lines of code to determine the number of dots needed by getting the length of the 2 other strings, but you really don't have to do it that way.

Place a text box in the detail section near the right margin. That's going to be our phone number. Then place a text box that stretches from the left margin to the left edge of the phone number box. Put this as the ControlSource:
=[CompanyName] & " " & [Contact] & String(120,".")

The String function will put 120 dots concatenated to the rest of the string (Company and contact). Set the can grow and can shrink properties for this box to no. All the dots will print, right up to the right edge of the text box. Any extra dots will not show since the box cannot expand.

Play around with the number of dots you need. Depending on the length of the line, you might not need as many or perhaps more.

 


 

Combo Box List Shows Description Only but Text Area Shows Key
A client of mine wanted a combo box for selecting US states. She wanted the drop down list to show the state but the text area of the box to show just the 2-letter abbreviation.

To do this, we used a table of US states and their abbreviations as the primary keys. The combo box used that table as the RowSource.

Next we set the column count to 2, the first column being the abbreviation and the second as the state name. The abbreviation is the bound column.

Normally, you want to hide the key from the user by setting the bound column width to 0 and the readable column to whatever fits best. But that would mean the text area of the combo box would show the state name instead of the abbreviation.

Setting the column widths to 0.0098"; 1" would show the state name, and the abbreviation would be too small to show in the list. But by giving the first column a width greater than zero and being the bound column would force it to be in the text area instead of the state name.

 


 

Code That Macro

If you are new to Access development, you probably rely heavily on macros. While they are easy to create and run, they are also had no way to trap errors until Access 2007 came along.

Up to and including Access 2003, if a macro runs up against an error, the best it can do is come to a stop and show a dialog box to the user. At that point, the user has to decide if he/she should skip the step or stop the rest of the macro from running. How is the user supposed to know what to do?

The best method is to use VBA which gives you a way to trap errors and handle them for the user.

You can turn a macro into code rather painlessly even if you don't know a thing about VBA. Select the macro in the database window. Click on Tools>Macro>Convert Macros to Visual Basic (for Acc 2007, select the macro, click the Database Tools tab on the Ribbon, then Convert Macros to Visual Basic. You will have the options to include error handling and comments. I suggest you opt for both. After all, we're doing this to handle errors.

A new module will be created named something like "Converted Macro-". The code window will automatically open when the conversion is finished, and you will see the code. If not, look for the module in the database window or Navigation Pane and double-click it to view the code. The new function will have the same name as the macro. At this point you should rename either the macro or the function. It's bad practice to give 2 or more objects the same name.

To run the code, create a command button on a form and set its On Click property to the name of the new function. It should look something like this:

=Delete_All_Data()

Another method is to type [Event Procedure] on the On Click property line and then click the little builder button to the right of the line. That will take you to the button's Click event. The Sub will look like this:

 

Private Sub Command276_Click()

End Sub


Type a call to the function between those two lines of code so it looks like this:

 

Private Sub Command276_Click()
Call Delete_All_Data

End Sub


Granted, the code created during the conversion uses old syntax, and there might be a better way to write it, but it gives you an idea as to how to write VBA. With a bit of reading and asking questions in Access groups like mine, you will soon be able to write your own "macro code".


 

SaveAsText/LoadFromText

There is a couple of undocumented methods belonging to the Application object that can come in very handy: SaveAsText and LoadFromText. Both of these are hidden methods so they do not show up in the IntelliSense context menu unless you unhide them.

 

To unhide these methods, open the object browser in the code editor by pressing F2. Right-click anywhere in the browser and select Show Hidden Members. That will expose them not only in the browser but in the IntelliSense menu as well.

 

Now close the browser and open the Immediate Window by pressing Ctrl+G or clicking the icon in the toolbar.

 

The arguments for Application.SaveAsText() are:

  1. the object type
  2. the object name
  3. the file path and name to save as

 

So if I had a form named frmCustomers I could save it to my folder Databases on my C: drive in the Databases folder like this:

Application.SaveAsText acForm,"frmCustomers","C:\Databases\frmCustomers"

 

To load it into any database, all you need to do is open the database and use the LoadFromText method like this:

Application.LoadFromText acForm,"frmCustomers","C:\Databases\frmCustomers"

 

Note the parameters are the same, except this time the file is the existing file.

 

"So what's the big deal?" you ask. I'm glad you asked. When you save a form as text, it instantly becomes very portable. You can load it into another database or save it on a USB stick or disk (it will be very small as compared to saving it in an Access database). And there is one more feature that makes these methods very useful. As you develop a form or report, the code page gets more and more fragmented as you edit code, add or delete controls, rename controls, etc. By the time you are finished, that code page is like Swiss cheese and just begging to corrupt. SaveAsText effectively removes the holes. All you need to do is save the object as text, delete the original object, compact your database and then use LoadFromText to bring it back into the database.

 

That method for cleaning a semi-corrupt object is much safer than decompiling the entire database (another undocumented practice that can just as likely do harm as good). And guess what! There is a very handy add-in made by Dimitri Furman that makes all this even easier. All you do is open the add-in within the database and select the object(s) you want to save as text. Then click a button and it's done. You can just as easily load the objects with the same add-in. Go to my Misc Downloads and Links page and look for Objects As Text to find a direct link to the file.

 


Built A String From A MultiSelect ListBox

Using a Listbox to filter a form or report or even a recordset for data manipulation is pretty straight-forward. Same as using a combo box, right? Yes, unless you want to use the MultiSelect property of the ListBox to filter for more than one value. To do that, you will need to build a string from the selected items in the list.

Here is a simple example of using a MultiSelect ListBox to filter a form. I'll use Northwind's Employees form. Any version of Northwind will work, including the Access 2007 accdb file.

Place a listbox in the Form Header section. Name the ListBox lstEmployees. Set the MultiSelect property to Extended. You could use Simple, but Extended has become the most common type in Windows programs. It lets you use the Ctrl key to select multiple items not in sequence, plus you can use the Shift key to select a series of items. You can even use a combination by selecting a series and then non-sequential items. Truly a wonderful feature! (Boy! I gotta get out more.)

Next put a command button named cmdFilter in the Header right next to the ListBox. This is the button we will use to run the code to filter the form.

Now we're ready to write the code. Actually, I'll write it. You'll just copy and paste my work. If you don't have a standard module in your database add one now. Call it whatever you want as the name is unimportant..to me anyway.The function can be pasted into your standard module or your form's class module as a private function. I prefer using a public function so all forms can use it. You never know when you might have another form that will need this functionality.

This is the function that will do all the work:


Now we need the code to apply the filter built by our function. The function takes 4 arguments with an optional 5th argument:

  1. ctrl As Control - This is our listbox
  2. intColNum As Integer - The column we want to use numbered from 0
  3. strFieldName As String - The field name. If you don't need a field name just pass an empty string("").
  4. strQualifier As String - The Qualifier needed. Text would use double quote("), Dates a octothorpe (#) These need to be surrounded by double-quotes. For text, I prefer using Chr(34) so I don't have to surround the double-quote with double-quotes...just to hard to read.
  5. [Optional] bolNoFieldName As Boolean

The Optional parameter in the function lets us include the string " IN(" . In this case we want to include that string so we set the argument to to False or leave it out since it will default to false. If we just wanted a comma-delimited string we would set the Boolean to True.

In our button's Click event, the variable strFilter is assigned to the return from BuildList. That variable is assigned to the form's filter and the filter is turned on.

Private Sub cmdFilter_Click()
Dim strFilter As String

strFilter = BuildListMe.lstEmployees, 0, "EmployeeID", "", False)
Me.Filter = strFilter
Me.FilterOn = True

End Sub

I've left out all error trapping in this example. I'll leave that drudgery to you. You would probably want to trap an empty string if bolNoFieldName was set to false.

 


Controlling The Controls On A Custom Toolbar

You can create a custom toolbar in Access 2003 or earlier for your users that has all the buttons on it but still control the users access to objects based on their ULS* user group. This adds another layer of security assuming you have taken away the user's access to the database window.

I have a table I use to determine which buttons to show depending on the current user's user group. It has 3 fields: MenuPK (autonumber - Primary Key), UserGrp(text) and ItemNum(long). The ItemNumber would be the index number of the item on the toolbar. The index starts with 1.

We'll do all this in the Startup form's Load event. First, we'll get the UserGroup for the current user. I always assign a usr to only one group. By planning your groups in a heirachy, you should never have to assign a user to more than one group. Then we will call the function that does the work. It determines which buttons the user should see in the toolbar.

The Load event:


The GetUserGroup function uses the workspace objects.

Here is the function that shows or hides the buttons.

 

Then you'll want a function that shows them all again when necessary.


 


 

Saving an Email Attachment

There are lots of examples on the web for sending emails with one attachment, but what if you want to automate saving that attachment sent to you (on one of your public folders)? Or even multiple attachments? The process is a bit different. This function, using late binding, does just that. You pass the Subject line the sender will be using and the file path you want to save the attachment to.

 


Using a Value List For an IN Operator of a WHERE Clause

Parameterized queries are pretty straightforward. Put a prompt in square brackets in the WHERE clause and when the query runs the user will be prompted for the value.


Ex:


SELECT *
FROM MyTable
WHERE LastName = [Please enter a last name]

 

When the query is run a prompt box much like an InputBox pops up asking the user to enter a last name.

You can even use a control on a form so the user types the last name in the control and that value is passed to the query without a prompt box popping up.

 

For example, let's say the form's name is MyForm. The text box is named txtLastName. If you use the form's control it would look like this:

 
SELECT *
FROM MyTable
WHERE LastName = [Forms]![MyForm]![txtLastName]

 

But what if you want the user to be able to type in a list of names to go in the WHERE clause? If the user typed:
Smith Jones Fox


or even if you instructed the user to separate each name with a comma like this:
Smith,Jones,Fox

 

Both of these methods will fail:
WHERE LastName IN([Forms]![MyForm]![txtLastName])

WHERE LastName IN([Please enter a list of names separated by a comma])

 

But there is a rather tricky way to create a parameter for an IN operator. Using InStr() to compare the field to each value in the string will work.
WHERE (((InStr("," & [forms]![Form3]![txtExclude] & ",", "," & [LastName] & ","))>0));

 

See how we are comparing the return of InStr to zero. If the return is greater than zero, a match was found. In other words if the string were substituted for the parameter it would lool like this:
WHERE (((InStr(",Smith,Jones,Fox,", "," & [LastName] & ","))>0));

 

The string tested is:
",Smith,Jones,Fox,"


The field we are testing is:
"," & [LastName] & ","

 

If anything between 2 commas is found to match the field [LastName] the return would be greater than zero.

You can do the same thing to exclude what is in the string by changing >0 to =0 which would mean no match should be found.

 

But let's take it one step further. It is very common to put a space after each comma just as you would if you were typing a document. In that case, this string:
Smith, Jones, Fox


would parse like this:
,Smith, Jones, Fox,

 

We have to remove the spaces because there is no last name like " Jones" (note the leading space). We can remove the spaces using the Replace() function in all versions of Access starting with 2000. We replace any space with an empty string.


Replace([forms]![Form3]![txtExclude]," ", "")

 

The WHERE clause would then look like this:
WHERE (((InStr("," & Replace([forms]![Form3]![txtExclude]," ", "") & ",","," & [OrderID] & ","))>0));

 

Here again, change >0 to =0 if you want all the last names except those in the comma-delimited list.


 

Working with SQL Server Linked Tables: The Hidden Gotchas

When an Access database nears its size limit (1 gbs for Acc97, 2 gbs for Acc2000 through 2007) you need to either break up the database into two or more files, possibly losing referential integrity (you can't create relationships that span Access files) or move the data to an environment such as SQL Server.

 

Fortunately, an Access database makes for a very good graphical user interface (GUI) to a SQL Server database. You can link to the tables just like you can link to Access tables by using ODBC. When linking the tables initially select the file type ODBC Database and create a DSN file or use an existing one, all with the help of the linking wizard.

 

SQL Server fields are called columns. I will refer to them as such for the sake of clarity.

 

There are some differences of which you must be aware.

 

Yes/No fields are bit columns in a SQL table.
A bit column can be null, zero or 1. In Access, a Yes/No field is either 0 or -1. Bit columns display in Access as 0 or -1. But in this case, what is displayed is not always what is real.

 

The 0 could be 0 or null. Access will choke on nulls in that type of field. You MUST make sure all nulls are updated to 0. In fact, it is a good practice to make a default of 0 for bit columns.

 

The -1 is actually 1. All queries that have a bit column in the criteria that is equal to -1 must be changed to either = 1 or <>0.

 

Any table or view** that is not static MUST have a unique index.
Any competent SQL DBA will tell you every table should have a clustered Primary Key, or, if it is not clustered there should be another clustered index on the table. Tables and views without unique indices are not updatable. The error message you get (if any) is too vague to let you know this.

 

Any design change made to a table or view will not show up in the Access front end.
You must relink the object. If you try to insert a value in the new column through VBA without relinking the value will go into the column that used to have the new column's ordinal position.

 

Let's say you have a SQL table with columns in this order:
CustomerID int IDENTITY(1,1) - Same as an AutoNumber in Access starting at 1 and incrementing by 1
CompanyName varchar(50) - Same as a text (50) in Access
Address varchar(50)

 

Then you create a new column Rating int and put it in the table right after CompanyName. Should you try to update Rating the data will actually go into Address. In this case, there will be no error because whatever alpha-numeric value you enter will be accepted in the Address column provided it is not too big.

 

See the potential for disaster?

 

Sometimes if you change an object used by a view, not only will Access not see the change without a relink, but SQL might not even see the change until you run a DDL command of sp_refreshview @viewname = MyView to update the view's metadata.

 

Relinking a view causes it to no longer be updatable.
When you link to a view Access will ask you to select field(s) that makes a record unique. While you do not have to do this, if you don't, the view will not be updatable. Relinking manually might lose the unique index that Access creates. If you relink through VBA the index will not be kept unless you write code to do it.

 

Using the Format property in a form or report will not always work.
I have had the formatting ignored whether I use the control's Format property or the Format() function directly in the query. I worked around this by using the Format() function directly in the ControlSource as in:
=Format([Amount], '#,###.00')

 

This bug may have been fixed, but if it hasn't at least you will be aware of it if it bites you.

 

DAO code modifications

There are a couple changes you will need to make when working with DAO. You need to include the argument "dbSeeChanges"

1. Recordsets:

Set rs = db.OpenRecordset("SELECT Top 1 * FROM ErrorLog", _
dbOpenDynaset, dbSeeChanges)

2. Executing a SQL statement:

CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

 

Those two are the only ones I've run up against. I'll update here if I find more.


 

Let Your Users Do The Sorting

 

When you create an Access application the requirements usually include lookup tables for frequently entered data. An example of a lookup table would be one with a field for shirt sizes and a field for the primary key (probably an AutoNumber). The primary key would be used as a foreign key in another table, say, an Order Detail table.

 

Keeping the application as flexible as possible, you would want to handle adding new sizes to the list so you code the NotInList event for the combo box to add the new item.

 

Pretty common stuff.

 

But say you have sizes small, medium, large in the table and your supplier suddenly starts making smallium, a size that is between small and medium. The new item is added to the table but now the sort based on the primary key index puts smallium at the end of the list.

 

Your user calls you up, "No, no, no! It should be between small and medium! Fix it!"

 

So you say, "No problem. I'll just add a field with a number so I can force a sort order independent of the primary key."

 

You add a field named SortOrder and fill in the values so everything is in the right order. Then 2 days later the miserable supplier adds medarge. Yup, right between medium and large. GRR! Now your sort order is off again and this time you have to mess with it again. This kind of maintenance you just don't need.

 

Wouldn't it be great if you could just give the user a form with the list and let him click a button to move an item's order? Why not go further and give the access to all lists?

 

The sample database for this How-To is in Access 2002-2003 MDB format. It can be opened in all versions of Access from 2002 (XP) and up.

 

Here is what you need (download sample 2002-2003 format):
1. All Lookup tables have either a prefix or a suffix that says they are lookups. I use the suffix "_lkp".
2. All Lookup tables have this basic structure:
        Primary Key (AutoNumber),
        Item Description (text),
        SortOrder (long)

Actually the SortOrder is the most important thing, but keeping the table structure the same makes life simpler.

 

3. A form with a combobox named cboTableName, a listbox named lstItems, a command button named cmdUp and a command button named cmdDown.

 

Use this as the RowSource for cboTableName:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Name Like '*_lkp' AND MSysObjects.Type IN(1,4,6)
ORDER BY MSysObjects.Name;

 

MSysObjects is a system table that has information about all the objects in the database. I use MSysObjects.Type IN(1,4,6) because that takes into account all tables: local tables, linked Access tables, and linked ODBC tables. You can adjust the RowSource to fit your circumstances.

 

Keep the RowSource for the listbox blank, but be sure the RowSource Type is Table/Query.

 

The AfterUpdate event of cboTableName sets the RowSource for lstItems.

 

Private Sub cboTableName_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT * " _
& "FROM " & Me.cboTableName _
& " ORDER BY SortOrder"

Me.lstItems.RowSource = strSQL
Me.lstItems.Requery

End Sub

The buttons have this code:

Private Sub cmdDown_Click()
Call ReOrderTblSort(Me.lstItems, Me.cboTableName, "SortOrder", False)
End Sub

Private Sub cmdUp_Click()
Call ReOrderTblSort(Me.lstItems, Me.cboTableName, "SortOrder", True)

End Sub


The procedure that does all the work is ReOrderTblSort. It takes 4 arguments:
the listbox, the combobox, the name of the field that sorts (SortOrder), and a Boolean that tells the procedure to move the item up (true) or down (false).

The procedure uses the listbox's column with the SortOrder to find the record in the table. We set a bookmark on that record so we can change the SortOrder of either the record before or after the one we are moving. What is going on is a simple swap of values. For example, The record with SortOrder 3 exchanges values with the record with SortOrder 4.

This swap happens every time the procedure is called. Now all you do is present the user with the form. He selects the table he wants to sort and then selects the item he wants to move. Clicking the appropriate button is all it takes. That means you get one less interruption in your day. Yay!

 


 

Back End Protection

The one bad thing about Access back ends is you can't really lock them down. Nosey users with permissions to read the tables can find the file and open it. ACK! And passwords are pretty much useless. If a user is savvy enough to find the file in the first place he'll probably know about Access password crackers.


There is a way to keep users out. It's not fool-proof (God just keeps making fools smarter and smarter), but at least it is enough to keep almost everyone but developers out.


Create a startup form in the back end. Put code in it to set all database properties to disallow menus, toolbars, bypass key, etc.


Add this after the last ChangeProperty call if you are using Access 2007 or 2010:
DoCmd.ShowToolbar "Ribbon", acToolbarNo


Put a label in the form that says something like "You are not authorized to use this database."


Remove the control box and set the style to Dialogue.


Enable the timer to 2 seconds with the Quit command in the Timer event.
Private Sub Form_Timer()
Quit
End Sub


Open the start-up form once to run the code that locks down the properties.


Now if anyone opens the database, it throws them out in 3 seconds with no way to bypass the form.


So you're wondering "How the heck am I going to get into the back end for maintenance or design changes?"


Not to worry. Use my Properties Setter to temporarily set all the database properties to "Allow".

Note: I haven't finished a 2007 version of this utility yet, but I'm working on it.


When you set all the properties hold the shift key down and click the Open Target button. The back end will open and you can work on it to your little heart's content.


Be sure to run the startup form before closing so the properties are all reset back to lock-down mode.

 

 


 

Displaying Database Information

When developing it makes sense to set up forms based on a template to keep a consistent look and feel for the user. It is a way to add to the flow of the application and tie everything together.


The database Title is one place to start. Putting it in all message boxes is a great way to make sure the user knows the message is from your application and not some other one he might have open.


I'm not talking about the Application Title found in the startup options. You could use that, but if you choose to use the Application Title it will show on the system bar tab. If it is more than a few characters it will be truncated if the user has several applications open. It will also be truncated if you use it as a shortcut name. It's best to keep it short...maybe even just use the initials. I've got one application named Dialysis Clinical Documentation System. That's a mouthful to start with, and it could show as "Dial..." on a crowded system bar tab so I set the Application Title to "DCDS".


But the database Title can be used within the application and be reasonably long without sacrificing any part of it.


Where would you find this property? The database Title can be set by opening the Database information box.
In Access 2010: Click on File > Info > View and edit database properties.
In Access 2007: Click on the Office Button > Database Properties
In all earlier versions click on File > Database Properties


Click on the Summary tab in the Database Properties box, and you will see the Title. It will probably already have the file name without the file extension as the Title. Change it to the name of the application. In my case, it would be "Dialysis Clinical Documentation System" without the quotes.


If you want the title to appear on your startup form put a text box at the top of the form with this as the Control Source:
=CurrentDb.Containers("Databases")!SummaryInfo.Properties("Title")


Now you are probably saying, "That's supposed to be easy?! Are you daft?! That's way too much typing for me to do or even remember every time I want to display the Title. Why can't I just type in the title?"


Well, you could just type in the Title's literal value, but properties such as Title do have a tendency to change during development, and there is no built-in way to just do a Find and Replace throughout the project. And constants need to be wrapped in functions to work in the places you might want to show the title. Constants also limit you to one string. Suppose you also wanted the Company. That would mean another constant and another wrapper function.


But there is a very flexible way to do this. And guess what? You can do it so you can use any of those summary properties in that box... All you do is write a function. That function can be used in any project.


There are several places you might want that database property or maybe another to display. I typically put it into the Title argument of message boxes like this:
MsgBox "Now you've done it! You broke it!, vbCritical, GetDBInfo("Title")


For that text box I mentioned earlier, the control source would be:
=GetDBInfo("Title")


Change the database Title and the change is propagated throughout the project.


Then there are the cool Custom Database Properties. Take a look under the Custom tab of the database properties box. There are lots of useful ones that go good on splash screens or "About" forms. Properties such as Department, Telephone Number (as in Help Desk). But you are not limited to the ones you see in the list. You can add your own just by typing in the name, type and value.


GetDBInfo will work for those properties, too. Did you notice the optional parameter strType? Of course you did. And did you see I set the default for that parameter to "Summary"? If you leave out the second argument it will be interpreted as "Summary". If you pass "Custom" as the argument the function will get the Custom property that you want.


Say you want the Telephone Number. The ControlSource would be:
=GetDBInfo("Telephone Number", "Custom")


Now go forth and customize.

 

 


 

Changing a Form's Color Scheme

Several years ago I developed an application for a bank. One of the requirements was to use the corporate color scheme on all the forms. One thing I found out about banks early on: they tend to spend a lot of marketing budget on logos and branding. After a few years, a new marketing exec came in and decided the brand needed a total make-over. So out went the corporate color scheme and in came a new one.


Redesigning forms to match a new color scheme is not something I like spending my time doing especially when I've got other projects with deadlines looming on the horizon. But corporate politics demand that no trace of the "old, tired" color scheme exist so when a change comes all my nice forms have to get the same make-over.


After re-scheming 4 or 5 forms and looking at the endless list of the remaining forms staring me in the face, it dawned on me there must be a better way to do it.


First, let's start by setting up an enumeration of colors of backgrounds, fonts and so on for the various parts of a form. Access 2003 and earlier makes getting the Long color values easy. Just make one form with the colors specified by the marketing department or by whomever owns the honor. The Color Picker wizard lets you see the RGB values. The Properties box shows the Long value. When 2007 came along the Long value was changed to the Hex value, but you can still get the Long value. Open the color picker, click on More Colors... and then the Custom tab to get the Red, Green and Blue numbers. Use the RGB() function with the numbers you get in the Immediate window.


For example, if the Red, Green and Blue values of a color were 62, 193, and 121 respectively type ?RGB(62,193,121) in the debug window and press Enter. That would return 7979326 which is kind of a lime green.


But a quicker way to get several colors' Long value would be to Press Ctrl+G to open the Immediate Window and type this and press Enter:

?Forms!YourFormNameGoesHere.YourControlNameGoesHere.BackColor

Or use my good friend, A.D. Tejpal's routine included in the sample code. Just pass the hex value as an argument to get the Long value.


The values in the sample code are not really much of a theme. I just pulled some numbers out of the air so don't blame me if they make a really scary form.

Now when the next marketing exec is hired all I need to do is change the color constants to match then new scheme. I included a system colors enumeration if you want to stick with the operating system's color theme.

Here is the code in its entirety:

Take a look at the subroutine named SetDefaultsTheme. That is one that can be made to set a form's default settings (likewise, SetDefaultsSys will set the defaults to system colors). That's handy when you are starting with a blank form. Saving it as your database form template would make sure all new forms looked just like it unless you make changes manually.

You can always add more constants to the enumeration, but keep in mind forms are more appealing and professional when they do not look like an explosion in a paint and sign store.

 


* ULS - Access's User Level Security also called Workgroup Security.

**A view is a compiled query in the SQL database.