Thread: adCmdText

A program importing data from Access to Excel works fine on my machine. But
on others it says, "Compile error, can't find the project or library." It
points to "adCmdText" in my codes. How to fix it?

Is there a missing reference to an ADO library?

--
Tim Williams
Palo Alto, CA


"Scott" <Scott@discussions.microsoft.com> wrote in message news:93CADBE5-9423-4EA0-AD78-CF1E48250131@microsoft.com...
> A program importing data from Access to Excel works fine on my machine. But
> on others it says, "Compile error, can't find the project or library." It
> points to "adCmdText" in my codes. How to fix it?

As pointed oout, adCmdText is in the ADO library. It is a constant that
Access can resolve ONLY if you are using 'early' binding; with 'late'
binding, you need to specify the actual value of the constant.

Thank you for your insights, but I need more help on this. Here are my codes:

Dim DBFullName As String, TableName As String, StateName As String,
TargetRange As Range
Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Integer

DBFullName = My_File.Value
TableName = "ProjectData"
StateName = Worksheets("MyData").Range("AE1")

Set TargetRange = Sheet1.Cells(1, 1)

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName &
";"

Set rs = New ADODB.Recordset
With rs
.Open "SELECT * FROM " & TableName & " WHERE [STATE] = '" &
StateName & "'", cn, , , adCmdText

For i = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, i).Value = rs.Fields(i).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs

End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing



"AA2e72E" wrote:

> As pointed oout, adCmdText is in the ADO library. It is a constant that
> Access can resolve ONLY if you are using 'early' binding; with 'late'
> binding, you need to specify the actual value of the constant.
>
>

AA2e72E,

Please excuse my ignorance, but how to specify the "early" binding in the
code? I appreciate your help.

"AA2e72E" wrote:

> As pointed oout, adCmdText is in the ADO library. It is a constant that
> Access can resolve ONLY if you are using 'early' binding; with 'late'
> binding, you need to specify the actual value of the constant.
>
>

In the VBE go to Tools>>References and check the box next to "ActiveX Data Objects" (whatever version is on your PC)

You would then declare objects as specific types

Eg:

Dim oConn as adodb.connection

instead of

Dim oConn as object



Tim.

"Scott" <Scott@discussions.microsoft.com> wrote in message news:7694BA6E-004C-4650-B9A0-28955A863AFC@microsoft.com...
> AA2e72E,
>
> Please excuse my ignorance, but how to specify the "early" binding in the
> code? I appreciate your help.
>
> "AA2e72E" wrote:
>
>> As pointed oout, adCmdText is in the ADO library. It is a constant that
>> Access can resolve ONLY if you are using 'early' binding; with 'late'
>> binding, you need to specify the actual value of the constant.
>>
>>

Thank you, Tim.

Actually, that's exactly what I did. I have "Microsoft Active Data Objects
2.8 Library" checked. And I also declared "Dim cn As ADODB.Connection" in
the code (Please see my previous email for all codes.). My current problem is
that the program works fine on my machine, but the exact same program does
not work on others' machines (all with Office 2X). I am looking for codes
that I can use so that the program works on every machine with Office 2X.

Thank you.


"Tim Williams" wrote:

> In the VBE go to Tools>>References and check the box next to "ActiveX Data Objects" (whatever version is on your PC)
>
> You would then declare objects as specific types
>
> Eg:
>
> Dim oConn as adodb.connection
>
> instead of
>
> Dim oConn as object
>
>
>
> Tim.
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message news:7694BA6E-004C-4650-B9A0-28955A863AFC@microsoft.com...
> > AA2e72E,
> >
> > Please excuse my ignorance, but how to specify the "early" binding in the
> > code? I appreciate your help.
> >
> > "AA2e72E" wrote:
> >
> >> As pointed oout, adCmdText is in the ADO library. It is a constant that
> >> Access can resolve ONLY if you are using 'early' binding; with 'late'
> >> binding, you need to specify the actual value of the constant.
> >>
> >>
>
>
>

Scott,

