– Perform a payback analysis
Nothing shows you’re value more then a ROI analysis of your work. If you did your due diligence during the discovery phase you should have a good grasp of the business problem you’re solving, now dig deeper and determine how much money you will save your client with a great Access solution.

– Can’t quantify savings? How about improved customer service? Productivity? Ability to expand?
Don’t short sell yourself if the ROI is difficult to come by, sometimes it’s hard to come up with an analysis that will show bottom line dollars, if that’s the case then aim for the intangibles: Does your app improve customer service? Can the firm do more with the same amount of people? Will the database prepare the company for growth going forward?

– Avoid the “L” word
It pains me to say it, but some executives I’ve worked with have mentioned “Layoffs” when seeing the improvements our firm can bring them. I prefer the “doing more with the same amount of people” analogy myself. You always want buy-in from the team helping you at your client, so avoid terms that will produce anxiety.

– Think of your work as a Profit Center
Many times it’s very easy to quantify savings…and so I always use the word “Investment” when referring to the cost of our work: “Your investment for this project will be…” is a line you will see at the end of my proposals.

Always view yourself in the right light and close more deals for your practice, get started today!

The concept of a subquery itself is fairly straightforward: have a query within a query. However, there are various applications of the subquery. Most frequently, a subquery is used in a filtering context:

SELECT a FROM n WHERE a NOT IN (SELECT a FROM x)

However, this is only the tip of the iceberg of their usefulness. Subqueries can also be used to create a ‘temporary view’ within your query construct. Let me give an example. Suppose that we have a table, tblTimeClock, consisting of an employee ID, a dateWorked column, and a timeLogged column of type double. When an employee clocks out for a day, their id and the total time they worked for the day is logged here. Now, we want to retrieve for a given day the employees that worked, how long each employee worked, and the total hours all employees logged for the day. Without subqueries, we can do one or the other, but not both. To retrieve the time each employee worked for the day, we would write something like:

SELECT employeeID, Sum(timeLogged) AS totEmpTime
 FROM tblTimeClock
 WHERE dateWorked=#11/13/2009#
 GROUP BY employeeID

This would return us the time logged by each employee. Now, to get the total time logged, we could do . . .

