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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
valhallatechAuthor Commented:
perhaps - using essentially same techinque i.e.
set Me.Recordset = MyADODB_r-only_Recordset
??
Glenn
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.