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

(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

For Outlook, refer t

http://msdn.microsoft.com/library/de...nvironment.asp

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'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)

"Jake Marx" wrote in 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

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

.

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


"Cindy" wrote in message
...
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

.

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


"Cindy" wrote in message
...
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

.



.

Hi Cindy,

Cindy wrote:
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]

Hi Cindy,

cindy wrote:
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!

You can ignore my second post, then.... <g

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]

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,

Cindy wrote:
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]

.

Hi Cindy,

cindy wrote:
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]