SELECT employeeID, Sum(timeLogged) AS totEmpTime, tTime.totTime
 FROM tblTimeClock,
 [SELECT Sum(timeLogged) AS totTime
 FROM tblTimeClock
 WHERE dateWorked=#11/13/2009#].
AS tTime
WHERE dateWorked=#11/13/2009#
GROUP BY employeeID

As the subquery only returns one value, it is a scalar subquery. That subquery is aliased as a table, and can be treated as such by referencing its alias, and then the desired column name.

NOTE: Pay attention to the period after the closing bracket. Omitting this period will cause Access (JET – not yet fully tested in ACE) to not properly recognize the subquery. Also, there can be no brackets within the defining brackets, as this will cause the query to fail. So this means the field names in the table(s) must contain no spaces.

In a more complex but practical example, say we want to retrieve all dry chemical lots produced in November 2009, and their moistures, if available. Building a query as such:

SELECT Lot.Lot_Number, rptQuality.cValu
FROM LOT LEFT JOIN rptQuality ON Lot.Lot_Number = rptQuality.lotnum
WHERE rptQuality.rstid=10 AND Lot.Lot_Completion_Date>=#11/1/2009 12:0:0 AM# AND Lot.Lot_Completion_Date<=#11/30/2009 11:59:59 PM# AND Lot.Hydro=’d’

Will only return to us the lot numbers and moistures of those lots that have been tested, regardless of the left join.  This is because we are filtering our result set by a value of that in the left-joined table (rstid=10), so we will only retrieve those lots that actually have moistures.  However, there may be several dozen lots that have been produced but not yet tested, and we do not want to exclude these lots from our query.  An appropriate solution is outlined below using a non-scalar subquery:

SELECT Lot.Lot_Number, rptQual.cValu
FROM LOT LEFT JOIN [SELECT rptQuality.lotnum, rptQuality.cValu FROM rptQuality WHERE rptQuality.rstid=10]. AS rptQual ON Lot.Lot_Number = rptQual.lotnum
WHERE Lot.Lot_Completion_Date>=#11/1/2009 12:0:0 AM# AND Lot.Lot_Completion_Date<=#11/30/2009 11:59:59 PM# AND Lot.Hydro=’d’

This query pre-filters our results down to just those lots with moisture values. Then, the left join is applied. With no additional filters on the left-joined table, our query will correctly return ALL produced dry lots in November, and show any relevant moistures.

Subqueries used in this manner may be treated exactly like any other table or query, where one can aggregate data and perform any other transformations as necessary.

With Access 97 (newer versions untested), there are some limitations to the subquery. First, a subquery cannot be nested within a subquery. In extremely complex cases, you will have to either store the results of one of the subqueries in the back-end .mdb as a view or temp table, or figure out a way to create the complex join syntax to make it work properly. Also, Access 97 does not like the use of [] to encapsulate relative field references. I.E. [SELECT [Lot_Number] FROM Lot]. AS LtNum will error Access in a heartbeat. You MUST use absolute field references as such: [SELECT Lot.Lot_Number FROM Lot]. AS LtNum. To summarize you cannot use [ or ] within your subquery, or Access will produce errors. Meaning, the field names in the table(s) must contain no spaces.

For more information on subqueries and scalar subqueries, please visit the following resource(s):
http://www.blueclaw-db.com/accessquerysql/scalar_subquery.htm

It’s sold out – again

Yet again this year’s conference was sold out, not because there’s a ton of people here but there is a low threshold of attendees due to the facilities. It lends itself to a small gathering of less than 100 people and a great atmosphere of sharing. Each year I book earlier so I don’t miss out. In addition, we had people from Brussels Belgian and Australia this year, which I think is a testament to the value of the conference.

Great Mix of People

I don’t have exact numbers but it seams that this year we have more independent consultants, but we still have a good mix of programmers, non-programmers, government and private enterprise people.

Day 1 – Walk your heart out

I enjoy coming in early and taking the walk around the Southern falls and being one with nature for a few hours. This year I arrived 30 mins late and had to do it alone, but I enjoy coming here and seeing and hearing the roar of the water as the mountain caps melt. I will post photos and videos when I get back home, simply stunning. Word to the wise: Bring hiking shoes and be prepared to hike a couple of miles going downhill and uphill. My sedentary programming job doesn’t prepare me for these hikes, but I love doing it.

F. Scott Barker - Automating Applications from Access.

Scott’s a great presenter, he kept us roaring with laughter, which is a great sign of a wonderful presenter, and had something for both the beginners and pros in the room. Scott established what would be the norm throughout the conference - speak up and let’s make this interactive. We took it to heart and peppered him with questions and observations.

Break – veggies! Thanks John!

Luke Chung – Taking over an Access Application

Luke blew it out of the water with the most comprehensive discussion on this topic I’v ever seen. He had 1.5 hours and did not stop for a breather, going through 50 slides of great info complemented with tips and personal experience.

Dinner – Beef Brisquette

Armen Stein – Round Table – Business/Consultant Roundtable Best Pratices

Armen did a great job hosting a wonderful session where we discussed many aspects of having a practice. We discussed a wide range of topics: quotes, estimating, handling clients, you name it. Worth the price of coming out here just for this.

End of Day 1 – Social at Blueberry

We all ended up at one of the buildings here and talked late into the night at the social, catching up with old friends and talking IT, sports, etc. plus smoking my stogie and drinking my fav beer.

Day 2 – Ryan McMinn – Access 15 Overview and where Access is headedd

Nothing new for me here, saw the same things I saw at MVP conference and still can’t talk about it till the pre-release is out. Lots if interest and questions from the audience on this topic.

Armen Stein – Experiences in Microsoft Cloud

I’m not a fanboy of Microsoft’s cloud resources but Armen made a great case for the move. He mentioned how using Lync he can be on a conference call and have it switch to the phone and not miss a beat. The economics are so cheap that if you’re not using Exchange with Outlook you really don’t have an excuse to make the switch.

F. Scott Barker – Using API Libraries in Access

Learned something new today: you can use API calls to create network drives, both interactively or behind the scenes. Also, always use the API vs File Dialog ActiveX object since the API version works every time. Note to self: Don’t change working demo code to try out suggestions from audience members, never leads to a good result. Scott touches most of the usual APIs we commonly use: Zip and Unzipping, user names, file dialogs, etc.

Alison Barter – Upsizing Access to SQL Server

This topic is near and dear to my heart, so I was looking forward to Alison’s presentation. She did a thorough review of upsizing Access.

Round Table with Ryan

Everyone peppered Ryan with questions on the future of Access: No VBA is NOT going away. Don’t know when Office 15 is coming out. There is no Developer certifications for Microsoft Access.

End of Day 2

At its simplest definition, recursion is a function that is applied to itself, or a function that is called by itself.  Here is a basic example, taken from mathematics, of a factorial.  5! is expressed as 5x4x3x2x1, or 120.  To compute a factorial for the number n, the following simple function could be utilized:

Public Function factorial(n as Integer) AS Integer
            If n <= 1 then
                        Factorial = 1
            Else
                        Factorial = n * factorial(n-1)
            End if
End Function

The logic for this function is as follows: We will use 3 as our n parameter value.  Upon first call, we have 3*factorial(2).  Factorial(2) is 2*Factorial(1).  Factorial(1) is 1.  We therefore have 3*2*1, or 6.

Recursion within the scope of say, a laboratory information management system, is used to build complex formulas, which are based on other formulas.  For example, if we have a formula for Copper Dry Basis that is CuWt*(1-Moist), where CuWt is a formula that is (CuPost-CuTare)/CuWt, and we try to parse out say, a Copper Dry Basis formula, first the parsing function is called.  The first element it sees might be a CuWt formula.  The parsing function is again called, this time with the formula id for CuWt.  (CuPost-CuTare)/CuWt as a string is returned (with relevant values, of course).  The original call to the parsing function is now active again, and we continue processing the additional elements, ‘*)1-‘, Moist, and ‘)’ .

