Hot Fox: [Access VBA Central] Searching for shadow in dark room

quarta-feira, 28 de outubro de 2009

[Access VBA Central] Searching for shadow in dark room

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