[AccessD] Recordset method Failed

Tue Sep 12 02:17:33 CDT 2017

Thanks Stuart,

Yes, this is SQL Server BE and ADO.

The recordsets are not returning Nulls and Zero Length Strings - they are
now returning the correct records.  It is what is being stored in the
comboboxes when I select an item from a list, then delete the selection in
the box (by selecting the text and pressing the delete key).

On both machines ComboBox1.ListIndex returns -1 when the text in the
combobox has been deleted.

gADODBConnection.Version is 6.1 on the development machine and 6.3 on the
new server.  What could this signify?

Permissions are all the same.  The failing seems to be at the Access end.

The SQL ANSI_NULLS setting seems to relate to the returning of records.
Records are being returned and can be selected.  It is the different
behaviour when they are deleted that has me stumped.

David

-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] 
Sent: Tuesday, 12 September 2017 5:33 p.m.
To: David Emerson
Subject: Re: [AccessD] Recordset method Failed

A few thoughts off the top of my head.

This is with SQL Server BE and ADO, yes?

Apparently some recordsets are returning Nulls and some are returning Zero
Length Strings.  
They are not the same thing.

If the Combobox is returning an empty string, it is not cleared.  What does
ComboBox1.ListIndex return? It should be -1

What does gADODBConnection.Version return on the two installations?

I notice in your earlier post that you are actually  calling a stored
procedure, not a SQL SELECT statement.  Have you compared the succeeding and
failing procedures, including permissions on them and their underlying
tables?

You may need to look at the SQL ANSI_NULLS setting.

On 12 Sep 2017 at 16:36, David Emerson wrote:

