Solved

ADO recordset problem using access 2000

Posted on 2001-08-20
28
380 Views
Last Modified: 2013-11-25
I am using an SQL Server 2000 backend and an Access 2000 frontend to a database I am (kind of!) making.

My problem is that for one of my forms I want to display a certain record (with a unique ID and CustomerID) and on a subform display in datasheet view all records with the same CustomerID but not the one in the main form. (don't worry I will get to some VB!)

The way I am doing this is putting a Stored Procedure on SQL Server that is passed the Unique ID of the record and it's CustomerID and returns the recordset I want. The SQL procedure works fine.. it returns the records related to the CustomerID but not the record that has the selected Unique ID.

My problem occurs when I use ADO to call that procedure and it returns the recordset. I then want to bind that recordset to the subform, I am using the code me.recordset = objRS <- the returned recordset, inside the subforms On_Load sub. I thought that this would work but I get the error message "The object you entered is not a valid recordset property" for the above line. From help files I found that this error is about trying to use a disconnected ADO recordset in this way but my recordset definitly has an active connection.

I am running out of ideas of why this would be (the only thing I can think of is that it has something to do with the subform) but I would like some advise as to whether I could do this with ADO recordsets etc..

If anyone wants I will put up my code but atm it is in a real mess (I've been playing with it all morning) so I will see if there are any responses first.

Thanks in advance

Ewen.
0
Comment
Question by:ph0bia
  • 12
  • 5
  • 4
  • +4
28 Comments
 
LVL 1

Expert Comment

by:kdg2000
ID: 6405862
Try instead of string(line):

me.recordset = objRS < - the returned recordset

To put such string(line):

Set me.recordset = objRS < - the returned recordset
0
 

Author Comment

by:ph0bia
ID: 6405876
Oh I'm sorry, typo there.

I did put Set me.recordset = objRS

If I don't put the set it comes up with some other error (can't remember what off of the top of my head)
0
 
LVL 1

Expert Comment

by:kdg2000
ID: 6405930
Send this part of the code (On_Load sub). Then the error is possible for certain to tell, where.
0
 

Author Comment

by:ph0bia
ID: 6405959
This is all the VB code that is used:-


Public objConn As New ADODB.connection
Public objCommand As New ADODB.Command
Public objRS As ADODB.Recordset
 

Public Sub ServerConnection(connection As String)

Set objRS = Nothing
Set objCommand = Nothing
Set objConn = Nothing
With objConn
    .ConnectionString = "Data Source=ewen"
    .Open
   
End With
With objCommand
    .CommandType = adCmdStoredProc
    .CommandText = connection
    .ActiveConnection = objConn
End With
End Sub

Private Sub Form_Load()

    Dim prm As ADODB.Parameter
    Call ServerConnection("SelectFromPastHistory")
   
    Set prm = objCommand.CreateParameter("log", adVarChar, adParamInput, 5, Forms.ProblemHistory.LogNo.Value)
    objCommand.Parameters.Append prm
    Set prm = objCommand.CreateParameter("ID", adInteger, adParamInput, , Forms.ProblemHistory.[CustomerID])
    objCommand.Parameters.Append prm
   
    Set objRS = objCommand.Execute
     
    Set Me.Recordset = objRS
    Me.requery
   
    Set objRS = Nothing
    Set objCommand = Nothing
    objConn.Close
    Set objConn = Nothing

End Sub
0
 
LVL 1

Expert Comment

by:kdg2000
ID: 6406035
Instead of:

Set objRS = objCommand. Execute

Set Me. Recordset = objRS
Me.requery

Try:

Set Me. Recordset = objCommand. Execute
Me.requery
0
 

Author Comment

by:ph0bia
ID: 6406069
that still didn't work. It still comes up with the same error.

I wouldn't be surprised if it is something really simple that I am missing.

Just one thing, I have not set Master or Child link fields for the subform because it is unbound at design time. Just incase anyone was thinking they wanted that piece of info.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6406347
From MSDN:

Q230737 - ACC2000: Cannot Set Form Recordset to Disconnected Recordset
http://support.microsoft.com/support/kb/articles/Q230/7/37.ASP

-----------------------------------------------------------The text of which reads:

ACC2000: Cannot Set Form Recordset to Disconnected Recordset

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS
When you try to assign an ActiveX Data Objects (ADO) Recordset object to a form, you may receive the following error message:

The object you entered is not a valid Recordset property.

CAUSE
You assigned a disconnected ADO Recordset object to the Recordset property of the form.

RESOLUTION
Set the ActiveConnection property of the ADO recordset to a valid ADO Connection object before assigning it to the form.

MORE INFORMATION
ADO allows you to create a recordset, and then to disconnect it from its source. In a client/server environment, this allows you to update the recordset on the client without maintaining a constant connection to the server. Later, your changes are batch updated to the server. Even though ADO allows you to disconnect a recordset from its source and manipulate it on the client without a connection, Microsoft Access forms bound to Recordset objects require a valid ADO Connection object.

Steps to Reproduce Behavior
CAUTION: Following the steps in this example will modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and perform these steps on a copy of the project.

Open the sample Access project NorthwindCS.adp.

Open the Customers form in Design view.

Clear the RecordSource property of the form so that the form is no longer bound.

On the View menu, click Code.

Add the following event procedure to the module of the form:

Private Sub Form_Open(Cancel As Integer)
    Dim rs As ADODB.Recordset
       
    'Create a new ADO recordset, and disconnect it
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Source = "SELECT * FROM Customers"
        .Open
        .ActiveConnection = Nothing
    End With
    Set Me.Recordset = rs
End Sub

Switch from the Visual Basic Editor to Microsoft Access.

Save the Customers form and close it.

Open the Customers form in Form view from the Database window. Note that you receive the following error when setting the form Recordset property to the disconnected recordset:

The object you entered is not a valid Recordset property.

REFERENCES
For additional information about using disconnected recordsets with ADO, please see the following articles in the Microsoft Knowledge Base:

Q190717 Disconnected Recordsets with ADO or RDS
Q184397 Getting ADO Disconnected Recordsets in VBA/C++/Java
For more information about using the form Recordset property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Form Recordset Property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: prb cannot can t

Keywords : kberrmsg kbdta KbClientServer AccessCS
Issue type : kbprb
Technology : kbAccessSearch kbAccess2000 kbAccess2000Search
0
 

Author Comment

by:ph0bia
ID: 6406402
wsh2 - I have already read that article, when it said that it was a disconnected issue I put this code just above the error.

MsgBox objRS.ActiveConnection

it came up with the connection details that I was expecting. it was using the correct data source name and the correct server etc. etc. so I was definitly connected. (I think!!)

(Also one thing to note about that article in the part where it says about replicating the problem it sets the activeconnection to nothing after the recordset has been opened, in access 2000 you can't do that, well, you can't on mine! so I am not really amazed with the help in that article, thats why I am asking the experts!!)
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6406488
First.. the test you should be using is:

   If objConn.State = adStateOpen Then
      MsgBox "The Connection Is Open"
   Else
      MsgBox "The Connection Is CLOSED (ie. Disconnected)"
   End If

<phone>.. BRB
0
 

Expert Comment

by:rolarsso
ID: 6406521
I think the New keyword is missing somwhere
If i remember correct it is something like this:

if you use New in the declaration, like:
Public objConn As New ADODB.connection
Public objCommand As New ADODB.Command
every time you use the object vb checks if it exists, and creates if needed

If you don't, like:
Public objRS As ADODB.Recordset
vb does not check if it exists, you have to type somwhere:
Set objRS = New ADODB.Recordset
before using it.
or you can declare with New, like the connection, but is some milliseconds slower...

/Roger
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 20 total points
ID: 6406522
Back.. <smile>

Ok.. try changing your code to this

<----- Code Begin ----->

Private Sub Form_Load()

   Call ServerConnection("SelectFromPastHistory")
   
   Dim prm As ADODB.Parameter
   Set prm = objCommand.CreateParameter("log", adVarChar, adParamInput, 5, Forms.ProblemHistory.LogNo.Value)
   objCommand.Parameters.Append prm
   Set prm = objCommand.CreateParameter("ID", adInteger, adParamInput, , Forms.ProblemHistory.[CustomerID])
   objCommand.Parameters.Append prm
   
   Set Me.Recordset = objCommand.Execute
   
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

   Set objRS = Nothing
   Set objCommand = Nothing
   objConn.Close
   Set objConn = Nothing

End Sub

<----- Code End ----->
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6406528
If you don't have the:

   Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

Event handler.. use the:

  Private Sub Form_Unload(Cancel As Integer)

Event handler instead.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6406563
rolarsso:
If you take a second look at the code, you will see that ph0bia has instantiated all of his ADO objects..

Public objConn As New ADODB.connection
Public objCommand As New ADODB.Command
Public objRS As ADODB.Recordset
 
And the objCommand.CreateParameter..

   Dim prm As ADODB.Parameter
   Set prm = objCommand.CreateParameter("log", adVarChar, adParamInput, 5, Forms.ProblemHistory.LogNo.Value)

And the objCommand.Execute
 
   Set objRS = objCommand.Execute

achieve the same purpose as the New keyword would.

If indeed the objects were NOT instantiated (ie. no NEW or comparable function).. then a definite Error message would occur the very first time you would go and try to use the object.  





   
0
 

Author Comment

by:ph0bia
ID: 6406593
firstly thanks a lot wsh2, I used the connection thingy and I was still right, it said connection open :-)
(It's near the end of my working day and I am tired so excuse me if I am being a bit smug!)

Secondly Roger, thanks for pointing out that there wasn't a NEW for the recordset in the global declaration (sp?) I have changed this but it doesn't seem to have had any effect at all.. v. strange!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:ph0bia
ID: 6406598
wsh2 - don't I need a new for the recordset then?

ie Public objRS As New ADODB.Recordset

I'm confused !!!
0
 

Author Comment

by:ph0bia
ID: 6406647
I've just tried moving the closing statements into an unload statement (You were right wsh2 I don't have a QueryUnload) but it still gives the error "The object you entered is not a valid recordset property"
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 6457645
Try this

