[AccessD] Recordset method Failed

Fri Sep 8 08:38:18 CDT 2017

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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com