Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 2nd, 2003, 12:36 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default converting DAO code to ADO

Hey all,
Can anyone tell me how to convert a workspace in ADO? for example, dim dbs as new database would look like dim dbs as new adodb.connection

so is there any keywords for workspace???

thnx

Sam Gharnagh
Jr. Programmer Analyst
Comp Sci at UofW
__________________
Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old October 2nd, 2003, 02:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

(duplicate post)

Hi Sam,

Glad to here you got it going.

In ADO the Connection object does double duty compared to DAO.

In DAO the Workspace object defines the session for a user and the Database object defines a data source.

In ADO the Connection object does both. It defines a session for a user for a data source. The conversion looks like:


' DAO
Dim dbs as DAO.Database
Set dbs = DBEngine.Workspaces(0).OpenDatabase(".\Northwind")


' ADO
Dim cnn as New ADO.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\Northwind.mdb;"

(though there are several ways to write these statments)

HTH,

Bob

 
Old October 3rd, 2003, 09:13 AM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can also use this and save a line of coding.

CurrentProject.Connection

This (in Access) defaults to the DB that is active. If you are trying to get information for multiple DB's then you would need to use the previous example....



Kenny Alligood
 
Old October 7th, 2003, 01:16 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have this code which is in DAO and I would like to convert it to ADO. What this code baiscally does is that, identifies the user and the groups that user belongs to. I don't know how to retreive this info using DAO. Thanks in advance.
'Supervisor has special priveleges; they can see the 'Admin' menubar.
Dim W As Workspace, US As String, U As User, i As Integer
Set W = DBEngine.CreateWorkspace("NewWS", "MyAdmin", "MyPassword")
US = CurrentUser()
Set U = W.Users(US)
Set SubCmdBar = CmdBar.Controls(5) 'admin menu
SubCmdBar.Controls(1).enabled = True
SubCmdBar.Controls(2).enabled = True

If U(i).Name = "Admins" Or U(i).Name = "Supervisor" Then
     SubCmdBar.Visible = True
     If U(i).Name = "Admins" Then 'show database window
     Else 'hide database window
     End If
Else
     SubCmdBar.Visible = False 'admin window is not shown
     'hide database window
End If
 
Old October 7th, 2003, 02:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Something like the following should get it for you. First, you need to set another reference to the "Microsoft ADO Ext. 2.x for DDL and Security" library. This is the ADOX library which contains the security extentions required to deal with JET security.

The ADOX catalog object contains user and group collections (and other collections that describe the db schema). You'll also need a user object. The CurrentUser method is also valid with ADO:

Sub TestADO()

    Dim C As New ADOX.Catalog
    Dim U As ADOX.user

    ' open catalog
    C.ActiveConnection = CurrentProject.Connection

    Set U = New ADOX.user
    U.Name = CurrentUser()

    Set SubCmdBar = CmdBar.Controls(5) 'admin menu
    SubCmdBar.Controls(1).Enabled = True
    SubCmdBar.Controls(2).Enabled = True

    If U.Name = "Admins" Or U.Name = "Supervisor" Then
        SubCmdBar.Visible = True
        If U.Name = "Admins" Then 'show database window
            Else 'hide database window
        End If
    Else
        SubCmdBar.Visible = False 'admin window is not shown
        'hide database window
    End If

End Sub

Hope this gives you some ideas.

Bob

 
Old October 7th, 2003, 02:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

The above doesn't really use the catalog object, but you can use its users collection like:

With cat
 For Each user In .Users
   Debug.Print " " & usrLoop.Name
   Debug.Print " Belongs to these groups:"
   ' Enumerate all Group objects in each User
   ' object's Groups collection.
   If user.Groups.Count <> 0 Then
     For Each grpLoop In usrLoop.Groups
       Debug.Print " " & grpLoop.Name
     Next grpLoop
    Else
     Debug.Print " [None]"
    End If
Next user

That's straight from the help file (search your computer for ADO210.CHM)

