Late Binding

[Cindy]

I have an Excel 2000 application that uses references to
Active X Data Objects 2.5 and Outlook 9 Object library. I
need to instill late binding because users have different
versions of MSOffice and this causes a problem with the
library reference files. I did post before on this and
received some feedback, websites, etc. but I am confused
especially on how to "late bind" the ActiveX Data Objects
2.5 library file. Please help if you can.

Thank you.

Cindy

 

[Guest]

(ADO) ActiveX Data Object 2.5 Library

Dim ADOR as Varian
Set ADOR = CreateObject("ADODB.RecordSet"

to talk to your databased, you need

Cnn={connection string, usually ODBC
Sql =P SQL statement

You should have these already

ADOR.Open Sql, Cnn ' Opens the database and returns the result of your SQ

ADO has several other object hierarchies, Connection, Stream, et

OUTLOOK

Dim OLK as Varian
Set OLK = CreateObject("Outlook.Application"

DOWNSIDE: 1. No intellisense with late bindin
2. You need to hard code predefined constants

For ADO: Download the MDAC SDK from Microsoft
For OUTLOOK: I has a reference to a Microsoft page ... can't remember right now ... will send another message if I remember

 

[Guest]

For Outlook, refer t

http://msdn.microsoft.com/library/d...tml/rehowOverviewOfProgrammingEnvironment.asp

 

[Jake Marx]

Hi Cindy,

Not sure what feedback you already got on this, but I'll give it a shot.
Typically, I develop all code using Early Binding (set a reference, Dim
objects explicitly, use named constants from libraries). Once everything is
working, I modify my project to use Late Binding. To do this, you need to:

1) Get values of all named constants from libraries (in your case, ADO and
Outlook). For example, if your code has adStateOpen in it, you need to get
the actual value of this constant so that when you remove the reference to
ADO, your project will work. If you did not replace the named constant with
its value, you would get a syntax error (if you use Option Explicit) or
unexpected results (as the constant will evaluate to zero when the library
reference is removed). The easiest way to get the value of a constant is to
print it to the Immediate Window:

?adStateOpen
1

Obviously, you'll want to do this while the reference is still set.

2) Replace all explicit object types with Object. And replace all Set x =
New y with a CreateObject call. So if you have:

Dim cnMain As ADODB.Connection
Dim cdEmp As ADODB.Command

Set cnMain = New ADODB.Connection
Dim cdEmp = New ADODB.Command

You would replace this with:

Dim cnMain As Object
Dim cdEmp As Object

Set cnMain = CreateObject("ADODB.Connection")
Set cdEmp = CreateObject("ADODB.Command")


That should do it. If you have any further questions or don't understand
something here, let us know.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bob Phillips]

Jake's reasoning is very sound on this. I have posted a worked example
previously (using Outlook) on this same topic. You can read it at
http://tinyurl.com/2qern

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Cindy,

Not sure what feedback you already got on this, but I'll give it a shot.
Typically, I develop all code using Early Binding (set a reference, Dim
objects explicitly, use named constants from libraries). Once everything is
working, I modify my project to use Late Binding. To do this, you need to:

1) Get values of all named constants from libraries (in your case, ADO and
Outlook). For example, if your code has adStateOpen in it, you need to get
the actual value of this constant so that when you remove the reference to
ADO, your project will work. If you did not replace the named constant with
its value, you would get a syntax error (if you use Option Explicit) or
unexpected results (as the constant will evaluate to zero when the library
reference is removed). The easiest way to get the value of a constant is to
print it to the Immediate Window:

?adStateOpen
1

Obviously, you'll want to do this while the reference is still set.

2) Replace all explicit object types with Object. And replace all Set x =
New y with a CreateObject call. So if you have:

Dim cnMain As ADODB.Connection
Dim cdEmp As ADODB.Command

Set cnMain = New ADODB.Connection
Dim cdEmp = New ADODB.Command

You would replace this with:

Dim cnMain As Object
Dim cdEmp As Object

Set cnMain = CreateObject("ADODB.Connection")
Set cdEmp = CreateObject("ADODB.Command")


That should do it. If you have any further questions or don't understand
something here, let us know.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have an Excel 2000 application that uses references to
Active X Data Objects 2.5 and Outlook 9 Object library. I
need to instill late binding because users have different
versions of MSOffice and this causes a problem with the
library reference files. I did post before on this and
received some feedback, websites, etc. but I am confused
especially on how to "late bind" the ActiveX Data Objects
2.5 library file. Please help if you can.

Thank you.

Cindy

 

[Cindy]

Hi Jake,

I switched my references over as you stated but I have a
compile error on the last line of this code, at
adOpenKeyset, adLockOptimistic, adCmdTable.

I included some of my code thinking it might help. Thanks
so much.


'Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cn, rs As Object

Application.Cursor = xlWait

' connect to the Access database
Set cn = CreateObject("ADODB.Connection")
'Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=T:\PDA\PDA Request.mdb;"
' open a recordset for PDATracking Table
'Set rs = New ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open "PDATracking", cn, adOpenKeyset,
adLockOptimistic, adCmdTable

-----Original Message-----
Hi Cindy,

Not sure what feedback you already got on this, but I'll give it a shot.
Typically, I develop all code using Early Binding (set a reference, Dim
objects explicitly, use named constants from libraries). Once everything is
working, I modify my project to use Late Binding. To do this, you need to:

1) Get values of all named constants from libraries (in your case, ADO and
Outlook). For example, if your code has adStateOpen in it, you need to get
the actual value of this constant so that when you remove the reference to
ADO, your project will work. If you did not replace the named constant with
its value, you would get a syntax error (if you use Option Explicit) or
unexpected results (as the constant will evaluate to zero when the library
reference is removed). The easiest way to get the value of a constant is to
print it to the Immediate Window:

?adStateOpen
1

Obviously, you'll want to do this while the reference is still set.

2) Replace all explicit object types with Object. And replace all Set x =
New y with a CreateObject call. So if you have:

Dim cnMain As ADODB.Connection
Dim cdEmp As ADODB.Command

Set cnMain = New ADODB.Connection
Dim cdEmp = New ADODB.Command

You would replace this with:

Dim cnMain As Object
Dim cdEmp As Object

Set cnMain = CreateObject("ADODB.Connection")
Set cdEmp = CreateObject("ADODB.Command")


That should do it. If you have any further questions or don't understand
something here, let us know.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have an Excel 2000 application that uses references to
Active X Data Objects 2.5 and Outlook 9 Object library. I
need to instill late binding because users have different
versions of MSOffice and this causes a problem with the
library reference files. I did post before on this and
received some feedback, websites, etc. but I am confused
especially on how to "late bind" the ActiveX Data Objects
2.5 library file. Please help if you can.

Thank you.

Cindy

.

 

[Chip Pearson]

Cindy,

You need to replace adOpenKeyset, adLockOptimistic, and
adCmdTable with their numeric equivalents, which are 1, 3, and 2,
respectively.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Hi Jake,

I switched my references over as you stated but I have a
compile error on the last line of this code, at
adOpenKeyset, adLockOptimistic, adCmdTable.

I included some of my code thinking it might help. Thanks
so much.


'Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cn, rs As Object

Application.Cursor = xlWait

' connect to the Access database
Set cn = CreateObject("ADODB.Connection")
'Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=T:\PDA\PDA Request.mdb;"
' open a recordset for PDATracking Table
'Set rs = New ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open "PDATracking", cn, adOpenKeyset,
adLockOptimistic, adCmdTable

-----Original Message-----
Hi Cindy,

Not sure what feedback you already got on this, but I'll give it a shot.
Typically, I develop all code using Early Binding (set a reference, Dim
objects explicitly, use named constants from libraries). Once everything is
working, I modify my project to use Late Binding. To do this, you need to:

1) Get values of all named constants from libraries (in your case, ADO and
Outlook). For example, if your code has adStateOpen in it, you need to get
the actual value of this constant so that when you remove the reference to
ADO, your project will work. If you did not replace the named constant with
its value, you would get a syntax error (if you use Option Explicit) or
unexpected results (as the constant will evaluate to zero when the library
reference is removed). The easiest way to get the value of a constant is to
print it to the Immediate Window:

?adStateOpen
1

Obviously, you'll want to do this while the reference is still set.

2) Replace all explicit object types with Object. And replace all Set x =
New y with a CreateObject call. So if you have:

Dim cnMain As ADODB.Connection
Dim cdEmp As ADODB.Command

Set cnMain = New ADODB.Connection
Dim cdEmp = New ADODB.Command

You would replace this with:

Dim cnMain As Object
Dim cdEmp As Object

Set cnMain = CreateObject("ADODB.Connection")
Set cdEmp = CreateObject("ADODB.Command")


That should do it. If you have any further questions or don't understand
something here, let us know.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have an Excel 2000 application that uses references to
Active X Data Objects 2.5 and Outlook 9 Object library. I
need to instill late binding because users have different
versions of MSOffice and this causes a problem with the
library reference files. I did post before on this and
received some feedback, websites, etc. but I am confused
especially on how to "late bind" the ActiveX Data Objects
2.5 library file. Please help if you can.

Thank you.

Cindy

.

 

[cindy]

Chip,

Yes, I was just going to write back and say I found it (I
re-read Jake's replay and he told me how to find the
numeric equivalents). Thanks!

One more question - do I need to late bind the reference
to MS Office 9.0 Object library to account for different
versions of Office? If yes, what would change in my code?

