If your client sees you as a cost center you already lost…
Access can do great things, on a short time frame and with a smaller budget than other technologies. If your client can’t see the benefits of your work it’s your responsibility to step up and show them how your work output should be considered as part of a profit center. Here’s how:
– 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!
Subquery and the Scalar Query
Today’s guest post come’s from Mark Davis, a Access MVP. You can learn more about him at http://www.linkedin.com/in/markgerarddavis
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
PAUG Live Blogging
This week I’m at Paug’s Access conference in gorgeous Silverfalls, Oregon. I’ll be live blogging here so come back here often for frequent updates over the weekend.
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
Using Recursion in your Access database
Today’s guest post come’s from Mark Davis, a Access MVP. You can learn more about him at http://www.linkedin.com/in/markgerarddavis
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)
Part 4: Switching from Production to Beta backends in SQL Server with ease.
Author note: This is the final post on a four post series on DSN-less tables in Access.
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.
Part 3: Destroy your linked tables on exit
Author note: This is part 3 of a series on DSN-less tables in Access.
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.
Part 2: Linking tables using a SQL Server table
Author note: This is part 2 of a series on DSN-less tables in Access.
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 DonLink = 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.
DSN-less tables; a better way…
Author note: This is part 1 of a series on DSN-less tables in Access.
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!
Why using unbound forms are a bad idea
Many of you know that I’m a big fan of Access with SQL Server, and if you’re a frequent blog reader you will also know it’s not easy to optimize the relationship between them, it takes work but it’s very rewarding when they’re working great. Some developers however go to the extreme and use unbound forms, is it worth it?
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”
- Quick loading of data since you only retrieve data the user needs to see in that moment.
- No locks placed on your tables
- You can parse the data through all kinds of business rules before posting back to your database.
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
Checking if files are locked using VBA
In one of our client’s applications, we built an email template that sometimes may attach files to be sent out. Our client wanted a way to preview the attachment before they actually send the email. This created a dilemma – if they can preview and potentially edit the files, we certainly don’t want to continue and try to attach open files to an email, therefore creating a problem. So we needed a way to check whether files may be already open, alert the user to close the file, verify it’s closed then send the emails.
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