I haven't tested any of this stuff, but just wanted to point out the ADOX library which you may not have been aware of.

 
Old October 14th, 2003, 11:03 AM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Bob,
Thanks for the help! With your help I used the ADO group collection objects :)
I have another problem with converting DAO to ADO. I have converted all the code from DAO to ADO using the microsoft KB website as a guide. But I have a problem with recordsets.
in my old dao code i had a global recordset called rst which was opened and used from a few different forms, whenever u click on the list. When I changed it to ado i used rst.open method but since the recordset is already open at a different point, it will give me an error that the object is already open so i cant reopen it.
this was not a problem before, if u know any sites or tutorials that i could read on about ADO recordsets that would be great. I have looked up all of the recordset methods, and properties on microsft site.
I also tried using the execute method but that also gives me some other errors.
thanks in advance

Sam Gharnagh
Jr. Programmer Analyst
Comp Sci at UofW
 
Old October 14th, 2003, 01:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Sam,

Two thoughts.

First, if you're getting an error telling you that the recordset is already open, that means that the global recordset object is already instantiated and you can just reference its methods and properties in code with variablename.propertyname syntax. There is no need to call the Open method again. The object already exits. Just reference it.

Second, if you are done using the recordset object, you should destroy it as quickly as possible. You can always re-open it by calling the Open method if you need to recreate it. COM objects (like ADO objects) are responsible for there own life, so to speak, and should be explicitly destroyed using the Set and Nothing keywords to free up any memory and system resources associated with them. So in all of your procedures using ADO objects, if you don't need the object anymore, destroy it with:

rst.Close
Set rst = Nothing

I use global connection objects a lot with syntax like:

' Release the global connection object
  If Not gcnn Is Nothing Then
    If gcnn.State = adStateOpen Then
      gcnn.Close
      Set gcnn = Nothing
    End If

All your ADO objects should be released in this way. You can also include object clean-up code in your error handlers and give your procedures a single exit point as in:

On Error GoTo HandleErr

' Procedure body goes here

ExitHere:
    Set gcnn = Nothing
    Set grst = Nothing
    Exit Sub

HandleErr:
    MsgBox "Error " & Err.Number & ": " & _
     Err.Description, vbOKOnly, "foo"
    Resume ExitHere
    Resume

End Sub

Instead of ever using Exit Sub just call Exit Here instead. .NET's new garbage collection feature handles a lot of this object clean-up, memory management stuff for you. But with COM you're pretty much on your own.

HTH,

Bob

 
Old October 15th, 2003, 01:33 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wow, im a little lost and confused but thanks, this does help! I ll get right on that! I removed all the calls to recordset.open and put one call in the form_load and I closed it after I was done with it. I have a few questions now, do i need to set rst to nothin? what if i want to use rst later on.

How can i use the execute method with adDynaSet option, Is it even possible to do that? I mean, should i use execute method instead of rst.open??? the reason i use rst is that i can use the cursor with rst.Movefirst, moveLast.... metheds.
Sam
p.s. I ll get back to you in a few days, I'wont be in this location. Thanks for all your help!
 
Old October 15th, 2003, 10:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Sam,

Trust me, you're not lost and confused yet. Read on. I apologize for the length of this post, but ADO recordset objects and cursors behave in very unexpected and peculiar ways, and you really have to get your hands dirty with a little code to understand their idiosyncrasies.

Quote:
quote:do i need to set rst to nothing? what if i want to use rst later on.
First, you have total control over the lifetime of ADO objects. It's completely up to you when you create them and destroy them. To create them, use:

Dim rst AS ADODB.Recordset
Set rst = New ADODB.Recordset

The Set statement explicitly creates a new instance of the object. To destroy the object use:

rst.Close
Set rst = Nothing

If you destroy a recordset object and need another one, just re-Set your object variable to a New instance (Set rst = New ADODB.Recordset), and call the new instance's Open method (rst.Open string, connection, cursor, lock, etc.). That's all there is to it.

One word of warning. You may run into the following declaration syntax:

Dim rst As New ADODB.Recordset (instead of Dim rst As ADODB.Recordset). The New keyword is added to the declaration.