> I have been doing more investigating and have come up with something 
> unusual.
> 
> The IT people have set up a separate box with SQL 2008R2 installed 
> cleanly and Access 2010 installed cleanly.  Some of the problems have 
> gone away with setting the record sets of forms and combo boxes.  I 
> have found that I am now getting an unusual thing happening when I use 
> some of the combo boxes for filters.
> 
> The combo boxes are basically 2 columns with the first column being an 
> ID and the second column being a text description.
> 
> When I select an item from the list the combo box has the value of the 
> ID for that item.
> 
> If I clear the combobox and use nz to get the value of the box (for 
> example nz(me!cboFilter,0)) I get 0 on my development machine but the 
> new server returns an empty string.  I know this because I had this on 
> the After Update event:
> 
> MsgBox "(" & Nz(Me!cboFilter, 0) & ")"
> If Nz(Me!cboFilter, 0) = "" Then MsgBox "Empty string"
> 
> On My machine I get:
> (0)
> 
> On the new server I get
> ()
> Empty String
> 
> Why the different behaviour?  Is this a setting, or something with NZ 
> I am not aware of?
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
> 
> 
> 
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of David Emerson Sent: Saturday, 9 September 2017 1:21 p.m. To:
> 'Access Developers discussion and problem solving' Subject: Re:
> [AccessD] Recordset method Failed
> 
> Thanks for the suggestions Jim.  
> 
> Config Issue - Do you mean the configuration I use to make a 
> connection to the SQL Server?  If so it is identical to the working 
> version except I have changed the server name.
> 
> Protocols used to communicate with SQL server - Can you please provide 
> more information?  Where would I look for these?  Is it an SQL Server 
> setting, Windows setting, other?
> 
> I think the commands are ok.  I never use spaces in names without 
> using Square brackets, and all the SQL commands are working on the old 
> server. The SQL Database is a direct restore from a backup of the old 
> server.
> 
> ADO Provider:  This is what I am using:
> 
> gADODBConnection.ConnectionString = "Provider=sqloledb;Data 
> Source=NZWDCPRDSQL01\NZPROD01;Initial Catalog=PRISM_PRD;Integrated 
> Security=SSPI;"
> 
> The main difference I note is that the old SQL Server is 2008, the new 
> one is 2012.  Could there be differences between the versions that I 
> am not aware of?
> 
> Thanks for your time.
> 
> David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Jim Dettman Sent: Saturday, 9 September 2017 1:38 a.m. To: 'Access 
> Developers discussion and problem solving' Subject: Re: [AccessD] 
> Recordset method Failed
> 
> David,
> 
>  Sounds like either a communications problem with the new server or a  
> config issue.
> 
>  I would verify the protocols used to communicate with SQL server (vs  
> TCPIP vs named pipes) are the same for the old server and new server, 
> and match the order that the clients have.
> 
> Then I would look at the SQL commands themselves.   You can bump into
> problems where fields and/or table names are not delimited with square 
> brackets ( [] ), especially when Extended ANSI is at work (the
> reserved word list expands).   Look at one of the SQL statements that
> fails and delimit everything.   See if that fixes it.
> 
>   Might also be a problem with the ADO provider.
> 
> Jim.
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of David Emerson Sent: Thursday, September 07, 2017 06:19 AM To:
> 'Access Developers discussion and problem solving' Subject: Re:
> [AccessD] Recordset method Failed
> 
> Update - Here is a summary of what we know:
> 
>  Run-time error '-2147024770 (8007007e)'
>  Method 'Recordset' of object '_Combobox' failed. 
> 
> The error only seems to effect setting the recordset method of forms 
> and combo boxes (I am able to get data from the recordsets retrieved 
> from SQL, but not able to assign the recordset to the Access objects).
> 
> 
> We have tried using a completely fresh copy of Access prog which still 
> shows the same error.  Decompile, Repair and Compact have not helped.
> 
> All references have been checked.
> 
> The system was originally being tested in Access 2010 (it is currently 
> running successfully in Access 2010 on a different server).  Upgrading 
> Access to 2013 has partially solved the problem - some combo boxes and 
> forms are having their recordsets updated but not all of them when 
> there are a large number on a form, or the form has a large number of 
> subforms. Creating a new database in Access 2013 and importing all the 
> objects has not changed anything.
> 
> The programme was originally working and then suddenly stopped working 
> without anything being changed to the Access PRISM file.
> 
> The SQL database file had permissions added to it and the error 
> started to show after that.  However we hadn't tested the programme 
> immediately prior to the change in permissions so cannot be sure that 
> the problem is in SQL.
> 
> The SQL database file has subsequently been restored from the original 
> backup but the error persists.  Tests were done before the permissions 
> were applied to the new copy with the same error showing.
> 
> The SQL database is being run on SQL 2012.  The current version is 
> being run on 2008.
> 
> Anything anyone?
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
> 
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Paul Hartland via AccessD Sent: Thursday, 7 September 2017 2:21 
> p.m. To: Access List Cc: Paul Hartland Subject: Re: [AccessD] 
> Recordset method Failed
> 
> If your not putting a compiled version on the new server I assume that 
> when it fails you have gone into tools/references anx checked for any 
> missing references
> 
> On 6 Sep 2017 23:21, "David Emerson" <newsgrps at dalyn.co.nz> wrote:
> 
> > Hi Listers,
> >
> >
> >
> > I have an accdb which uses ADO to get a recordset from an SQL Server 
> > database
> >
> >
> >
> > I am getting the following error:
> >
> >
> >
> > Run-time error '-2147024770 (8007007e)'
> >
> > Method 'Recordset' of object '_Combobox' failed.
> >
> >
> >
> > The code is:
> >
> >
> >
> >     Set rstTemp = basRunDataObject("dbo.spfrmAAMenu", adCmdText)  
> >     <---
> > This
> > function code is given at the end of this message
> >
> >
> >
> > ' If Not rstTemp.EOF Then
> >
> > '     rstTemp.MoveFirst
> >
> > '     Do Until rstTemp.EOF
> >
> > '         MsgBox rstTemp!LGARegionID
> >
> > '         rstTemp.MoveNext
> >
> > '     Loop
> >
> > 'End If
> >
> >
> >
> >     Set Me!cboFilterLGARegionIDNo.Recordset = rstTemp    <--- Error
> >     on
> > this
> > line
> >
> >
> >
> >
> >
> > When I unrem the code block then the msgbox shows the values of the 
> > LGARegionID which indicates that the recordset is being returned.
> >
> >
> >
> > Here is the rub.  The programme is working fine on my machine and on 
> > the clients current machine.  It is only on their new server that 
> > they are wanting to migrate to that the problem happens.
> >
> >
> >
> > Also, it is happening for all similar calls to set combobox 
> > recordsets and form recordsets throughout the whole programme.
> >
> > Also, it was working a few days ago, but when they tried yesterday 
> > it stopped working.
> >
> >
> >
> > The only area I can think of is some permissions at the SQL Server 
> > database end.  The IT department was playing with security settings.
> > Could one of these caused the problem?
> >
> >
> >
> >
> >
> > Public Function basRunDataObject(strCommandText As String, 
> > commandType As ADODB.CommandTypeEnum) As ADODB.Recordset
> >
> >
> >
> >     On Error GoTo Err_basRunDataObject
> >
> >
> >
> >     Dim adocmd As ADODB.Command
> >
> >     Dim rst As ADODB.Recordset
> >
> >     Set rst = New ADODB.Recordset
> >
> >
> >
> >     rst.LockType = adLockOptimistic
> >
> >     rst.CursorType = adOpenKeyset
> >
> >     rst.CursorLocation = adUseClient
> >
> >
> >
> >     If gADODBConnection.State = adStateClosed Then
> >
> >         Call basOpenGlobalConnection
> >
> >     End If
> >
> >
> >
> >     Set adocmd = New ADODB.Command
> >
> >     With adocmd
> >
> >         .ActiveConnection = gADODBConnection
> >
> >         .CommandText = strCommandText
> >
> >         .commandType = commandType
> >
> >         .CommandTimeout = 0
> >
> >     End With
> >
> >
> >
> >     rst.Open adocmd
> >
> >
> >
> >     Set basRunDataObject = rst
> >
> >     Set adocmd = Nothing
> >
> >
> >
> > Exit_basRunDataObject:
> >
> >     Exit Function
> >
> >
> >
> > Err_basRunDataObject:
> >
> >     Select Case Err
> >
> >         Case 0:
> >
> >         Case Else
> >
> >             Call basErrorMsg("basRunDataObject - " & strCommandText)
> >
> >     End Select
> >
> >     Resume Exit_basRunDataObject
> >
> >     Exit Function
> >
> >
> >
> > End Function
> >
> >
> >
> >
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 