As I stated in my previous post, I was wanting to expand my use of pass-through queries when working with my SQL data. It just so happened that one of the places where I knew that I wanted to make this change was actually in the same area where I was getting the error messages when the application failed.

I took the time to completely change the method I was using to return a very small amount of data. I implemented the use of a pass-through query. I must admit that in my testing during this development change I did not notice a significant change in the speed at which the data was returned.

After making this change and feeling good about moving from DAO record set to direct access to the data with the pass-through query, I was again ready to tackle my unresolved issue and try to get my application to work with the MS Access runtime version, especially on a Windows XP machine.

I decided to try making an accde version of the application and give it a try. To my surprise when I opened my application on the XP machine usinig the accde file with the runtime version, I was able to use the application and did not received any of the error messages that I had previously received.

This means that now I can deploy my application using the MS Access 2010 runtime version and all I have to use is the accde type file.

As I start to deploy the application I will be posting back here to share any experiences.

When I reached the point in the development of my most recent project where I was about ready to deploy it, I began to think about the process of actually deploying this new application and began to wonder about what issues I might encounter when I did deploy it. I knew that I was going to be deploying this new application to users that would most likely not even have Microsoft Office installed on their computer, much less have MS Access 2010. So there was really no decision to be made. I had to deploy the application and install the runtime version of MS Access 2010.

As I began to research the deployment of the runtime version it became increasingly clear that I could expect to run into some issues. I was reading postings by some developers who had not been able to get their application to run under certain circumstances. There seemed to be more issues when deploying the Access 2010 runtime version to users with a Windows XP machine. With this in mind, I decided to start my deployment efforts on an XP machine that I had sitting around. Actually, to tell the truth, I have been developing the entire application on an XP desktop machine and a Windows 7 laptop, but I actually did have another XP machine that I could use to test my deployment efforts.

Please note that this XP machine had had MS Office 97, including MS Access installed on it. I first decided that I would un-install the Office 97, including MS Access. I then downloaded and installed the Access 2010 runtime version. I also created an entry in the registry to create the needed “Trusted Location” as per the instructions that can be found here: http://www.accessribbon.de/en/?Trust_Center:Trusted_Locations as well as other locations. I then started my test of running my application by first attempting to run the .accdb file using the Access runtime version. I immediately got the message shown below.

After receiving this message, the application immediately shut down. No other information was available about the cause of the error.

My next effort was to rename the accdb extention to “accdr”. When I attempted to run my application using this renamed extension I received the error message shown below.

This error was actually from the error checking in my application and indicated that there was an error in the VBA code in the Form_Unload event of a form named “frmHidden”.

Let me stop here and explain that I had tested my application extensively and had included error checking at every appropriate place. Opening my application and running it on both XP and Windows 7 machines to be sure there were no errors, especially when just opening the application.

Next on my list for testing was to have Access compile and create an accde version of my application. When I tested my new complied version of my application on my test deployment XP machine, I was able to login to the application and I was thinking, “Ok, now I have it working.” As I reached a critical place in the usage of the application I again received an error message very much like the one shown above with the exception that it referenced code behind a totally different form. Due to the fact that I was so confident that I did not have problems with my VBA code that would cause this kind of error, I was hesitant to even look at the VBA code. After doing a lot of searching for answers and finally even looking at my VBA code, I was even more confused about why my application would not work when being deployed on an XP machine. By the way, perhaps I need to say right here that during this same time I was doing some testing of deploying the same application on a Windows 7 machine and was not experiencing any issues. I also was not getting any of the errors that I was seeing on the XP machine.

Needless to say, this was very frustrating and there was not a lot of assistance or help available. In fact, some comments I read had me just about ready to tell my users that they would not be able to use my application on their Windows XP machine but would have to upgrade to Windows 7 if they wanted to run my new application. My better judgment told me this was not the correct option.

Just to provide some information about my application, please understand that I had never before created an entire application that was linked to an SQL Server database. I have really had a severe leaning curve going on and I have come to have an even greater appreciation for using stored procedures and pass-through queries.

While all of this was going on, I was also thinking about changing the way I was accessing some data in a few strategic places the application, so I decide to take a break from the deployment issues and work on implementing the use of a pass-through query to replace the use of a DAO record set to return a small amount of required information. Although the DAO record set did not seem to be extremely slow, it was just interesting to me how much better the response was when I used a stored procedure or a pass-through query than when using the normal record set method.

Hang around for another day or so and I will share what happened with I changed from using the DAO record set method to the pass-through query method.

Recently I have had another of those interesting learning curves. I have spent the past 18 to 20 years attempting to learn to develop database applications exclusively using MS Access. I started with Access 2.0 and up to now I have developed in every version of Access since 2.0.

I have even had experience using “views” provided by some database administrator as a data source in my project, but I had never attempted to really get in to the use of SQL Server.

As I said, my latest development project actually required that I not only use SQL Server that that I also learn to create Stored Procedures. While I do not consider myself to be even close to a guru with SQL Server, I can say that after spending the past several months developing a new “cloud” based application I am certainly much more fluent in it that I was before.

