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

sexta-feira, 30 de outubro de 2009

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

Jeff are you getting any data back in the Recordset or are you getting the "There are no records....." message?

If you are getting data can you test if you are getting what you expect within the calling program where you want to use it? Or in other words what data IS in that field if anything?

Note both of these test suggestions are tests conducted from within the recipient that you want to use the data within.

Are you using a declared ODBC global PC name for the database (forget the exact term for it)?

--- On Wed, 10/28/09, pdcaregiver <jpjones23@earthlink.net> wrote:

From: pdcaregiver <jpjones23@earthlink.net>
Subject: [Access VBA Central] Searching for shadow in dark room
To: AccessVBACentral@yahoogroups.com
Date: Wednesday, October 28, 2009, 7:57 PM

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").CopyFromRe cordset 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

[Non-text portions of this message have been removed]