Monday, June 28, 2010

New Sample: RollingAverages.MDB


RollingAverages.MDB

by Roger Carlson

This sample illustrates two different ways to create a "Rolling Average" query.  It averages the latest 13 months in the sequence.

Sequence
tWeek
tValue
RollingAverage
20Week 26
10
39.8461538461538
19Week 25
11
43.8461538461538
18Week 24
22
48.4615384615385
17Week 23
44
47.6923076923077
16Week 22
55
45.4615384615385
15Week 21
44
46.1538461538462
14Week 20
22
45.4615384615385
13Week 19
77
48.5384615384615
12Week 18
88
46.1666666666667
11Week 17
11
42.3636363636364
10Week 16
74
45.5
9Week 15
35
42.3333333333333
8Week 14
25
43.25
7Week 13
62
45.8571428571429
6Week 12
71
43.1666666666667
5Week 11
12
37.6
4Week 10
15
44
3Week 9
64
53.6666666666667
2Week 8
35
48.5
1Week 7
62
62

The first two use DCount and the second two use a Sub Query.  You must identify a unique column in the query to create the sequence on.  I used an autonumber ID field, which while in order, has gaps in it. 

 The DCount method creates an updateable recordset.  The SubQuery method produced non-updateable recordsets.

 You can find the sample here: RollingAverages.mdb.

.

Friday, June 25, 2010

What’s the Difference Between Early Binding and Late Binding?


Some time ago, I ran into this question on the internet:

Question:

This is something I've never really figured out about Office Automation. These all seem to be equivalent. Is there a preferred version?

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")


------

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")


------

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")


-----

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")


------
The only difference I can find is the last one does not require a Reference to Excel set. Any advantage to or against this?

Answer:

The difference is between what's called Early Binding and Late Binding.

Early binding gives you faster access at runtime to an object's methods and properties and a smaller executable. This is because early binding lets the compiler "hard code" in the links between the app and the object. Early binding also ties you to whatever object is specified at design time because, under the hood, early binding uses the object unique identifier to flag all references. To use early binding you must instantiate the object with the New keyword. At runtime, New finds the object in the Windows registry using a direct access based on the object's unique identifier.

Late binding gives you slower access at runtime and a larger executable because code to search for the object's methods and properties that you ask for must be searched for at runtime. Late binding allows you to load a variety of different objects provided only that the object has the right method names (with the right parameters). To use late binding you must instantiate your object with CreateObject, which takes longer because the code performs a lookup in the registry using the object's ProgId.

To get IntelliSense support at design time you must declare your variable as a specific datatype (i.e. not "Object"). To use IntelliSense you must also add a reference to the object's library which is where the definition of the object's datatype is held. However, you can still use either early or late binding by using either New or CreateObject (respectively) to instantiate the object.

So, the first code sample is an example of Early Binding with IntelliSense because it instantiates the object with the New keyword and declares the variable with its datatype.

The second code sample is an example of Early Binding with without IntelliSense because it instantiates the object with the New keyword, declares the variable as "Object". This is probably the least useful because it requires a reference set but still doesn't give IntelliSense.

The third sample is an example of Late Binding with IntelliSense because it does not use the New keyword to instantiate the object, but it does declare the variable with the datatype.

The last sample shows Late Binding without IntelliSense.

Traditionally, Access has been notoriously bad at resolving references at runtime when a new version of a library was installed on the computer or when the Access application was moved to a different computer.

To avoid this problem, I've made it a practice to use CreateObject to instantiate the object because it allowed me to avoid having to add a reference to my Access project. However, to avoid giving up IntelliSense, I'll add a reference to the object's library at design time and declare their variables with a specific datatype. Then, when it was time to release the application, I'll remove the reference to the library and change the declarations to use "Object" rather than a specific datatype.

In terms of the above samples, I'll design using sample 3 and convert it to sample 4 when I put it into production.

Wednesday, June 16, 2010

Access 2010 Web Databases: Other Compatibility Errors

(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)


In this blog series, I'm discussing how to convert an existing Access application to a web application (the series starts here: How Can I Put My Access Database On The Web?). As with any database project, I started with the data.

