Portland Access User Group

Portland Access User Group Conference

September 28-30, 2019

The PAUG Database Designer International conference brings together a wide range of Access developers, consultants, power users and Access enthusiasts. This marks the 21st anniversary of the conference. We will once again be returning to the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing.

Monday, June 28, 2010

New Sample: 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.

20Week 26
19Week 25
18Week 24
17Week 23
16Week 22
15Week 21
14Week 20
13Week 19
12Week 18
11Week 17
10Week 16
9Week 15
8Week 14
7Week 13
6Week 12
5Week 11
4Week 10
3Week 9
2Week 8
1Week 7

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:


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?


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

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)