These recursions are built into the formula models, allowing ultimate flexibility when building formulas.  Though we could technically have the formula model exist without recursion, it would make the creation and application of some formulas near impossible to enter in the correct syntax.  As an example, the verbose display of the Error % formula for one process could easily be 1527 characters long, including all the appropriate bracketing and operands.  For the manager to be expected to correctly program that formula would be absurd.  Because of recursion, the formula can be simplified to it’s very base form, a percentage difference formula, which is only 64 characters in length.

A special note for consideration is that there must always be a final point of termination for recursive functions.  For the sake of this discussion, that point of termination is when there are no longer any elements to process for the current formula, specifically elements of the ‘formula’ type.  In our above factorial example, we halt processing when we get to the integer 1, as defined by the mathematical definition of a factorial.  Not providing a final point of termination results in a similar scenario to an infinite loop, causing memory leaks and a whole bunch of bad stuff that you really don’t want to deal with.  As a matter of practicality, a mathematical formula cannot be based on itself.  So, the formula for Copper Dry Basis cannot be based on the formula for Copper Dry Basis.  When editing formula compositions by hand (you should NOT do this and instead should use the formula builder – a separate form and base table used to construct the formulas).

For more information on the concept of recursion, as well as programming examples, please see the following resources:

http://en.wikipedia.org/wiki/Recursion#Recursion_in_computer_science

http://erwnerve.tripod.com/prog/recursion/magic.htm (geared toward java, but can be adapted easily)

You can review part one here.

Take a look at part 2 here.

See part 3 here.

How can you test changes in the cloud with your Beta users?