After reading many post on some of my favorite forums, it seems that I started out much the same as many of you have indicated that you started. I simply created linked tables to my SQL Server database which, by the way, is very reasonably hosted by a local hosting provider on his web server.

Using the “linked table” method I started with the development of my application. (Perhaps I should make it clear here that I am actually developing this application for my own use. It will be a commercial database application that will replace a desktop database application that I wrote and deployed almost 15 years ago.) I was very pleasantly surprised withj the speed at which my queries and forms were being populated. As with any project things stared out rather simple as far as queries and/or forms were concerned.

However, the farther I went developing more and more complicated queries and forms, things began to slow down in certain place. Needless to say I was starting to think, “Big mistake moving to SQL Server. This going to be way too slow.” Then someone suggested that I tap into the power of Stored Procedures. I can say that I knew just enough about them to be very dangerous. So I started to read and study but I was not getting where I needed or even wanted to go fast enough. Then I remembered that I had a really good friend that was a real guru with SQL Server. In fact that is what he does everyday. He is currently creating SSIS packages at his new job. So, instead of struggling along and wasting my time on reading, I decided to call Jimmy. I want to give credit where credit is due. My friend, Jimmy, has provided the most tremendous help that anyone could have ever asked for.

Come back in a day or so for more about this SQL learning curve. I will be sharing my experiences with leaning to get some real speed into my application using stored procedures. I will also be sharing my experience with pass-through queries.

For those of you who have had problems deploying you Access 2010 applications on Windows XP machines where you get the error message “ODBC call – fail …”, I have some news about a possible fix.

Ok, so I have asked the questions, but now I am thinking that perhaps the better question might be, do you even provide any custom naming for any controls?

Forms and reports are an inevitable part of any good database application. Forms provide the platform for users of the application to interact with the data. Reports provide the ability to present the data in a predefined and structured manner. Both forms and reports required controls to display data.

For the purpose of this writing, I will concentrate my thoughts on Forms. We will leave the discussion of controls in reports to another time.

During development of the database application adding controls to forms is a very common and required practice. These controls can be added to forms and/or reports in a variety of ways. Users can use a wizard to add a form and have the required controls added by the wizard. Users can also simply select a table and click the “Fom” option from the “Create” tab on the ribbon and Access will automatically create a form that will allow the user to interact with the data in the table. Alternately user can also simply create a completely blank form, provide a record source for the form and then user one method or the other to add the required controls to the blank form.

Forms come in basically two varieties, bound and unbound. This subject is one that must be address by its self. Here is a link that will help to understand this concept better: http://www.baldyweb.com/BoundUnbound.htm.

Every control that is added to a form or report must have a name. No matter what method is used to add controls to a form, Access does the best job possible to provide names for all controls by naming the using the name associated with the type of control and then adding an incrementing number to the end of that name.

I consistently see VBA code from others where they are attempting to refer to controls that clearly have the default names provided by Access when the control was added. As a result, I have come to believe that many Access developers do not understand that they should use some type of naming convention to provide meaningful names for the controls on their forms.

While I have no desire to try to tell anyone what naming convention to use, I do want to strongly encourage all Access developers to adopt and use some naming convention. You might not want to adopt any one specific naming convention but rather take the options offered by other naming conventions and develop your own custom naming conventions. So, here are some links that might just inspire some of you to actually start using some sort of naming convention. So check out these links: The Leszynski Naming Convention, The Lexzynski/Reddick Naming Convention or the Microsoft Access Object Naming Conventions.

What should be named and what might not need to be named? During the development of my own forms, the only controls that I do not individually name are labels that are associated with or attached to other controls. In the event that I plan to manipulate a label associated with another control is associated with or attached to another control, I will also provide a custom name for the label as well as the primary control. One condition where I do this is when I have a check box that has a Yes/No field as its data source. Let’s say the check box has a Yes/No field named “Active”. I would name the check box “chkActive”. I would also provide a name for the that is associated with or attached to that check box. I would name the label “lblStatus”. This way I can refer to each control independently but still have the label to be connected to the check box so when I hide the check box, the connected label is also hidden.

I cannot begin to tell you how critical it is during the VBA coding process to have your controls named with meaningful names. Only when you have experienced this will you be able to understand the positive impact that using a good, consistent naming convention will have on your coding ability.

I will be posting more on creating, naming and using objects in Access in future posts.

If you have ever had to manage the visibility, the enabled or the locked properties of multiple controls on one or more forms, then you need to take a look at a demo database and the accompanying PDF document that I have posted on the Download page of my website at: http://www.askdoctoraccess.com/DownloadPage.aspx

Here are a few highlights about this technique:

Special Method for Managing Multiple Controls on a Form

I initially started out just using macros to manage these controls. Over the years I then started developing and using VBA code behind the object on my forms like the one described above to manage the various controls on my form. I just created individual lines of code that would make specific controls visible or not based on the button that was clicked. For example, if the Edit button was clicked, I would specifically reference each of the controls that needed to be made visible using one line of VBA code to make each control to make it visible.