Thanks again

-----Original Message-----
Cindy,

You need to replace adOpenKeyset, adLockOptimistic, and
adCmdTable with their numeric equivalents, which are 1, 3, and 2,
respectively.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Hi Jake,

I switched my references over as you stated but I have a
compile error on the last line of this code, at
adOpenKeyset, adLockOptimistic, adCmdTable.

I included some of my code thinking it might help. Thanks
so much.


'Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cn, rs As Object

Application.Cursor = xlWait

' connect to the Access database
Set cn = CreateObject("ADODB.Connection")
'Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=T:\PDA\PDA Request.mdb;"
' open a recordset for PDATracking Table
'Set rs = New ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open "PDATracking", cn, adOpenKeyset,
adLockOptimistic, adCmdTable

-----Original Message-----
Hi Cindy,

Not sure what feedback you already got on this, but

I'll
give it a shot.

Typically, I develop all code using Early Binding (set

a
reference, Dim

objects explicitly, use named constants from

libraries).
Once everything is

working, I modify my project to use Late Binding. To

do
this, you need to:

1) Get values of all named constants from libraries (in your case, ADO and
Outlook). For example, if your code has adStateOpen in it, you need to get
the actual value of this constant so that when you

remove
the reference to

ADO, your project will work. If you did not replace

the
named constant with

its value, you would get a syntax error (if you use Option Explicit) or
unexpected results (as the constant will evaluate to

zero
when the library

reference is removed). The easiest way to get the

value
of a constant is to

print it to the Immediate Window:

?adStateOpen
1

Obviously, you'll want to do this while the reference is still set.

2) Replace all explicit object types with Object. And replace all Set x =
New y with a CreateObject call. So if you have:

Dim cnMain As ADODB.Connection
Dim cdEmp As ADODB.Command

Set cnMain = New ADODB.Connection
Dim cdEmp = New ADODB.Command

You would replace this with:

Dim cnMain As Object
Dim cdEmp As Object

Set cnMain = CreateObject("ADODB.Connection")
Set cdEmp = CreateObject("ADODB.Command")


That should do it. If you have any further questions

or
don't understand

something here, let us know.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Cindy wrote:
I have an Excel 2000 application that uses references to
Active X Data Objects 2.5 and Outlook 9 Object library. I
need to instill late binding because users have different
versions of MSOffice and this causes a problem with the
library reference files. I did post before on this and
received some feedback, websites, etc. but I am confused
especially on how to "late bind" the ActiveX Data Objects
2.5 library file. Please help if you can.

Thank you.

Cindy

.

.

 

[Jake Marx]

Hi Cindy,

I switched my references over as you stated but I have a
compile error on the last line of this code, at
adOpenKeyset, adLockOptimistic, adCmdTable.

'Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cn, rs As Object

Just a comment here. When you are declaring variables (whether they be
"normal" variables or object variables), you should always explicitly
declare them. In this case, cn is being declared as a Variant because you
didn't use "As Object" for it. Only rs gets declared as an Object. In this
case, it doesn't make much difference (as either Variant or Object will work
here), but doing this can cause unexpected results. That's why I typically
declare each variable on a separate line.

Other than that, your new code looks good and should work as it did when
early bound.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

Hi Cindy,

Yes, I was just going to write back and say I found it (I
re-read Jake's replay and he told me how to find the
numeric equivalents). Thanks!

One more question - do I need to late bind the reference
to MS Office 9.0 Object library to account for different
versions of Office? If yes, what would change in my code?

No - that reference is put in there automatically by Excel. It will change
as needed when users with other versions open the workbook.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[cindy]

Thanks Jake

It works! Also, thanks for the tip on declaring
variables. I thought when you separated by a comma on the
same line they would all be declared as that type.

Have a great day

-----Original Message-----
Hi Cindy,

I switched my references over as you stated but I have a
compile error on the last line of this code, at
adOpenKeyset, adLockOptimistic, adCmdTable.

Please reread #1 in my original reply. <g> Or use the values Chip provided.

'Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cn, rs As Object

Just a comment here. When you are declaring variables (whether they be
"normal" variables or object variables), you should always explicitly
declare them. In this case, cn is being declared as a Variant because you
didn't use "As Object" for it. Only rs gets declared as an Object. In this
case, it doesn't make much difference (as either Variant or Object will work
here), but doing this can cause unexpected results. That's why I typically
declare each variable on a separate line.

Other than that, your new code looks good and should work as it did when
early bound.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

.

 

[Jake Marx]

Hi Cindy,

Thanks Jake

You're welcome - glad to help out!

It works! Also, thanks for the tip on declaring
variables. I thought when you separated by a comma on the
same line they would all be declared as that type.

No problem. That's a very common misconception, BTW.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]