Once you’ve rolled out your Access masterpiece using SQL Server in the cloud, your client’s will love your work so much they are going to request additional changes. (Hint: If you don’t get upgrade requests then it wasn’t a masterpiece to begin with). It’s easy to test changes on a local SQL Server database, but more often than not you will need to have a select group of your app users try the changes before rolling them out to everyone, and that means testing using the cloud.

Use a SQL Server Beta database to test changes before going live

SQL Server makes it easy to create a Beta database, just copy the live database or restore a full backup and give it a different name. Typically, we give ours the same name with “Beta” suffix. We usually use the production server as our Beta server in order to test the system under real world conditions but you don’t have to. Once you have a Beta database up and running use the code below to easily switch from Beta to production and back.

How to change to Beta: Just rename your Access file

In our apps, to switch to our SQL Server Beta database we just add the word Beta to our Access file. Having to rename the file and not change any code makes life easier and allows us to experiment with data without impacting operations and ensures that both beta and production are functionally identical. Our client also uses the technique for training new users on the system.

Public Function BetaTesting() As Boolean
10 If CurrentProject.Name = ReadGV("ProjectFileName", 1) Then
20    BetaTesting = False
30    TempVars.Add "BetaTesting", False
 'Change from Beta to production?
40    If InStr(1, CurrentDb.TableDefs(ReadGV("BetaTableTest", strText)).Connect, ReadGV("BetaCatalog", strText)) > 0 Then
 'Relink to production database
50       ChangeAppTitle ReadGV("ProjectTitle", strText)
60       RelinkAllTablesADOX
70   End If
80 Else
90    BetaTesting = True
100   TempVars.Add "BetaTesting", True
110   ChangeAppTitle "++++++++ BETA " & ReadGV("ProjectTitle", strText) & " BETA +++++++++"
120   If InStr(1, CurrentDb.TableDefs(ReadGV("BetaTableTest", strText)).Connect, ReadGV("BetaCatalog", strText)) < 1 Then
 'Relink to Beta database
130      RelinkAllTablesADOX
140   End If
150 End If
End Function

In line 10 we test if the current file name is the production version stored in our tblProgramOptions table, otherwise we are dealing with a Beta version of the system. (Click ReadGV to see a post on using ReadGV).

If it’s a production database then in line 30 we set a global variable for use later in the app. In line 40 we test if the table links are pointing to Beta and if so relink all tables to the production database in line 60.

If it’s a Beta database then in line 90 we define a global variable, change the app title and relink all tables to the Beta database in line 130. (Click on RelinkAllTablesADOX to view a post on how to relink all of your tables.)

Since we name our SQL Server database by just adding Beta to the name we can use the following lines in RelinkAllTablesADOX to switch to Beta.

If TempVars!IsBeta Then
 strCatalog =  conCatalog &  "Beta"
 Else
 strCatalog = conCatalog
 End If

*UPDATE* Code for ChangeAppTitle follows:

Public Sub ChangeAppTitle(strTitle As String)
 Dim proTitle As Property

 On Error Resume Next
 With CurrentDb
  Set proTitle = .CreateProperty("AppTitle", dbText, strTitle)
  Call .Properties.Append(proTitle)
  .Properties("AppTitle").Value = strTitle
 End With
 Call Application.RefreshTitleBar
End Sub

Series Conclusion

I hope all of these posts will help you take advantage of DSN-less tables in your app, let me know in the comments if you do use the code and how it’s helped you.

You can review part one here. Part 2 here. Part 4 here.

In part three of this series I’m going to give you the code to delete all of your linked tables once the app closes. Deleting your links makes your app secure since both the user’s name and password are stored with the linked tables. It may not be much of an issue on a local area network, but if your app is using SQL Server in the cloud it can be a security issue.

Where to place your code?

At our shop we have a main form that launches on every app, so we place the following code on the form’s close event:

Private Sub Form_Close()
   'delete any tables where connection property has ODBC in it
   Dim tdf As DAO.TableDef