Here, the New keyword enables implicit creation of the recordset object. The first time the object is referenced a new instance of it will be created. No Set statement is needed. AVOID THIS SYNTAX. Watch what happens when you try and destroy an object instantiated in this way. Place the following code in a module in the Northwind database and run it from the Debug window:

Sub rstTest()

    Dim rst1 As ADODB.Recordset
    ' implicit instantiation
    Dim rst2 As New ADODB.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM Customers"
    ' explicit instantiation
    Set rst1 = New ADODB.Recordset

    rst1.Open strSQL, CurrentProject.Connection, adOpenStatic
    rst2.Open strSQL, CurrentProject.Connection, adOpenStatic

    rst1.Close
    Set rst1 = Nothing

    rst2.Close
    Set rst2 = Nothing

    If rst1 Is Nothing Then
        MsgBox "Explicit rst1 is nothing."
    Else
        MsgBox "Explicit rst1 is NOT nothing"
    End If

    If rst2 Is Nothing Then
        MsgBox "Implicit rst2 is nothing."
    Else
        MsgBox "Implicit rst2 is NOT nothing"
    End If

End Sub

Notice that the implicitly instantiated object (rst2) isn't destroyed, even though you've set it to Nothing. I don't know exactly why this occurs, but the fact that it does will lead to memory leak problems if you use implicit instantiation.

Quote:
quote:How can i use the execute method with adDynaSet option, Is it even possible to do that? I mean, should i use execute method instead of rst.open???
It isn't possible to use the adOpenDynamic (Dynaset is a DAO term) cursor type with the Execute method of a Connection or Command object because the Execute method always returns a forward-only, read-only recordset (if it returns records at all, and it doesn't have to). I only use the Execute method to execute queries that don't return records (i.e., "action queries" like INSERTs, UPDATEs, DELETEs). Using the Open method of the Recordset object will allow you to use cursor types, like adOpenDynamic, that provide more functionality.

Now here's where it gets pretty weird. I hope you're sitting down. While adOpenDynamic is part of the ADO cursor enum, and you can use it in your code, Jet does not support dynamic cursors, so using adOpenDynamic will give you either a Keyset cursor or a Static cursor, depending on your Lock Type. If you use adOpenDynamic and adLockReadOnly you will get a Static cursor. All other combinations of adOpenDynamic and the LockType enum will give you a Keyset cursor. A true dynamic cursor would allow users in a multi-user environment to instantaneously see data modifications made by other users. Jet doesn't support this functionality. A Keyset cursor allows users to make changes, but if they want to see changes made by other users, they have to refresh their cursor (by closing and reopening a recordset object).

To see what type of cursor you are actually getting with the various Cursor Type/Lock Type combinations, I put together the following module in a new db and just imported the Customer table from Northwind. You can run it from the debug window:

Sub WhatsMyCursorType()
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim strCursor As String
    Dim strLock As String

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset

     ' Change the cursor type/lock type properties to see which
     ' kind of cursor the OLE DB provider gives you.

     rst.Open "Customers", cnn, adOpenDynamic, adLockOptimistic
     ' rst.Open "Customers", cnn, adOpenDynamic, adLockBatchOptimistic
     ' rst.Open "Customers", cnn, adOpenDynamic, adLockPessimistic
     ' rst.Open "Customers", cnn, adOpenDynamic, adLockReadOnly

    strCursor = GetCursorType(rst.CursorType)
    strLock = GetLockType(rst.LockType)

    Debug.Print "The OLE DB provider opened a " & strCursor & " cursor using " & strLock & " locking."

End Sub

Function GetCursorType(CursorType As Integer) As String

    ' *******************
    ' CursorTypeEnum
    '
    ' adOpenDynamic = 2
    ' adOpenForwardOnly = 0
    ' adOpenKeyset = 1
    ' adOpenStatic = 3
    ' adOpenUnspecified = -1
    '*******************

    Select Case CursorType
        Case 2
            GetCursorType = "Dynamic"
        Case 0
            GetCursorType = "ForwardOnly"
       Case 1
            GetCursorType = "Keyset"
        Case 3
            GetCursorType = "Static"
        Case -1
            GetCursorType = "Unspecified"
    End Select

