Setting a Form''s Recordset with ADO

Question

Post reply

Setting a Form''s Recordset with ADO

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
March 24, 2005 at 8:20 am

#65624
So I have a form that had a record source with a big honking Select statement. The customer wanted the form to be dependent on values from another form. I changed the Select statement in the Form properties Record Source adding a "WHERE 0=1" so it wouldn't actually retrieve any data, then added the following:
Private Sub Form_Load()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rstSQL = "SELECT * FROM Commitments_View Where Targetyear=" & GYear
    rst.Open rstSQL, sqldb, adOpenKeyset, adLockOptimistic
    rst.MoveFirst
    Set Me.Recordset = rst
    Set rst = Nothing
End Sub
GYear is a Global variable set in another form. The code bombs at the "Set ME.Recordset = rst" saying that rst isn't a valid recordset property. I go into debug and look at the rst object properties and find:
- Cursor Type=adOpenForwardOnly
- Lock Type=adLockReadOnly
- Record Count=-1
- Source="SELECT * FROM Commitments_View Where Targetyear=2005"
The Source SQL statement is correct. If I copy it and paste it into Query Analyzer it runs fine and returns 128 records. It appears ADO isn't opening the recordset. I have no idea why. (ps. the connection is set in a background form and is available as a global variable. I've used this code in several other forms and it has worked just fine.)
Any ideas why this isn't working? I've been banging my head against this one for the last day and I'm out of ideas...
Any help/suggestions are appreciated.
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 1

Answered my own question for those of you who are interested...

I had to add "conn.cursorlocation=aduseclient" to my connection definition.

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 2

I need to user this too, very soon. Can you please show the complete code, ie where you inserted your solution code. Thanks.

Lester Vincent,

Sydney

smknox Hall of Fame Points: 3275 More actions · Answer 3

Private Sub YourFunction()

Dim Conn As ADODB.Connection

Dim cmd As ADODB.Command

Dim rst as ADODB.recordset

Dim rstSQL as String

Set Conn = New ADODB.Connection

Conn.Open fstrCnn()

Set rst = New ADODB.recordset

rstSQL= "SELECT..." statement

rst.Open rstSQL,,,

rest of your code....

Conn.Close

Set Conn = Nothing

End Sub

Note: fstrCnn() is set elsewhere. If you need to define your connection string here then:

Dim strCnn as string

strCnn= "Provider=sqloledb;Data Source=YourServer;Initial catalog=YourDB;Integrated Security=SSPI"

HTH,

Sam

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 4

Thanks Sam. I can pick it up from there.

Regards,

Lester Vincent

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

Here's where you need to set the cursor location

Set Conn = New ADODB.Connection
Conn.CursorLocation=adUseClient Conn.Open fstrCnn()