Using ADO recordsets on MDB Forms

Hi xperts
Can someone confirm or deny this for me? I'm working in an MDB file, but am wanting to assign an adodb.recordset to a forms recordset property, and am getting errors trying this. Using a DAO.recordset works fine.  I read somewhere that forms in mdb files use DAO, and in ADP files use ADO - So I assume therefore that you cant do what I want, but the help file leaves ample room to assume you can do this - so could someone clear this up for me?

Here's an simplified example of what I want to do - this code would exist in a form:

Public Sub DetermineRecordset()
dim r as new adob.recordset

r.open "SELECT Stuff FROM sometable WHERE igetwhatIwant=true", connection etc etc etc

set me.recordset = r ' << Error occurs here for ado, but not dao Can I persuade this to work?

End Sub

I know its not many points, but a yes or no is proably all I need - if yes I'll ask the question of how for more points
Thanks
Glenn
LVL 2
valhallatechAsked:
Who is Participating?
 
Stephen_PerrettCommented:
Glenn, one thought, The mdb format I used was Access 2002 - 2003

May not have worked in earlier versions.

My ADO version listed in References is

Microsoft Active X Data Objects 2.1 Library

Steve
0
 
Stephen_PerrettCommented:
Yes I have done it with read only data
Steve
0
 
Stephen_PerrettCommented:
Perhaps you should wait to get some other points of view on this
Steve
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
valhallatechAuthor Commented:
perhaps - using essentially same techinque i.e.
set Me.Recordset = MyADODB_r-only_Recordset
??
Glenn
0
 
Stephen_PerrettCommented:

I actually set the recordset from another form but essentially the same as

Set Me.Recordset = rstADO


I actually used a command object with many parameters to setup the recordset first.

I believe the following characteristics of the recordset may help you

  With rstADO
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .LockType = adLockBatchOptimistic '<--- Glenn I get the feeling this was important
        .Open cmd
        .ActiveConnection = Nothing
    End With

Steve
 
0
 
SidFishesCommented:
This connects to a local mdb...make sure that you have references to DAO and ADO (Microsoft ActiveX Data Objects)...and DAO above ADO in the list...


Dim cnnStr As String
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection

cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myPath\MyDB.mdb;"

cnn.Open cnnStr


   strSQL = "Select * from tblMyTable"
 
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
Debug.Print strSQL
     With rst
               
            If rst.RecordCount = 0 Then
                    MsgBox "None Found"
                    Exit Sub
                End If
                Me!txtID = !id
                Me!txtSomeField = !SomeField                
          .Close
    End With
   
   
    Set rst = Nothing


0
 
valhallatechAuthor Commented:
hi steve - my suspicion is that this is the same net result as I've done - but I'll have a crack at what you've done and see where the effective difference is
0
 
valhallatechAuthor Commented:
hi sidfish
thanks for the effort to reply - I'm not sure what you're getting at - yes I can connect to an external mdb file this way, but what I can't do is, from within a form within an mdb file is execute somthing like:

set me.recordset = rst

when rst happens to be of type ADODB.recordset. I notice you've set the field on the form to the value in the recordset, but it doesn't give me the benefits I want. Its highly likely I've missed your point though.

thx
Glenn


0
 
SidFishesCommented:
i missed your point actually...about the set recordset

your could try this...i use this in A2000 and A2002RT and it works...(altho i'm connecting to sql server...but that shoudn't matter...i've just modified the connection string)


Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myPath\MyDB.mdb;"
    cnn.Open
strSQL = "SELECT Stuff FROM sometable WHERE igetwhatIwant=true"
    Set rst = New ADODB.Recordset
        With rst
            .Open Source:=strSQL, _
                  ActiveConnection:=cnn, _
                  CursorType:=adOpenKeyset, _
                  LockType:=adLockOptimistic
             
              Set Me.Recordset = rst
           
            .Close
        End With
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

   
0
 
valhallatechAuthor Commented:
hi Steve
Yeah - I'm using 2003 - still haven't had a chance to play with it yet
Glenn
0
 
valhallatechAuthor Commented:
HI sidfish
Just to clarify  - the code just provided... is it executing in an MDB file or an ADP file
Glenn
0
 
SidFishesCommented:
with the connection string in the example...it's mdb

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: Stephen_Perrett {http:#13199631} & SidFishes {http:#13199739}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jimhorn
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.