StartAgain:
 For Each tdf In CurrentDb.TableDefs
    If InStr(1, tdf.Connect, "ODBC") > 0 Then
       DoCmd.DeleteObject acTable, tdf.Name
       GoTo StartAgain
    End If
 Next tdf
 Set tdf = Nothing
End Sub

Notice the use of the label “StartAgain” when you delete a table it changes the tabledef collection and you need to start over in order to refresh it, otherwise the code will error out.

Next Week: Changing from Production to Beta database by just changing the Access file name.

You can review part one here. Part three here. Part four here.

In last week’s post I talked about using a single table in your SQL Server database to easily manage security, in today’s post I’m going to take it one step further and use that same table to create all the necessary links needed for your app on startup.

Use ADODB to loop through table and create links
The technique uses an ADODB recordset to loop through all the records in tblTablePermissions and create the links on startup. We store the user’s name and password with the link, so we use a custom login form to store the user’s name and password for the session, which are then used to create the links.

Step 1: Trap user’s login info and test to see if they can connect with SQL Server
Here’s the code we use when the user clicks the login button on our login form:

Private Sub cmdLogin()
If IsNull(Me.txtUserID) Or IsNull(Me.txtPassword) Then
 MsgBox "You must supply both your user name and password", vbInformation, "Can't Login"
 Exit Sub
End If
'Store login credentials in memory
TempVars.Add "UserID", Me.txtUserID.Value
TempVars.Add "Password", Me.txtPassword.Value
If InStr(1, CurrentProject.Name, "Beta") > 0 Then
   TempVars.Add "IsBeta", True
Else
   TempVars.Add "IsBeta", False
End If
If Not OpenMyConnection Then
 MsgBox "Connection to SQL Server failed, please verify your user name and/or password", vbInformation, "Login Failed"
 Exit Sub
End If

RelinkAllTablesADOX
End Sub

Change between Beta and Production Databases
By including the name “Beta” in the project name the code will point to the Beta database instead of the production one. I’ll be elaborating more on this later in the series.

OpenMyConnection

Notice the use of OpenMyConnection, which I use in my Easy ADODB methods. Here is the definition of the function:

Public Function OpenMyConnection() As Boolean
 On Error GoTo OpenMyConnection_Error

10 If con.State = adStateOpen Then
20 con.Close
30 End If
40 con.ConnectionString = conConnection & "User ID =" & TempVars!UserID & ";Password=" & TempVars!Password
50 con.Open
60 If Not con.State = adStateOpen Then
70 OpenMyConnection = False
80 Else
90 OpenMyConnection = True
100 End If
On Error GoTo 0
 Exit Function
OpenMyConnection_Error:
MsgBox "Error " & Err.Number & " Line " & Erl & " (" & Err.Description & ") in procedure OpenMyConnection of Module mdlConnect"
End Function

RelinkAllTablesADOX Procedure
This procedure is where the fireworks go off:

Function RelinkAllTablesADOX() As Boolean
 Dim cat As Object
 Dim tbl As Object
 Dim fLink As Boolean
 Dim strSQL As String
 Dim strTableName As String
 Dim strField As String
 Dim strDriver As String
 Dim strLocalTableName As String
 Dim rs As ADODB.Recordset
 Dim strCatalog As String
 Const conCatalog As String = "DataBE"
 Const conServer As String = "ServerName"
 On Error GoTo HandleErr
 Set cat = CreateObject("ADOX.Catalog")
 cat.ActiveConnection = CurrentProject.Connection
 strDriver = "SQL Server"

 For Each tbl In cat.Tables
 With tbl
 ' Delete any linked tables
   If .Type = "PASS-THROUGH" Then
     CurrentDb.TableDefs.Delete .Name
   End If
 End With
 Next tbl

30 strSQL = "Select * from tblTablePermissions Where DontLink = 0"
 OpenMyRecordset rs, strSQL