End Function

Function GetLockType(LockType As Integer) As String

    '*******************
    ' LockTypeEnum
    '
    ' adLockBatchOptimistic = 4
    ' adLockOptimistic = 3
    ' adLockPessimistic = 2
    ' adLockReadOnly = 1
    ' adLockUnspecified = -1
    '*******************

    Select Case LockType
        Case 4
            GetLockType = "BatchOptimistic"
        Case 3
            GetLockType = "Optimistic"
       Case 2
            GetLockType = "Pessimistic"
        Case 1
            GetLockType = "ReadOnly "
        Case -1
            GetLockType = "Unspecified"
    End Select

End Function

Running the above as is outputs this message to the debug window:

"The OLE DB provider opened a Keyset cursor using Optimistic locking." (even though I specified the adOpenDynamic cursor type).

Another example would be using the adOpenForwardOnly cursor type. You'd expect to get a cursor that would generate an error if you attempted to use the MovePrevious method, right? That's what happens if you use adOpenForwardOnly and adLockReadOnly. You get a true ForwardOnly/ReadOnly cursor. But if you use adOpenForwardOnly and adLockOptimistic, you are suddenly able to use the MovePrevious method. Is this because your ForwardOnly cursor miraculously gained added functionality from the new lock type? Nope. It's because this combination doesn't give you a ForwardOnly cursor, it gives you a Keyset cursor. The true ForwardOnly/ReadOnly cursor only supports the following methods:

   Find
   Notifications

The Keyset cursor supports these methods (with Optimistic Locking):

   AddNew
   Delete
   Find
   Holding Records
   MovePrevious and Move
   Notifications
   Update
   batch updating

I'm working on a module at the moment that will output all of the methods supported by all of the Cursortype/LockType combinations, just to help me keep this straight.

Anyway, just wanted to let you in on two of ADO's little secrets: 1. The object you thought you destroyed may still be alive and well if you didn't instantiate it correctly, and 2. The cursor you asked for is frequently not the one you get. It takes a little VBA to interrogate your recordset objects thoroughly, and find out what they are really up to.

Oh, I typically use adOpenKeyset/adLockOptimistic if I want an updateable recordset I can navigate through, and adOpenForwardOnly/adLockReadOnly if I just want to fetch read-only data.

Here are some pretty standard ADO blocks:

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String

    strSQL = "SELECT JobTitle FROM tblJobTitle " _
       & "ORDER BY JobTitle"

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

    ' code

    rst.Close
    Set rst = Nothing

or here the conection string references a split backend .mdb:

    ' module level recordset variable
    Dim mrstClients As Recordset

Private Sub Form_Open(Cancel As Integer)
    Dim cnnClient As ADODB.Connection
    Dim strProvider As String
    Dim strDataSource As String

    strProvider = "Microsoft.Jet.OLEDB.4.0"
    strDataSource = CurrentProject.Path _
        & "\dbOutpatient.mdb."

    Set cnnClient = New ADODB.Connection
    cnnClient.Open "Provider = " & strProvider & _
        "; Data Source = " & strDataSource

    Set mrstClients = New ADODB.Recordset
    mrstClients.Open "qryClients", cnnClient, adOpenKeyset, _
        adLockOptimistic


Regards,

Bob






Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO or DAO knowledge76 Access VBA 3 October 12th, 2015 04:26 PM
how to convert this DAO connection code to ADO Con ruth26 VB Databases Basics 0 May 11th, 2006 05:15 AM
how to translate a code in DAO to ADO itsmenow Access VBA 2 July 7th, 2005 09:19 AM
DAO / ADO? merguvan Access VBA 8 January 18th, 2004 07:39 AM
How to connect .dbf file with ADO & DAO with code cbpanchal VB How-To 4 July 28th, 2003 10:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.