I've created a tool with an Excel front end and an Access back end.
The objective is to store data for analysis for folks working in
multiple locations. The tool works correctly on all 5 computers here
but at one remote location it doesn't.

The platforms here use Excel 97, Excel 2003 SP3 and Excel 2007. The
operating systems are XP Pro SP3, and XP ME SP2.

The remote location uses XP Pro SP3 and Excel 2003 SP3.

I've compared references and all are the same although I've not yet
delved down to see if there are different versions of libraries.

The line of code in the remote location that throws up the error is
very simply storing a table field in a variable. A snippet of code
follows.

Set rs = New ADODB.Recordset
With rs
.Open strSQLQuery, cn, , , adCmdText
If Not rs.EOF Then
strTblKey = rs.Fields("TableKey") <- error
Else
MsgBox "There are no records....."
End If
End With

The database table row follows:

ParentKey TableKey
Base_P_Key B9991110

The SQL statement is valid. I tested it directly in Access. i want
the TableKey field data (B9991110) to be placed into the variable.

The error message speaks to the item not being in the collection. Err
number 3265, "Item cannot be found in collection corresponding to the
requested name or ordinal."

I tried an alternative to test the query and connection in the code by
executing the query and then copying the record set to a worksheet. It
worked correctly on the remote computer. Here's it's copy statement:

Set xlSht = Sheets(ActiveSheet.Name)
xlSht.Range("A2").CopyFromRecordset rs

So, after all this wonderfulness, I come to my specific question. Have
any of you seen this sort of circumstance and, if so, have you any
suggestions for correcting the error. I really would rather figure
out a solution than to just use the CopyFromRecordset method. The code
ought to just work.

Hoping.....

Jeff

you could try

for cl = 1 to rs.Fields.Count
Cells(1,cl)=rs.Fields(cl-1).Name ' fields are zero based
Next

to see what field names get returned

in code, if you "know" its always going to be #2 then
you could just do this
strTblKey = rs.Fields(1)



"JP Jones" wrote:


I've created a tool with an Excel front end and an Access back end.
The objective is to store data for analysis for folks working in
multiple locations. The tool works correctly on all 5 computers here
but at one remote location it doesn't.

The platforms here use Excel 97, Excel 2003 SP3 and Excel 2007. The
operating systems are XP Pro SP3, and XP ME SP2.

The remote location uses XP Pro SP3 and Excel 2003 SP3.

I've compared references and all are the same although I've not yet
delved down to see if there are different versions of libraries.

The line of code in the remote location that throws up the error is
very simply storing a table field in a variable. A snippet of code
follows.

Set rs = New ADODB.Recordset
With rs
.Open strSQLQuery, cn, , , adCmdText
If Not rs.EOF Then
strTblKey = rs.Fields("TableKey") <- error
Else
MsgBox "There are no records....."
End If
End With

The database table row follows:

ParentKey TableKey
Base_P_Key B9991110

The SQL statement is valid. I tested it directly in Access. i want
the TableKey field data (B9991110) to be placed into the variable.

The error message speaks to the item not being in the collection. Err
number 3265, "Item cannot be found in collection corresponding to the
requested name or ordinal."

I tried an alternative to test the query and connection in the code by
executing the query and then copying the record set to a worksheet. It
worked correctly on the remote computer. Here's it's copy statement:

Set xlSht = Sheets(ActiveSheet.Name)
xlSht.Range("A2").CopyFromRecordset rs

So, after all this wonderfulness, I come to my specific question. Have
any of you seen this sort of circumstance and, if so, have you any
suggestions for correcting the error. I really would rather figure
out a solution than to just use the CopyFromRecordset method. The code
ought to just work.

Hoping.....

Jeff
.

Thank you Patrick. I'll try these.

On Thu, 29 Oct 2009 08:59:02 -0700, Patrick Molloy
wrote:

you could try

for cl = 1 to rs.Fields.Count
Cells(1,cl)=rs.Fields(cl-1).Name ' fields are zero based
Next

to see what field names get returned

in code, if you "know" its always going to be #2 then
you could just do this
strTblKey = rs.Fields(1)



"JP Jones" wrote:


I've created a tool with an Excel front end and an Access back end.
The objective is to store data for analysis for folks working in
multiple locations. The tool works correctly on all 5 computers here
but at one remote location it doesn't.

The platforms here use Excel 97, Excel 2003 SP3 and Excel 2007. The
operating systems are XP Pro SP3, and XP ME SP2.

The remote location uses XP Pro SP3 and Excel 2003 SP3.

I've compared references and all are the same although I've not yet
delved down to see if there are different versions of libraries.

The line of code in the remote location that throws up the error is
very simply storing a table field in a variable. A snippet of code
follows.

Set rs = New ADODB.Recordset
With rs
.Open strSQLQuery, cn, , , adCmdText
If Not rs.EOF Then
strTblKey = rs.Fields("TableKey") <- error
Else
MsgBox "There are no records....."
End If
End With

The database table row follows:

ParentKey TableKey
Base_P_Key B9991110

The SQL statement is valid. I tested it directly in Access. i want
the TableKey field data (B9991110) to be placed into the variable.

The error message speaks to the item not being in the collection. Err
number 3265, "Item cannot be found in collection corresponding to the
requested name or ordinal."

I tried an alternative to test the query and connection in the code by
executing the query and then copying the record set to a worksheet. It
worked correctly on the remote computer. Here's it's copy statement:

Set xlSht = Sheets(ActiveSheet.Name)
xlSht.Range("A2").CopyFromRecordset rs

So, after all this wonderfulness, I come to my specific question. Have
any of you seen this sort of circumstance and, if so, have you any
suggestions for correcting the error. I really would rather figure
out a solution than to just use the CopyFromRecordset method. The code
ought to just work.

Hoping.....

Jeff
.