If TempVars!IsBeta Then
 strCatalog =  conCatalog &  "Beta"
 Else
 strCatalog = conCatalog
 End If
With rs
 Do While .EOF = False
 strTableName = !Table_Name
35 If IsNull(!Access_Name) Then
 strLocalTableName = strTableName
 Else
 strLocalTableName = !Access_Name
 End If
40 fLink = AttachDSNLessTable(strLocalTableName, strTableName, conServer, strCatalog, strDriver, _
 TempVars!UserID, TempVars!Password)

50 'Is it a view? If so check if index needed
 If Left(strTableName, 2) = "vw" And fLink Then
 strField = Nz(DLookup("KeyField", "tblLinkViews", "[View] = '" & strTableName & "'"), "")
 If Not strField = "" Then
 strSQL = "Create Index IX_" & strField & " On " & strLocalTableName & " (" & strField & ") WITH PRIMARY"
 CurrentDb.Execute strSQL
 End If
 End If
 .MoveNext
 Loop
 End With
 RelinkAllTablesADOX = fLink
ExitHere:
 Set cat = Nothing
 Exit Function
HandleErr:
 RelinkAllTablesADOX = False
 If Not fLink Then
 'MsgBox "Linking failed, please contact tech support.", vbInformation, "Error Linking Table"
 Resume ExitHere
 End If
 MsgBox _
 Prompt:=Err & ": " & Err.Description, _
 Title:="Error in RelinkAllTablesADOX"
 Resume ExitHere
End Function

Only link tables where DontLink = 0
Notice in Line 30 above we only link tables that have DontLink set t False. You may wonder why we even bother placing a table in tblTablePermissions when it’s not used in Access as a linked table? Remember in my first post we use it for setting security of all tables, and if there is ever a need to use ADODB and go directly to the table in SQL Server via our Access code or pass-through query then we need that table listed in tblTablePermissions.

Does view need index?
In line 50 above the code checks if the table being linked is a view, and if so, creates the index in Access using a local table called tblLinkViews. You could probably do away with this concept in your code and just add another column to tblTablePermissions called ViewIndex, then just use !ViewIndex in the code above to read the SQL syntax into strSQL.

Rename SQL Server Table?
Notice in line 35 above the system will actually use a different name for the Access table if so specified. This technique can come in handy when your dealing with a legacy app and wish to use names in SQL Server that better suit you but can’t be used in Access due to legacy code.

AttachDSNLess Table Code
The above procedure calls this sub to actually do the linking:

'Note: Code adapted from http://support.microsoft.com/kb/892490
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, strDriver As String, _
 Optional stUserName As String, Optional stPassword As String) As Boolean
10 On Error GoTo AttachDSNLessTable_Err
 Dim td As TableDef
 Dim stConnect As String

20 If stLocalTableName = "" Then
30 stLocalTableName = stRemoteTableName
40 End If
50 Application.Echo True, "Linking table " & stLocalTableName
 'Stop
 '//WARNING: This will save the username and the password with the linked table information.
60 stConnect = "ODBC;DRIVER=" & strDriver & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword

70 Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
80 CurrentDb.TableDefs.Append td
90 AttachDSNLessTable = True
100 Exit Function
AttachDSNLessTable_Err:
110 If Err.Number = 3265 Or Err.Number = 3011 Then
 'Table does not exist, continue anyway
120 Resume Next
130 End If
140 AttachDSNLessTable = False
150 Stop
160 MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function

In my next post I’ll discuss how to destroy the links when you exit the application.

You can review part two here. Take a look at part three here and part four here.

In my first post on this topic, I sent you to Doug Steele’s great article on how to do DSN-less table connections. Today I’m starting a new series of post on this topic that will cover the following:

• Using a SQL Server table to drive the process of creating DSN-less tables in Access. As an added benefit we will use the same table to setup security for tables.
• Code in Access that will cycle through the SQL Server table and create the links.
• Code in Access to destroy the links before exciting the application.
• Switching between Beta and production servers with ease.

