myRecordset.Open

[timothyl]

Hello, I am reading my Access book with vba and the topic is ADO and I am "learning" the following

Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnnX

myRecordSet.Open SQLstatement

' cnnX = variable that defines the connection, you can leave it as 'cnnX(good '
'as any other name)
' myRecordSet is the name you want to give your record set(usaly left as 'myRecordset)
' SQLstatement is a valid that is NOT an ACTION query

But where do I put this. I tried under a button click event with a simple SELECT statement, but received an error indicating that this was not the right way to use this. Do I put the first part in a module an then call it with myRecordset.open. Am confused. Any help is appreciated. Tim

 

[SOS]

Hello, I am reading my Access book with vba and the topic is ADO and I am "learning" the following

Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnnX

myRecordSet.Open SQLstatement

1. Without SEEING your SQLstatement we can't verify that it is a valid SQL string. So you might post it as well.

2. The click event of a button is fine but you need some modifications.

Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset

MyformName.RecordSource = myRecordSet.Open(SQLstatement, cnnX, adOpenDynamic, adLockOptimistic)

The part MyFormName.Recordsource =...
is just in case you wanted to assign it to a form. But just opening the recordset will be:

Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset

myRecordSet.Open SQLstatement, cnnX, adOpenDynamic, adLockOptimistic
 
myRecordSet.Close
cnnX.Close
Set myRecordSet = Nothing
Set cnnX = Nothing

But what do you want to do with it?

 

[timothyl]

SOS, thank you for your reply, at the time of witting I was reading a access book w/vba and I was not following the discussion. Since, I have
, kind off, sorta, some what got a fledgling understanding of this. I made a simple form with 3 cmd buttons and place this under one of them

Private Sub CmdDelete_Click()
On Error GoTo cnnError

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim rsDelete As New ADODB.Recordset
rsDelete.ActiveConnection = cnn

rsDelete.Open "SELECT * FROM Equipment WHERE DemoID = '" & Me.txtbox1.Value & "'", , adOpenDynamic, adLockOptimistic, adCmdText

If rsDelete.EOF = False Then
With rsDelete
.Delete
.Update
.Close

End With

End If

MsgBox "Record Deleted", vbInformation

Set rsDelete = Nothing
Set cnn = Nothing

Exit Sub

cnnError:
MsgBox "There was an Error Connecting to the DataBase." & Chr(13) _
& Err.Number & ", " & Err.Description

End Sub

This might have extra lines it dose not need (not sure) but works!, the two other buttons have similar code for Update and Addrecord.

But know have a real need, am tiring to move through Record set with this under a cmd button

Private Sub Command131_DblClick(Cancel As Integer)

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim rs As New ADODB.Recordset
rs.ActiveConnection = cnn

rs.MovePrevious
End Sub

Can not get to work, says recordset not open, tryed various things but no luck(there must be a argument needed for how to move through rs) I would
appreciate any help.

Thanks again Tim

 

[SOS]

Sorry, but I have to ask again - what are you trying to do with the recordsets? It seems as though you just need to use a bound form and then use something like:

DoCmd.RunCommand acCmdRecordsMoveNext

etc. to navigate on the form. ADO seems a little extreme here.

 

[timothyl]

SOS, I have form called Build Sheet, sales staff use it to set spects for wheel chairs to be used as demo's (which customer then use, so they know when time to purchase they are getting the right one as they can cost upward of $45,000.)

All I want to do is navigate through my saved Build Sheet froms, I tried using the wizard but it navigate on a different field then I wanted. I want to navagate on the ID field of the form(auto Number), it was navagating on a grouped demochairID field

As I am tring to understand ADO it seemed like a good excersise, not somting I would do under normal sircumstances

Thank for such a quick reply Tim

 

[SOS]

You would need to open the ADO recordset in the form's On Load event and then assign it to the form as the recordsource.

Then, from then on out you basically move it like any other bound source. The only reason you would use the ADO rst.moveNext and all that is if you are working on records in a loop in code. Opening the recordset to just do an .Update or .MoveNext isn't going to work because the recordset would be a different one than on the form.

It's hard for me to explain but hopefully that helps.

 

[timothyl]

I will play with your explanation also with the docmd advice, again thanks for all the help