When I attempted to upload my existing tables into SharePoint, I got a number of errors (Uploading the Data to SharePoint). I dealt with some of them (primary keys and unique indexes) in my last post (How can I create a Compound Unique Index?). I discussed errors in creating relationships in a web application (Creating Relationships). I talked about loading the tables into SharePoint (Uploading To SharePoint). Last time, I showed how to view the tables in SharePoint (Viewing In SharePoint).

In this final post discussing moving legacy tables from Access to SharePoint, I'll look as some other common errors that keep your tables from uploading to SharePoint.

Other Compatibility Errors

  1. Property value contains one or more characters that are incompatible with the Web.
    Table, field, and control names cannot contain characters that the Access client will accept. They cannot be longer than 64 characters and may not contain any of the following: / \ : * ? "" < > | # <TAB> { } % ~ & period (.), exclamation point (!), square brackets ([]), leading space, or begin with an equal sign (=).
  2. Only one of the Allow Zero Length property and Required property should be set to 'true' to be compatible with the Web.
    As the error says, you can't set both the Allow Zero Length and Required property of a field to "yes".
  3. Property value should to be empty or set to 'auto' to be compatible with the Web.This error refers to subdatasheets, which are not allowed in SharePoint lists. To correct it, set the Subdatasheet Name property of the table to [None] or [Auto]. You'll also get this message if you have values in the Link Child Fields and Link Master Fields properties. These properties must be empty.
  4. Calculated Column expression has an input field of a type incompatible with the Web, such as AutoNumber, Lookup, Memo, Hyperlink or Binary.
    Calculated columns are new to Access 2010, so they won't be in legacy systems. However, if you do use them, you can only use the following data types: Text, Number, Currency, Yes/No, Date/Time.
  5. Calculated Column result data type is incompatible with the Web.
    Likewise, the result of a calculated column can only be of the following data types: Text, Number, Currency, Yes/No, Date/Time.
  6. Column data type for value list lookups must be 'Text' to be compatible with the web.
    If you use a Value List for a lookup field (which I strongly advise against, anyway), it has to be filled with Text values. You cannot use numbers. A better solution, however, would be to create reference lists that you can query to fill the list.
There are others. I won't try to list them all here. In the Web Compatibility Issues table created by the Compatibility Check, there will be a link referencing each error.

Then, of course, there are errors (many, many of them) associated with uploading your application (forms, reports, etc.) to the web. I'll deal with those in a later series.
.

Friday, June 11, 2010

Access 2010 Web Databases: Viewing Tables In SharePoint

(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)


In this blog series, I'm discussing how to convert an existing Access application to a web application (the series starts here: How Can I Put My Access Database On The Web?). As with any database project, I'm starting with the data.

When I attempted to upload my existing tables into SharePoint, I got a number of errors (Uploading the Data to SharePoint). I dealt with some of them (primary keys and unique indexes) in my last post (How can I create a Compound Unique Index?). I discussed errors in creating relationships in a web application (Creating Relationships). Last time, I talked about loading the tables into SharePoint (Uploading To SharePoint).

This time, I want to take a look at the tables in SharePoint itself. It's not really necessary. You can and should do everything you need to do in Access. However, viewing your application in SharePoint can be instructive.

Go to File > Info:

[click to enlarge]

And click the link. The opening screen will look like this:

[click to enlarge]

The Design With Access link will allow you to make changes to the application in Access. I should point out here that you should always make any changes in Access and not directly in SharePoint. You can easily mundge your database into uselessness. Clicking the link will download a new copy of the application (with an ACCDW extension) to your hard drive. Make your changes and then save it back to SharePoint.

If you want to delete the application, under Settings, click the Delete This Site link. You won't do this often with a production database, of course, but if you're experimenting, you might need to do this. In the course this series, I did it quite a lot.

Table Properties
 I can look at my table properties as well. Navigate to your lists/table by going to a URL like so:

http://<yourserver>/<yoursite>/Lists/<yourtablename>/Allitemsg.aspx

You should see your table/list in datasheet view. Click the List Settings button on the List tab of the SharePoint Ribbon.
[click to enlarge]

Clicking AuthorID, I can view the properties of that table:

[click to enlarge]

From this, I can see that all the properties I set up in Access – including the relationships – made the transition to SharePoint. However, I want to re-emphasize that although you can view your tables in SharePoint, you should never modify them there. With Access Services, everything begins and ends in Access.

Next my next post (Other Compatibility Errors), I'll finish up the data portion of this series by looking at other data related issues that you need to watch out for.
.