1) move all your code to the main form "ProblemHistory"
2) rename subform control to "fsubProbHist"

3) rename the Form_Onload routine to Form_current

4) change the code references thus

Private Sub Form_current

Dim prm As ADODB.Parameter
   Call ServerConnection("SelectFromPastHistory")
   
   Set prm = objCommand.CreateParameter("log", adVarChar, adParamInput, 5, Me!LogNo.Value)
   objCommand.Parameters.Append prm
   Set prm = objCommand.CreateParameter("ID", adInteger, adParamInput, , Me![CustomerID])
   objCommand.Parameters.Append prm
   
   Set objRS = objCommand.Execute
   
   Set Me.fsubProbHist.form.Recordset = objRS
'   Me.requery
   
   Set objRS = Nothing
   Set objCommand = Nothing
   objConn.Close
   Set objConn = Nothing
end sub

add some error handling!!

Richard
0
 

Author Comment

by:ph0bia
ID: 6457713
I tried it thanks Richard but I get the same error on the line
Set Me.fsubProbHist.form.Recordset = objRS

I think it has something to do with the fact that I am calling a stored procedure and it returns a forward only cursortype.
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 6457945
can you post the Stored proc SQL?
Richard
0
 
LVL 1

Expert Comment

by:eeevans
ID: 6458540
ph0bia, No, you don't need the "Public objRS As New ADODB.Recordset" with the New as the objCommand.Execute will pass back a reference to a ADODB.Rectordset object.  I would however try changing your code to the following as I have seen Dim'ing As New cause problems before.