Why Destroy The Links?
Our firm always uses DSN-less tables; by avoiding a DSN at each workstation at the client site, we simplify our deployment. It works well in a corporate environment since we use a trusted connection, meaning your Windows password and username are not included in the table link. If you grab the application and put it on another workstation that user’s credentials are used instead, not the original users.

But if the application is using SQL Server over the web, (data in the cloud), we’re  using SQL Server security, where we prefer to control the login process using an Access form as opposed to letting Access handle it with a cryptic message. Once the user is authenticated through our code, we proceed to link all of the tables using a single SQL Server table, which saves the username and password with the table links behind the scenes. We destroy the links when the user exits the app so that it can’t be used by unauthorized users.

It takes time to recreate all of the links on startup, so managing user expectations is essential. In general, people don’t mind waiting a little bit on program startup, in particular if it’s good for securing their data

Don’t rely on your table being there…
One benefit of this approach is not worrying if the user, (or the programmer), deletes a linked table while using the app. The system will simply recreate the link again on startup.

One table to rule them all: tblTablePermissions
tblTablePermissions in SQL Server has a dual purpose: set permissions for all other SQL Server tables and drive the linking process in Access.

One of the best features of SQL Server is its ability to secure data, but it can be a chore to maintain and a hassle to setup, the more tables your app has the longer it can take to manually setup security. With tblTablePermissions and usp_RunPermissions we can easily add a table or view to the database and quickly redo our security schema by running usp_RunPermissions.

Three security roles: Admins, Employees and Clients

The app where I designed this technique had three roles in SQL Server:

• Admin: Members in this role get all rights to every table listed in tblTablePermissions
• Employee: Read rights to all tables and selective rights based on which columns are set to True in UpdateEmployee, DeleteEmployee or InsertEmployee.
• Client: The app I designed with this security schema also had clients logging in to mostly view data and only modify certain tables, such as tblNotes. For the majority of tables they get read on nothing at all.

Next week : Code in Access to read tblTablePermissions and setup table links!

To bind or not to bind, that is the question

Unbound forms are forms with no data source, whereas bound forms have one and it’s the default way to bind your form to your tables or queries. Having a data source is the normal way you create Access forms,  but it can cause problems no matter how careful you can be:

• Record Locks – Have you ever had an app grind to a halt because someone left for lunch while editing a record?
• Slow performance in opening a form: A poorly designed form can be bring your app to it’s knees.
• Lost Data – Stop me if you’ve heard this one: “You and another user are editing the same record, you can either copy to the clipboard or discard your changes”

Wow, that sounds awesome! What’s not to like?

Unbound forms require a LOT of code to work. You basically must take over the tasks Access will normally handle: retrieve data from your database and store it locally, load the data into your forms fields, wait until user saves, discards or edits data, save said data back to your database. In short, you have just increased your workload tenfold, for a small increase in performance, if any.

Tips for using bound forms:

Rather than using unbound forms, here are some tips to optimize bound forms you can use to avoid issues:

• Maximize the user of views and stored procedures. Ask yourself every time you want Access to do something if it can be done by the server better and faster.
• Never display data the user has not asked for. For example, always use the where clause of the DoCmd.OpenForm and DoCmd.OpenReport to display the exact record(s) needed by the user.
• Use temporary tables as much as you can. They are great for speeding up your app and provide some of the benefits of unbound forms. Example: Avoid mixing local tables with SQL Server tables and instead download the SQL Server data into one or more temp tables.
• Use infrequently updated lookup tables in the frontend and not on SQL Server. For example, the list of 50 states and territories should be a native table in your FE. Have a duplicate copy on  your server for views you may create for your app.
• Maximize the use of ADODB. If you haven’t learned how to use ADODB you are really missing out on the best method to communicate with SQL Server from Access.  For example, instead of using DAO and action queries on linked tables, send the action query to SQL Server, which can do it far faster in almost all cases. Ben has in exceptional cases, even bound forms to ADO recordsets, which offers full native functionality and is still easier than doing it unbound. However, it has some caveats that you should be aware of if you go this route.
• Learn TSQL: To really turbo charge Access with SQL Server you’re going to have to learn SQL Server’s native language. What are you waiting for? Go out and purchase a good TSQL beginner’s book and learn it today!