Thursday, June 3, 2010

Access 2010 Web Databases: Uploading to SharePoint

(Note: The information below is applicable to Access 2010 web databases. It may not be correct for Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)

In this blog series, I'm discussing how to convert an existing Access application to a web application (the series starts here: How Can I Put My Access Database On The Web?). As with any database project, I'm starting with the data.

When I attempted to upload my existing tables into SharePoint, I got a number of errors (Uploading the Data to SharePoint). I dealt with some of them (primary keys and unique indexes) in my last post (How can I create a Compound Unique Index?). Last time I discussed errors in creating relationships in a web application (Creating Relationships).


I'm finally ready to upload the tables to SharePoint. Before I do that, however, I need to check the compatibility again (see Compatibility Checker). Assuming there are no more errors to correct, I can Publish To Access Services.

Access Services


Obviously, I need a SharePoint 2010 site with Access Services installed. While it's always possible to set up my own server, there are various hosting companies that provide Access Services, and there will be more in the coming years.

Important Note: Hosting services for this blog series are provided free of charge by http://www.accesshosting.com/. They are one of the first SharePoint providers specializing specifically in Access Services. If you'd like to try anything from this blog series, AccessHosting provides a 30-day free trial. They also have a Developer Sandbox option for developers to use Access Services and prototype solutions for customers beyond the 30 day trial.

So once I have my hosting account, I can fill in the appropriate information: the Server URL and the Site Name. The Server URL is my account on the SharePoint host and the Site Name is the name I want the site to have. In my case I chose the name of my database.


Once the information is filled in, the Publish to Access Service button will become active. Clicking it will bring up a login window.

After entering my password and clicking OK, I'll see a dialog box that shows the various object being uploaded to SharePoint


When it's done, I'll see the following:

Publishing to SharePoint produces a number of irreversible changes to the database, which I'll discuss presently. Fortunately, Access makes a copy of the original, pre-upload database with a "_Backup" suffix, so if you have problems, you can easily go back to your original version.

Database Changes

The first and most obvious change is that my tables now look different


The icon in front indicates they are now SharePoint Lists. As such, you can no longer go into Table Design view to modify your table. You must make any changes in the Table Tools Ribbon.




Between them, you have most of the table design ability of the Table Design View, limited, of course, to those properties that SharePoint list support.  Creating new objects like Queries, Forms, and Reports are more restricted.


Some queries like Aggregate Queries and Union Queries (to name just two) are not supported and forms can only be created in Layout View. Certain query, form, report, and code types are only supported in the "Client", that is, it will only run when used in Access as opposed to a Web Browser. This is useful in a Hybrid Web Application, but not in a Browser-Only application (which is what I'm hoping to develop here).

Many of the Database Tools are also absent in a Web Application. These include the Relationships Window, Performance Analyzer, Table Analyzer, and uploading data to SQL Server and SharePoint.


There are other differences that vary depending on the specifics of your database, but once you've uploaded your database to SharePoint, you need to be aware that some Access features are limited.

In my next post, (Viewing In Sharepoint), I'll look at the tables in SharePoint.

.

Wednesday, June 2, 2010

CreateDatabase Problem in Access 2007 MDB

I ran into an interesting situation that I thought might be interesting to blog.

I have an Access 2003 format database that I am running in Access 2007 (my company recently upgraded, but I'm keeping the MDB file format for now).  In it, I have a line of VBA code like this:

Set db = CreateDatabase("C:\MyDatabase.mdb", dbLangGeneral)

It creates a blank Access database for me to export data to.  The database created is used as a back-end database for another MDB, that is, the tables are linked into another database application, which is also in 2003 format.

In this other front-end, I've started getting errors that said:

Microsoft Office Access does not support linking to an Access database or Microsoft Office Excel Workbook saved in a format that is a later version than the current database format.


This is odd because I'm working all in the Access 2003 MDB format.  But when I investigated, it turned out that the database created with the above code was an ACCDB format database (Access 2007) but with an MDB extension.  I can tell because if I open it, the LACCDB locking file is created.
 
In retrospect the problem is obvious.  The CreateDatabase method creates the database in the default format, which for Access 2007 is dbVersion120.  So I needed to specify the format I want:
 
Set db = CreateDatabase("C:\MyDatabase.mdb", dbLangGeneral, dbVersion40)
 
.