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