Public objConn As ADODB.connection
Public objCommand As ADODB.Command
Public objRS As ADODB.Recordset
 
Private Sub Form_Load()
Dim prm As ADODB.Parameter
   Set objConn = New ADODB.connection
   Set objCommand = New ADODB.Command

   Call ServerConnection("SelectFromPastHistory")
   
   Set prm = objCommand.CreateParameter("log", adVarChar, adParamInput, 5, Forms.ProblemHistory.LogNo.Value)
   objCommand.Parameters.Append prm
   Set prm = objCommand.CreateParameter("ID", adInteger, adParamInput, , Forms.ProblemHistory.[CustomerID])
   objCommand.Parameters.Append prm
   
   Set objRS = objCommand.Execute
   
   Set Me.Recordset = objRS
   Me.requery
   
   Set objRS = Nothing
   Set objCommand = Nothing
   objConn.Close
   Set objConn = Nothing

End Sub
Regards
0
 

Author Comment

by:ph0bia
ID: 6460010
thanks for the suggestion eeevans but it still didn't help.
Here is the procedure SQL Richard. It's very simple. Using ?objRS.GetString in the immediate window it shows that there is a recordset returned by the procedure I just can't set that as the "active" recordset for the subform.


CREATE PROCEDURE SelectFromPastHistory  @log varchar(5), @ID int

AS

SELECT *
FROM PastHistory
WHERE LogNo <> @log
AND CustomerID = @ID

Ph0bia
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 6463372
I know I am grasping at straws.... but

try changing your Sp to

Select
<<Fieldname1>>,<<Fieldname2>>
from
FROM PastHistory
WHERE LogNo <> @log
AND CustomerID = @ID

instead of Select *

Richard
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 6463382
Also Add

SET NOCOUNT ON

as first line of procedure (after AS)

Richard
0
 

Author Comment

by:ph0bia
ID: 6487091
Thanks a lot for the help everyone but in the end none of the suggestions worked. I'm sure it was just a simple mistake on my part but I didn't have the time to keep working on it so I used a work around using views which I think will be much slower when the database becomes bigger, but what else could I do?!
Thanks again, it's just a shame we never found a solution.
Ewen.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6810330
I have refunded 80 points to you to facilitate a point split for all participants here, leaving 20 points for each.  The details on how to facilitate a point split were provided in your Community Support question below.  This process is recommended so that you can maintain an audit trail of all your questions asked, rather than a Moderator doing these for you.  Accept one here for 20, and post new questions in this topic area for the other 4 Experts.

http://www.experts-exchange.com/jsp/qShow.jsp?qid=20268507

Thank you,

EE Moderator
0
 

Author Comment

by:ph0bia
ID: 6810389
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6811021
Ph0bia --->  Hats off to you in acknowledgement, you do EXCELLENT WORK!

:)

EE Moderator
0
 

Author Comment

by:ph0bia
ID: 6812741
Cheers Moondancer :-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now