Late Binding

[tmort]

I have an Access db developed with Access 2003 that will work (with
adjustments to references) with 2002 and 2000. Presumeably it will also work
with Ofice 2007.

So far I've manually adjusted references for each machine I install it on.
I'm told that I should look into late binding. I've found some discussions
on how to do this with Excel and Outlook. It looks like I change the way I
call thse by changing as object to application or vice versa where I call
them.


I'm pretty sure that I've had to change some of the other references as well
and I don't know if I can use late bindings with the others or how.


The references I use are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 2.5/3.51 Compatibility Library
OLE Automation
Microsoft ActiveX Data Objects 2.7 Library
Microsoft Excel 11.0 Object Library
Microsoft Outlook 11.0 Object Library

Can someone help me with this? I saw another post that says to look at the
MVP site, but, I haven't been able to locate it.

Thanks

 

[Klatuu]

The difference between early and late binding is where you declare the object
to be an Excel Application object. If you Dim the object as an Excel
Application, it binds the version of Excel that is installed on your
computer, that is Early Binding. If you dim the object as an object, and
declare it as an Excel Application in the GetObject or CreateObject methods,
that is Late Binding. In this case, it uses the version of Excel on the
user's computer.

I prefer late binding. You don't have to worry about a specific version.
It is a little slower to load, but not enough to be a problem.

 

[Tom Wickerath]

Try compiling your code first: Debug | Compile ProjectName. Does it compile
without any errors?

You should not need this reference any longer, but you very well may have
DAO code:

Microsoft DAO 2.5/3.51 Compatibility Library

I would try unchecking this one, compiling again. You will likely get a
compile error. If so, select the Microsoft DAO 3.6 Object Library (Access
2000/2002/2003 or the 3.51 Object Library for Access 97). Then try compiling
your code again. If you get a compile error with the DAO 3.6 (or 3.51) Object
Library, but not with the Microsoft DAO 2.5/3.51 Compatibility Library, then
your VBA code should be updated to modern coding practices.

Here is an example of using late binding with Excel that I created:

http://home.comcast.net/~tutorme2/samples/Statistics.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Douglas J. Steele]

AFAIR, the 2.5/3.51 Compatibility Library only works with Access 97.

 

[tmort]

I'm using Access 2003. I tried changing DAO to 3.6 and got a "User Type not
Defined Error". It didn't like this line:

Dim m_oSmtp As AOSMTPLib.Mail

I suppose there could be more.

I don't know what the proper syntax should be. Are you saying that once its
compiled if it uses 3.6 it will work with 2000 and up?


How about the other references: OLE, ActiveX, etc?

Thanks

 

[Klatuu]

I don't know what AOSMTPLib.Mail is, but it is not DAO.

 

[tmort]

I haven't done much with this db in awhile. It was an ActivX email client I
was experimenting with to send secure email.

I have removed that and can now compile with 3.6.

After I compile with 3.6 and uncheck it it chokes on the following code when
I compile again with a user definined type not defined error:

Public Function GetDatabaseName(dbDatabase As Database, sDatabaseName As
String) As Long

'**********************************************************************
'**********************************************************************
' ***
' *** Parameters: dbDatabase.....Database object that the name should be
' *** retrieved from
' *** Parameters: sDatabaseName.....Return variable, leave blank
' ***
' *** Returns: Integer success code....... 0 - Success
' *** 1 - Failure (no slash found)
' *** 2 - Failure (zero length database name)
' *** 2 - Failure (zero length file name)
'**********************************************************************
'**********************************************************************

Dim iPosition As Integer
Dim iReturn As Integer

sDatabaseName = "###ERROR###"

If Len(dbDatabase.name) = 0 Then ' No path or filename
iReturn = 2
ElseIf Right(dbDatabase, 1) = "\" Then
' Last part of path/filename is a "\" which is invalid
iReturn = 3
Else
iReturn = 1 ' Assume there is no slash and the file is invalid
iPosition = Len(dbDatabase.name)

