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