I don't see any code in this thread: maybe the message is no longer on the server? Also I'm not clear if your developement machine
is Office2K?

You could try substituting the actual value of adCmdText (you can find this via the Object browser: F2 in the VBE and check under
the ADO library)

--
Tim Williams
Palo Alto, CA


"Scott" <Scott@discussions.microsoft.com> wrote in message news:29030635-A5AC-49B5-A6DE-063AD1398607@microsoft.com...
> Thank you, Tim.
>
> Actually, that's exactly what I did. I have "Microsoft Active Data Objects
> 2.8 Library" checked. And I also declared "Dim cn As ADODB.Connection" in
> the code (Please see my previous email for all codes.). My current problem is
> that the program works fine on my machine, but the exact same program does
> not work on others' machines (all with Office 2X). I am looking for codes
> that I can use so that the program works on every machine with Office 2X.
>
> Thank you.
>
>
> "Tim Williams" wrote:
>
> > In the VBE go to Tools>>References and check the box next to "ActiveX Data Objects" (whatever version is on your PC)
> >
> > You would then declare objects as specific types
> >
> > Eg:
> >
> > Dim oConn as adodb.connection
> >
> > instead of
> >
> > Dim oConn as object
> >
> >
> >
> > Tim.
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message news:7694BA6E-004C-4650-B9A0-28955A863AFC@microsoft.com...
> > > AA2e72E,
> > >
> > > Please excuse my ignorance, but how to specify the "early" binding in the
> > > code? I appreciate your help.
> > >
> > > "AA2e72E" wrote:
> > >
> > >> As pointed oout, adCmdText is in the ADO library. It is a constant that
> > >> Access can resolve ONLY if you are using 'early' binding; with 'late'
> > >> binding, you need to specify the actual value of the constant.
> > >>
> > >>
> >
> >
> >

Thank you.

One friend looked at my codes, which did not work on his machine either. But
somehow, he checked all Microsoft web components under the Reference of Tool
and suddendly it worked on his machine. I tested on a couple of other
machines and they all worked. We don't know why. But it works.

I appreciate your attention and efforts.

"Tim Williams" wrote:

> Scott,
>
> I don't see any code in this thread: maybe the message is no longer on the server? Also I'm not clear if your developement machine
> is Office2K?
>
> You could try substituting the actual value of adCmdText (you can find this via the Object browser: F2 in the VBE and check under
> the ADO library)
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message news:29030635-A5AC-49B5-A6DE-063AD1398607@microsoft.com...
> > Thank you, Tim.
> >
> > Actually, that's exactly what I did. I have "Microsoft Active Data Objects
> > 2.8 Library" checked. And I also declared "Dim cn As ADODB.Connection" in
> > the code (Please see my previous email for all codes.). My current problem is
> > that the program works fine on my machine, but the exact same program does
> > not work on others' machines (all with Office 2X). I am looking for codes
> > that I can use so that the program works on every machine with Office 2X.
> >
> > Thank you.
> >
> >
> > "Tim Williams" wrote:
> >
> > > In the VBE go to Tools>>References and check the box next to "ActiveX Data Objects" (whatever version is on your PC)
> > >
> > > You would then declare objects as specific types
> > >
> > > Eg:
> > >
> > > Dim oConn as adodb.connection
> > >
> > > instead of
> > >
> > > Dim oConn as object
> > >
> > >
> > >
> > > Tim.
> > >
> > > "Scott" <Scott@discussions.microsoft.com> wrote in message news:7694BA6E-004C-4650-B9A0-28955A863AFC@microsoft.com...
> > > > AA2e72E,
> > > >
> > > > Please excuse my ignorance, but how to specify the "early" binding in the
> > > > code? I appreciate your help.
> > > >
> > > > "AA2e72E" wrote:
> > > >
> > > >> As pointed oout, adCmdText is in the ADO library. It is a constant that
> > > >> Access can resolve ONLY if you are using 'early' binding; with 'late'
> > > >> binding, you need to specify the actual value of the constant.
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>