Do While (iPosition > 0) And (Mid(dbDatabase.name, iPosition, 1) <>
"\")
iPosition = iPosition - 1
If Mid(dbDatabase, iPosition, 1) = "\" Then
sDatabaseName = Right$(dbDatabase.name, Len(dbDatabase.name) _
- iPosition)
iReturn = 0
End If
Loop
End If

GetDatabaseName = iReturn
' Return the status. Databasename will be returned in sDatabaseName
variable

End Function

I don't know what AOSMTPLib.Mail is, but it is not DAO.

Also I tried compiling it with 3.6 checked and ActiveX unchecked and it
wouldn't compile although after I removed the code mentioned above I am not
knowingly calling on any ActiveXs

 

[Tom Wickerath]

Hi Doug,

I'm sure it's intended use was to assist folks with upgrading older code to
Access 97, but as a quick experiment, I just tried substituting the
compatibility library for the DAO 3.6 Object Library in Access 2000, 2002 and
2003. In all cases, the code compiled okay. (I didn't try running any code,
to see if any run-time errors might result, but I am able to compile the code
with the Microsoft DAO 2.5/3.51 Compatibility Library included as a
substitute for the DAO 3.6 Object Library).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

I have removed that and can now compile with 3.6.

After I compile with 3.6 and uncheck it it chokes on the following code when
I compile again with a user definined type not defined error

It is not surprising that you need a reference to the DAO 3.6 Object
Library. This is a very common reference found in many Access applications,
and is a default reference in new applications created using Access 2003 (but
not 2000 or 2002). Personally, I wouldn't bother attempting to make the DAO
code late bound. Just worry about any references to version specific
libraries, such as the Excel or Word Object Libraries.

You may find this compiled Help file helpful:

The Office XP Automation Help file is available for download
http://support.microsoft.com/?kbid=302460


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[tmort]

So are you saying that DAO is not version specific and 3.6 will work with
older installation?

 

[Douglas J. Steele]

DAO 3.6 is the correct reference for Access 2000, Access 2002, Access 2003
and Access 2007 (when not converting to the Access 2007 ACCDB file format)

 

[Douglas J. Steele]

What I meant to say is that as far as I know, that reference doesn't do
anything in any other version than Access 97.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I'm sure it's intended use was to assist folks with upgrading older code
to
Access 97, but as a quick experiment, I just tried substituting the
compatibility library for the DAO 3.6 Object Library in Access 2000, 2002
and
2003. In all cases, the code compiled okay. (I didn't try running any
code,
to see if any run-time errors might result, but I am able to compile the
code
with the Microsoft DAO 2.5/3.51 Compatibility Library included as a
substitute for the DAO 3.6 Object Library).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[tmort]

Thanks,

What about the AciveX reference?

Since I took out the code that deals with using the ActiveX email client I
am not aware that I am using any ActiveX.

I still have the smtp ActiveX client registered with Windows.

Any ideas

 

[Douglas J. Steele]

Microsoft ActiveX Data Objects 2.7 Library is the reference for ADO, an
alternate means of interacting with the data.

You can try unchecking the reference, and then compiling your application
(under the Debug menu while in the VB Editor). If you're not using ADO, the
application should compile properly. If it doesn't compile, it may mean you
need to add the Microsoft ActiveX Data Objects 2.7 Library reference back.
(Not being able to compile, though, could be due to some other error, not
the fact that you're using ADO)

 

[Tom Wickerath]

It allowed the DAO code in my test database to compile....
The only DAO reference I had checked for the tests that I ran earlier this
morning, in Access 2000, 2002 and 2003, was the Microsoft DAO 2.5/3.51
Compatibility Library.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[tmort]

I tried to recompile without an ActiveX reference and it didn't work. I see
I am using adodb.

It will compile with ActiveX 2.1 (although I haven't checked functuionality
yet) I see that what libraries are availabe are dependent on a number of
things including service packs. Is it safe to assume that 2.1 or higher
would be on Office 2000 und up installations.

Would you suggest this approach?

Thanks

 

[Tom Wickerath]

Is it safe to assume that 2.1 or higher

would be on Office 2000 und up installations.

In general, this should be a pretty good assumption, since the ADO 2.1
library is added by default to all new databases created using Access 2000.
However, where is your compile error when you remove a reference to ADO
totally? If it is in old code that you are no longer using, then you might be
better off to just comment out (or remove) that code. If it turns out that
you really do need a reference to this library, I'd suggest becoming familiar
with the MDAC Component Checker utility, because you will undoubtedly come
across one or more PCs with some type of configuration problem. The symptoms
include everything working fine on all other PCs, but not on the affected PCs.

Component Checker: Diagnose problems and reconfigure MDAC installations
http://support.microsoft.com/kb/307255


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[tmort]

Thanks,

The code that uses ADO is necessary code.

I have looked at the component checker.

I take it that:

MDAC 2.1 - refers to ActiveX 2.1
MDAC 2.5 - refers to ActiveX 2.5
etc.

Is this correct?

 

[Tom Wickerath]

The code that uses ADO is necessary code.

And you are positive that it cannot be re-written as DAO code (since you've
already established that your database requires a checked reference to the
DAO Object Library)? If you are just hitting data in a JET database (ie.
..mdb file), you are likely better off to stick with DAO. On the other hand,
if you are accessing data from another source, such as SQL Server, Oracle,
Exchange, etc., then ADO is likely your best choice.

I take it that:

MDAC 2.1 - refers to ActiveX 2.1
MDAC 2.5 - refers to ActiveX 2.5
etc.

Is this correct?

Sounds reasonable. But do I know for sure? The answer is "no".


Here are some additional links you may find helpful:
http://support.microsoft.com/kb/814521
http://msdn2.microsoft.com/en-us/library/ms810810.aspx


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[tmort]

And you are positive that it cannot be re-written as DAO code (since you've
already established that your database requires a checked reference to the
DAO Object Library)? If you are just hitting data in a JET database (ie.
.mdb file), you are likely better off to stick with DAO. On the other hand,
if you are accessing data from another source, such as SQL Server, Oracle,
Exchange, etc., then ADO is likely your best choice.


Sounds reasonable. But do I know for sure? The answer is "no".


Here are some additional links you may find helpful:
http://support.microsoft.com/kb/814521
http://msdn2.microsoft.com/en-us/library/ms810810.aspx


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________