There are shed-loads of good stuff in Access 2010 web databases (and in standard Access 2010 web databases too), but one major drawback is that some pretty basic SQL is not possible in web databases. One of those omissions is the ability to do a “SELECT DISTINCT” in order to show unique values in a query. When I first found this out I was not impressed, but assumed that there must be a work around of some sort since there (nearly?) always is with a tool as flexible as Access. However, I needed to use a SELECT DISTINCT in a test Access 2010 web database I am creating for a customer and I spent a whole day trying to sort out how to do it. I was not a happy bunny.
However, I posted my problem at http://social.technet.microsoft.com/Forums/en-US/access/thread/759df5d3-b21c-4645-8515-73e838d599f1 and Krunal Sheth of Microsoft came to the rescue (again). I have now simplified it a bit (as I had a table, which for my situation is unnecessary) and added comments to Krunal’s solution. The revised solution can be found at http://cid-a189988cfd6632eb.skydrive.live.com/browse.aspx/Public?uc=1.
It works like this:
1) The main search query (qselContainsName) searches fields in three tables. The main table (tblCompanies) contains a couple of fields called MDName and SecretaryName, which the query checks to see if either or both contains text which matches our search term (which we supply in the form frmMainForm). Thus we might want to search for “Cossey” or “Coss” in each of these fields. Additionally qselContainsName searches in two related tables called tblDirectors and tblContactEvents. Since these two tables are on the “many” side of a one-to-many relationship with tblCompanies, multiple matching records in them means that qselContainsName will return multiple records for the same company. In a client query the addition of a simple “DISTINCT” to the SQL would bring it back to showing the company just the once, but we cannot use “DISTINCT” in an Access 2010 web query.
2) To get over this problem with a web query, we can add a field to tblCompanies called FilterOnMe and then create a query (qselFilteredCompanies) based on tblCompanies and which has the criterion of “FilterOnMe = True”. This will give us the equivalent of a “SELECT DISTINCT”. All (ha!) we have to do is create a data macro which will work through the companies returned by qselContainsName and set the FilterOnMe field for those same companies in tblCompanies to True. It was this bit that I struggled with. Anyway, here is the data macro we need.
This data macro is called from the Search button on the form frmMainForm and works as follows:
i) We start with creating a parameter to receive the search text to be used by qselContainsName (remember this query returns our list of matched companies, but returns multiple copies).
ii) We next have a “For Each Record In tblCompanies” block to which we assign an Alias. Thus we start to work through each record in tblCompanies and assign them an Alias of FilterTable makes them editable. We set a localvar called lvFilterApplied to False. We’ll see why in vi) below.
iii) Using “Look Up A Record In qselContainsName” we check to see if the tblCompanies record (aka “Filtertable”) matches any returned by the query qselContainsName by using the Where Condition of FilterTable.CompanyID=CompanyID. At this point the default context is qselContainsName so CompanyID means qselContainsName.CompanyID. We could have made it clearer by assigning an Alias, but since we don’t need to edit anything in qselContainsName, we don’t need an alias (qselContainsName is a read-only query anyway). Note that it is here we also set the parameter in the query qselContainsName (prmName) to the value supplied by prmSearchString created in i) above.
iv) If the Look Up finds a match, the next section (EditRecord) will have a record to work with. Here “Alias Filtertable” is telling the EditRecord block which record to work with, not setting a new Alias. This can be confusing. In this section we set the FilterOnMe field in this record to True. Thus this record will later be one of the records found when we run the query qselFilteredCompanies in our form as this query looks for records in tblCompanies which have their FilterOnMe field set to True.
v) It is a bit unfortunate that “For Each Record In” and “Look Up Record In” In blocks don’t have an “End For Each Record” and “End Look Up Record” terminator. The only way to see in a data macro where the block ends is to look carefully at the alignment of the start of the sections within such a block. Collapsing a block by clicking on the + marker at the left end at the start of the block and seeing what disappears helps with this. If something disappears it means it is in the block itself; if nothing happens to the section you are looking at it means it is not in the block. Anyway, the SetLocalVar action on lvFilterApplied is used here to set the localvar lvFilterApplied to True, a value which will be used in the next section.
vi) We now test the localvar lvFilterAppled to see whether it is True or False. We initialised this localvar in each record in our “For Each Record In tblCompanies” in i) above by setting its value to False and, if we found a matching record the query qselContainsName we changed this localvar to True in v) above. We now use the final value of this localvar to determine whether we need to reset the value of FilterMe in our record from tblCompanies back to False since the user may have run this data macro in a previous search and our record in tblCompanies may still have its FilterOnMe field set to True from that previous search.
By running the above data macro we thus set the FilterOnMe field in each record in tblCompanies to True where there is a corresponding match in the query qselContainsName (and to False if there is no match) and a requery of the subform fsubSearchResults (based on the query qselFilteredCompanies) now shows the user those companies which have a match in the query qselContainsName. The important thing is that the subform shows the company’s/companies’ details once only for each matching company.
The Thoughts of Chairman Cossey
i) A similar method could be used to create a Union query. If I get the chance I’ll knock up an example.
ii) What about multi-user issues? If two users use this method at very nearly the same time, we might get unpredictable results as the “For Each Record In” block for both users might run at the same time. If searching for different text Access is going to get confused. To get round this, I would suggest adding a separate table to hold details of Company ID’s and the name of the CurrentWebUser and base qselFilteredCompanies on this.
iii) What about using this in a client front end? Since we are changing a potentially significant number of records in tblCompanies and thus these will all need to be synchronised with the Sharepoint database and then out to other users of client front end, we could find ourselves generating a fair bit of network traffic. I am not sure whether Sharepoint synchronises only changed values in fields or the entire row of a record even if only one field gets changed. If it does synchronise the entire row of data, putting the CompanyID and FilterOnMe fields into a separate table may be worthwhile if our table tblCompanies has many fields. Creation of such an associated table is fairly simple. Create a table called tblCompaniesDistinct with its own ID field, add a Lookup & Relationship field (pointing to the CompanyID field back in tblCompanies) and move the FilterOnMe field from the table tblCompanies across into tblCompaniesDistinct as well. Allow cascading deletions when setting up the table so that deleting a record in tblCompanies causes the deletion of the corresponding field in tblCompaniesDistinct. Now add an AfterInsert data macro in tblCompanies to create the associated record in tblCompaniesDistinct, setting the CompanyID field in tblCompaniesDistinct to the value in tblCompanies and also setting FilterOnMe to False.
iv) It will be interesting to see how fast this runs when the database contains lots of records. We try to avoid using “For Each” blocks in VBA, preferring to use SQL if at all possible, because retrieving data via SQL is nearly always faster (30 times faster in one database I took over, but then that was across a network whereas this is on the server).
Since Access 2010 will be able to use web services via linked tables under certain circumstances, I decided to install Sharepoint Designer 2010 RTM on my PC. Microsoft had released the Access 2010 runtime version on Monday 10th May, one day ahead of the official business launch of Office 2010. When Office 2007 came out, the Access 2007 runtime was due to come out “shortly afterwards”, but didn’t emerge until several months later, had to be “recalled” and was then relaunched. It also had some really bad (for one of my customers and me) limitations, including the inability to take Sharepoint 2007 data offline. During the beta testing of Access 2010 it was stated that the Access 2010 runtime would be made available around the time of the launch of Office 2010 proper. I was very impressed to see that promise kept.
With a gleeful heart I therefore decided to install Access 2010 runtime and Sharepoint Designer 2010 onto my main PC (Windows 7 / Office 2007 Enterprise), but when I did it all went a bit wonky. I can’t remember at what point I noticed things going wrong, but it was something like this.
Finally decided to uninstall Access 2010 runtime, leave SPD 2010 in place, totally uninstall Office 2007, re-install Office 2007 and install its SP2. Initially Office 2007 refused to install (Mum!), but again setting all subfolders and files in C:\Windows\Installer to read-write allowed it to install when I tried again. SP2 looked like it was going to fail, but when it hit a problem, it offered the chance of retrying and this time it worked.
I now went into Access 2007 and checked out CurrentProject.Connection and it worked. Hooray! I also tried out SPD 2010 and that connected to my trial Access 2010 Services site OK, so life was looking good. I then promised myself that I would not chance my luck and install Access 2010 runtime, but gave in about 10 minutes later and installed it. Hey, it works.
So I now have Office 2007 full version, Sharepoint Designer 2010 and Access 2010 runtime installed and apparently working on my PC. Total Access Analyzer 2007 also appears to be working OK. A web database I knocked up for my own learning purposes works nicely with Access 2010 runtime.
My PC has loads of different stuff installed on it as it is my development PC, so you might not have the same problems on yours. I hope not. However, even with my PC, though it was a lot of hassle, it all seems to work now.
Office 2010 RTM is now available to Technet and MSDN subscribers, but not to Microsoft Action Pack subscribers. http://technet.microsoft.com/en-gb/subscriptions/bb892756.aspx says, “Software provided through TechNet Plus is for evaluation purposes and cannot be used in a live production environment.” so unless I fork out for an MSDN subscription (which I’m not going to do), then it looks like I need to wait another month until the new Microsoft Action Pack Development and Design subscription comes out some time week commencing 24th May. Oh well, only another 4 and a bit weeks till I get my hands on the RTM version of Access :-(.
I’ve been having quite a bit of trouble with a web database. The database initially published OK to Accesshosting.com, but I had some problems uploading some attachments via Access itself. I got round it by uploading using the web UI and then forgot all about it.
At some point I am probably going to have to republish this web database because Accesshosting.com are currently proving a free trial (they are excellent people at Accesshosting.com) and will go commercial some time in May (if I understand things correctly). So, rather than rush things at that time, I thought I would impress my wife and make sure I could republish my database OK. I created a local version of the database (File-->Save & Publish-->Save Database As-->Save As Local Database-->Save As) OK, but when I tried to publish to a new site (still at Accesshosting.com), I got a rather vague error message. When the publication of a web database fails, Access generates records in a special table to which you are given a link. The fields in this table are:
However, the two records generated only gave the Issue and Object Type, with Reason, Object Name and Field Name being blank. Hmm. Not particularly impressive. In each case the Object Type was “table” and the two Issues were:
That’s not a lot of help. I therefore did the following:
After all the data had been appended to the table in the new local copy, I was then able to publish it with no problem. So, problem solved? Nope. I hadn’t tried it with attachments involved.
I once again tried to republish my original database and got my original error messages back. I eventually found that Access was unable to publish some of the attachments due to their size. Some days ago I had been unable to upload some attachments to my original database using the Access UI, but did manage to upload them using the web UI. At that point I forgot about them and it was only when I was unable to republish my database when it had attachments that I remembered this. So it looks like Access really does have a problem publishing not very big (a few hundred KB) attachments, both with normal saving of attachments to a Sharepoint site and when publishing a database, yet saving them via the web UI works OK.
It would be helpful to have a meaningful error message when Access fails to publish for this reason (and to know if there are any settings which can be altered to allow larger attachments to be saved/published to Sharepoint).
I did some changes to the structure of a table whereby I converted a text field into a lookup and relationship field of the same name by turning off Autocorrect renaming the original text field, creating the new lookup field using the original field name and then deleting the old field. I think I must have thoroughly confused Access and Sharepoint as I then started receiving stating that my database is in an invalid state when trying to save a record in a web database. It suggested I should contact a site owner (not the site owner, but a site owner). Since the only site owner on my Sharepoint site is me, it is suggesting I start talking to myself. Now that has never produced anything sensible in 50 years, so why should it start happening now?
Instead, I headed for the USysApplicationLog table. This is a highly important tool for Access 2010 developers working with Sharepoint. When Access hits a problem it enters details into this table and you can also put data into it yourself, e.g. by using the LogEvent macro action in a data macro. Maybe I’ll post more info on using LogEvent another time.
However, today the USysApplicationLog table came to my rescue. It had 3 new rows in it. The first two seemed totally irrelevant. One was complaining about a missing field in a query (it spoke of an “invalid property definition”). Now I know that was wrong, because I had deleted that field. The second was complaining about a missing command button in a form. I know that was wrong too, because I had renamed the button. Hmm. Not too impressive so far. The third record was more use as it told me that a field was missing in a report. I opened the report, deleted the textbox whose field I had previously deleted and then tried resynchronizing. This still left me with the first two errors in USysapplicationLog. I tried an old-fashioned Compact and Repair, but that didn’t work.
When I had a similar problem once before, I was advised to try “re-hydrating” my application, i.e. clicking on “Open in Access” in the web UI via Options, but this did not work as Sharepoint informed me that my site was down. Joy, deep joy.
Next step was to save my database as a local copy, rename it and try publishing this to a new URL. Tried this, but this didn’t work either though it had worked on a previous occasion when I had confused Access and Sharepoint. One thing it suggested I do was use the web compatibility checker (Why? I thought it did that automatically before trying to publish), but that showed no problems. I was getting a bit concerned by this time. I had quite a bit of live data in my database. Yes, I know you are not meant to rely on beta software, but how else are you going to properly test stuff unless you’ve got all day to fiddle around with play data?
Moving back to my original database, I thought I would try using the hidden methods SaveAsText and LoadFromText to save the offending objects (the query and the form) to text and then read them back in again. However, as I typed this into the Immediate Window, I noticed SaveAsAXL from Intellisense and that this is not a hidden member, so I tried that instead of SaveAsText. I then used LoadFromAXL to import the objects into the database again. Note that I used SaveAsAXL on the query which was a client query, not a web query, as well as on the web form. Finally, I tried synchonising this original database and, hey presto, everything was working again. Phew! PTL!
So what is the moral of the story? Before you panic when you get told your database is up the chute, have a look in your USysApplicationLog and consider using SaveAsAXL/LoadFromAXL to overcome any corruption. DISCLAIMER: You should make backups before using SaveAsAXL/LoadFromAXL. I have not seen any documentation on how to use these or even if you should use them. I was getting a bit desperate, so had to use them, but be careful what you are doing if you yourself decide to use them. You do so at your own risk!