Note to my experienced Access colleagues: I am now totally aware of how inefficient and ridiculously time consuming this type of programming was. But hey, we all have to learn and I submit that many of our friends that are new to VBA programming are doing this same kind of programming right now.

Writing VBA code like this was a very time consuming process because I then had to create this type of code had over and over again in the code behind each command button.

This was so time consuming and required such a huge amount of redundant code that I finally figured out that I could create a “user defined function” that would allow me to create the VBA code just one time and, by passing a value of “true” or “false” my function, would cause the referenced list of controls to be visible or not visible based on the value passed to my function.

Although the user defined function made the development of this type of user interface much easier to create and maintain, it still required that I add a single line of code to address each and every control for which I needed to manage various properties.

I recently came across a couple of posting on the Access forums about using the “Tag” property to identify controls that were to be managed in some way. I have now expanded on this concept and come up with what I know to be an even more simple way to use the “Tag” property of controls to accomplish the management of controls on my form. Using my method, I can not only provide all of the same functionality provided by the user defined function method that I have used previously, but I can actually provide even more capability and flexibility for managing the controls on my form. The best news of all is that I can now use one Public function that is defined in a module to manage the appropriate properties of multiple controls on any form I choose by passing the name of the form and a true or false value to the function when it is called.

Note: Previously I had rarely used the “Tag” property of forms and/or controls, and had never found a really significant usage for this property.

In this new method, the “Tag” property becomes an intricate part of the process.

Not only can you define your own values to be used for managing the various properties of the controls instead of using the ones I have suggested, but you could easily add new values that would allow you to manage other properties of the controls.

To download the “ManagingMultipleControls” zip file containing the demo database, the PDF document with the complete explanation of how to implement this technique, including the public function that does the heavy lifting, just visit my website at: http://www.askdoctoraccess.com/DownloadPage.aspx

I recently posted the following VBA code to the Access World Forums but I though I would also post it here on my blog.

I had found code that was supposed to work, but did not, so I modified the code to make it work for me.

Copy the code below and paste it into a new module. Be careful not to name this new module with the same name as the Public Function: “RefreshSharePointLinks”.

'***********Code Start****************'This code is a modified version of the code that can be found at:'http://blogs.office.com/b/microsoft-access/archive/2009/02/04/code-to-refresh-sharepoint-link-tables.aspx'The original code has been modified by Byron Polk'It is not to be altered or distributed,'except as part of an application.'You are free to use it in any application,'provided the copyright notice is left unchanged.Public Function RefreshSharePointLinks()Dim dbs As DatabaseDim rst As dao.RecordsetDim tbl As dao.TableDefDim fld As dao.FieldDim strSql As StringSet dbs = CurrentDb()On Error GoTo Err_ChkError'iterate through all of the table objects in the databaseFor Each tbl In dbs.TableDefs 'only try to refresh linked or "Attadhed" tables If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then '***You may need to add additional "If" statements to have the code ' ignore specific tabale 'if the table is named "User Information List" do not relink the table If Left(tbl.Name, 21) <> "User Information List" Then 'if the table is named "UserInfo" do not relink the table If Left(tbl.Name, 8) <> "UserInfo" Then 'if the connection string for the linked or "Attached" table 'starts with "WSS" then this is a ShasrPoint table. If Left(tbl.Connect, 3) = "WSS" Then 'try to open an editabel recordset from this table strSql = "SELECT * FROM [" & tbl.Name & "];" Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset) 'locate an updatable field (not a calculated column) For Each fld In tbl.Fields If fld.Type = dbText Then 'if there is a value in this field use this field If Not IsNull(rst.Fields(fld.Name).value) Then 'insure that this field is an updateable type field If fld.DataUpdatable = False Then 'set the recordset to "edit" mode rst.Edit 'update the updatabale field with the same value rst.Fields(fld.Name).value = rst.Fields(fld.Name).value 'attempt to update the record. rst.Update 'if the table needs to be relinked, error 3851 will occur and 'our "On Error" error handling will kick in and relink the table 'Only if the update was successful, move on to the next linked table GoTo GetNextLinkedTbl End If End If End If Next End If End If End If End IfGetNextLinkedTbl:Next
***************End of Code***********

My initial intent for this blog was that I would start by posting information about Access for beginning users. Although I still feel that new information for new users is important and something that I have a passion for, I have now come to believe that I can provide relevant information for both beginners but also for even the professional user.

Just for the record, I make my living as an Access Database Application Developer. I normally work either as an independent contractor or for various staffing organizations. I have been doing this type of work for over 15 years now. (Wow, doesn’t seem like it has been that long.)

I have worked with every version of MS Access starting with Version 2.0 thru Access 2010. My most recent and current contract assignment is with a major, world wide, very diversified company. This company has their own installation of SharePoint version 2010 and I am getting first hand experience developing Access applications for use with SharePoint lists.
I will have much more about my experiences in future posts over the next few days and weeks. If you have any experiences that you would like to share with me and others about working with this relatively new tool, please provide some info here.