How To's and Articles

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 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 <Your App Name Goes Here>."
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.

-top- 

User Level Security Not Behaving? How to Fix It

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.

-top- 

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.

-top-

 -top-

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:

<the object type>

<the object name>

<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.

-top-

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:

Option Compare Database
Option Explicit

Public Function BuildList(ctrl As Control, _
        intColNum As Integer, _
        strFieldName As String, _
        strQualifier As String, _
        Optional bolNoFieldName As Boolean) As String
'Purpose  : Build an "IN" list for SQL Where clause.
'           Qualifier for text or dates.
'           Optional boolean used to determine if IN operator is needed.
'DateTime : 5/15/2008 15:06
'Author   : Bill Mosca
    Dim strList As String
    Dim varItem As Variant

    If ctrl.ItemsSelected.Count = 0 Then
        MsgBox "Please select at least one item.", vbInformation, _
                "No Items Selected"
        Exit Function
    End If

    For Each varItem In ctrl.ItemsSelected
        strList = strList & strQualifier & ctrl.Column0, varItem) _

                & strQualifier & ","
    Next

    'Trim last comma.
    If right(strList, 1) = "," Then
        strList = Left(strList, Len(strList) - 1)
    End If

    If bolNoFieldName = True Then
        BuildList = strList
    Else: BuildList = strFieldName & " IN(" & strList & ")"
    End If

End Function

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.

-top-

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:

Private Sub Form_Load()
    Dim strUserGrp As String

    strUserGrp = GetUserGroup
    Call ViewTbarBtns(strUserGrp, "tbrNavigate")

End Sub

The GetUserGroup function uses the workspace objects.

Public Function GetUserGroup()
    Dim CurrUser As String
    Dim UsrGrp As dao.Group
    Dim wks As dao.Workspace

    Set wks = DBEngine.Workspaces(0)

    For Each UsrGrp In wks.Users(CurrentUser).Groups
        If UsrGrp.Name <> "Users" Then
            GetUserGroup = UsrGrp.Name
            Exit Function
        End If

    Next

    Set wks = Nothing

End Function

Here is the function that shows or hides the buttons.

Option Compare Database
Option Explicit

Public Function ViewTbarBtns(strUserGroup As String, strToolbar As String)
'Purpose  : Hide or show button on tbrPrintRptSnp toolbar.
'DateTime : 2/4/2005 14:21
'Author   : Bill Mosca
    Dim tbr As Object
    Dim btn As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String
    Dim idx As Integer

    Set tbr = Application.CommandBars("tbrPrintRpt")
    Set db = CurrentDb

    'Get recordset for this group.
    strSQL = "SELECT ItemNum FROM tblMenu " _
        & "WHERE UserGrp = '" & strUserGroup & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    For idx = 1 To tbr.Controls.Count
        rs.FindFirst "ItemNum=" & idx
        ' If NoMatch is false it means the itemnum is assigned
        ' to the user group so show it. otherwise hide it.
        tbr.Controls(idx).Visible = Not rs.NoMatch
    Next

    Set rs = Nothing
    Set db = Nothing
    Set tbr = Nothing

End Function

And of course you will want a function to set all buttons visible as you do your developing.

Public Function TbarViewAll()
'Purpose  : Reset Toolbar so all items show.
'DateTime : 2/4/2005 14:33
'Author   : Bill Mosca
    Dim tbr
    Dim idx

    Set tbr = Application.CommandBars("tbrPrintRptSnp")

    For idx = 1 To tbr.Controls.Count
        tbr.Controls(idx).Visible = True
    Next

End Function

 -top-

Saving an Email Attachment

 -top-

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.

 -top-

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  -  Same as an AutoNumber in Access
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.

-top-

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!

-top-

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.

-top-

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

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