Hope these tips help you out! If you want even more, look at those excellent guides:

UtterAccess’ Beginner’s Guide to ODBC

Andy Baron’s Optimizing Microsoft Access with SQL Server

Get Exclusivity

Due to multitasking nature of Windows, there is no simple universal function to ask if a file, whatever type of file it may be, is open or not. However, we can at least approximate this functionality by attempting to open the file exclusively in our code and if we succeed, be reasonably sure that we are good to proceed onwards.

Introducing IsFileLocked() Function

When we open a text file with Notepad, Notepad does not place any locks on the file even when we dirty the file. In this situation, it does no harm to copy or read the file while it’s open by Notepad. Thus IsFileLocked() will return true for any .txt files opened by Notepad and in this situation, it’s generally OK. Of course, one shouldn’t try to write to the said file but that’s not what we are doing here. On the other hand, Word and Excel will place locks on their documents. Therefore trying to copy or read the file may be undesirable and threat it’s integrity. In this situation, our attempt to acquire an exclusive lock will fail, allowing us to alert the user to close the file themselves before proceeding further or cancel out.

The only significant caveat is that this procedure is not appropriate for checking whether a file is locked by other processes such as running background tasks. The locks can be acquired and released in milliseconds so calling the function is inherently racy. For purposes of checking whether users has a file open, this should be sufficient.

Public Function IsFileLocked(PathName As String) As Boolean
On Error GoTo ErrHandler
  Dim i As Integer

  If Len(Dir$(PathName)) Then
    i = FreeFile()
    Open PathName For Random Access Read Write Lock Read Write As #i
    Lock i 'Redundant but let's be 100% sure
    Unlock i
    Close i
  Else
    Err.Raise 53
  End If

ExitProc:
  On Error GoTo 0
  Exit Function

ErrHandler:
  Select Case Err.Number
    Case 70 'Unable to acquire exclusive lock
      IsFileOpen = True
    Case Else
      MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    End Select
  Resume ExitProc
  Resume
End Function

What if you have multiple files open?

That gets us to a good start but we also have to handle the fact that there may be more than one file open, and nobody likes being alerted by multiple dialogs. Thus we need to roll up the individual checks into a single message so the users can only see one message for all locked files that they may need to close. Here’s the code:

Public Function CheckForLockedFiles( _
  Files() As String _
) As Boolean
On Error GoTo ErrHandler
  Dim i As Long
  Dim lngLocks As Long
  Dim strFiles() As String
  Dim strMessage As String

  Do
    lngLocks = 0
    For i = 0 To UBound(Files)
      If IsFileOpen(Files(i)) Then
        ReDim Preserve strFiles(lngLocks)
        strFiles(lngLocks) = Files(i)
        lngLocks = lngLocks + 1
      End If
    Next
    If lngLocks Then
      strMessage = "The following files are in use. " & _
                   "Please close the application that may have it open." _
                   & vbNewLine & vbNewLine
      For i = 0 To UBound(strFiles)
        strMessage = strMessage & strFiles(i) & vbNewLine
      Next
      If vbCancel = MsgBox(strMessage, vbRetryCancel, "Files in use") Then
        CheckForLockedFiles = False
        Exit Do
      End If
    End If
  Loop Until lngLocks = 0
  If lngLocks = 0 Then
    CheckForLockedFiles = True
  End If

ExitProc:
  On Error GoTo 0
  Exit Function

ErrHandler:
  Select Case Err.Number
    Case 53 'File doesn't exist, ignore
      Resume Next
    Case Else
      MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
  End Select
  Resume ExitProc
  Resume
End Function