Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 781
  • Last Modified:

Seeking an example database that uses ADO coding

Does anyone have an example database in their code library or folder of goodies that uses ADO.

I want to study better how it is used with forms and to filter and seek records. My book collection and database templates are mostly VBA.

Any help or links to resources are much appreciated.

Sincerely,
Steve
0
9XqUwH3S
Asked:
9XqUwH3S
  • 7
  • 6
  • 2
  • +1
2 Solutions
 
tbsgadiCommented:
Have a look at the following:
http://support.microsoft.com/kb/281998


Gary
0
 
Rey Obrero (Capricorn1)Commented:
here is the sample code from VBA help  Seek Method (ADO)



'BeginSeekVB
Public Sub Main()
    On Error GoTo ErrorHandler

    ' To integrate this code replace the data source
    ' in the connection string
 
     'recordset and connection variables
    Dim rstEmployees As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim strSQLEmployees As String
    
    Dim strID As String
    Dim strPrompt As String
    strPrompt = "Enter an EmployeeID (e.g., 1 to 9)"
    
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
                "Data Source='c:\Program Files\Microsoft Office\Office\Samples\northwind.mdb';"
    Cnxn.Open strCnxn
     
     ' open recordset server-side for indexing
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.CursorLocation = adUseServer
    strSQLEmployees = "employees"
    rstEmployees.Open strSQLEmployees, strCnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
    
    ' Does this provider support Seek and Index?
    If rstEmployees.Supports(adIndex) And rstEmployees.Supports(adSeek) Then
        rstEmployees.Index = "PrimaryKey"
        ' Display all the employees
            rstEmployees.MoveFirst
            Do While rstEmployees.EOF = False
                Debug.Print rstEmployees!EmployeeId; ": "; rstEmployees!firstname; " "; _
                    rstEmployees!LastName
                rstEmployees.MoveNext
            Loop
        
    ' Prompt the user for an EmployeeID between 1 and 9
          rstEmployees.MoveFirst
          Do
             strID = LCase(Trim(InputBox(strPrompt, "Seek Example")))
             ' Quit if strID is a zero-length string (CANCEL, null, etc.)
             If Len(strID) = 0 Then Exit Do
             If Len(strID) = 1 And strID >= "1" And strID <= "9" Then
                rstEmployees.Seek Array(strID), adSeekFirstEQ
                If rstEmployees.EOF Then
                   Debug.Print "Employee not found."
                Else
                   Debug.Print strID; ": Employee='"; rstEmployees!firstname; " "; _
                   rstEmployees!LastName; "'"
                End If
             End If
          Loop
    End If
    
    ' clean up
    rstEmployees.Close
    Cnxn.Close
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndSeekVB

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
0
 
peter57rCommented:
"My book collection and database templates are mostly VBA."

ADO is not an alternative to VBA; it is an alternative to DAO.  Both are used within VBA, but for an ADP you would expect to use ADO for directly accessing the SQL Server data.  In an mdb/accdb with mdb/accdb data files you can use either, although DAO is now the preferred (and sometimes only) option.



0
 
9XqUwH3SAuthor Commented:
So I should be learning DAO. Okay.

Is ADOX an acronym synonymous with ADO?

Should I be seeking a book or articles about DAO, then?

I'm using this database for personal purposes with one user, but there isn't much sense in learning old technology then, is there (I assume not)?


0
 
9XqUwH3SAuthor Commented:
ADP stands for Active-X Direct Object Programming, correct?
0
 
tbsgadiCommented:
0
 
9XqUwH3SAuthor Commented:
Okay, I'll wrap this up,

but to tbsqadi: Your article,

"ADO is a more recent Microsoft Data Access technology."

seems to suggest the opposite from Peter57r's comment,

"although DAO is now the preferred (and sometimes only) option."

So, ADO is newer, but DAO tends to be preferred, is that your understanding?

Let me clarify this point and I'll study the linked articles and award points as best I can.

Thanks.

Comments welcomed from both contributors, by the way.
0
 
tbsgadiCommented:
The conclusion from the article was not as you stated but:

DAO is not going anywhere. Microsoft is committed to supporting it well into the future. If your application is purely a Microsoft Access database, then DAO should be the obvious choice. If you need some advanced recordset manipulation features and are connecting to a supportive outside source, then ADO is the ticket. Also, it should be noted that there is nothing wrong with mixing these two methods within the same project.
0
 
9XqUwH3SAuthor Commented:
So if I ultimately plan to employ SDK (Adobe Acrobat) coding in this project later down the line, I would want to use DAO.  

Per according to http://www.utteraccess.com/forums/printthread.php?Board=53&main=1241488&type=post article, I want to use DAO

"if all you need are the basics. If you are dealing solely with an Access (Jet) database, then DAO will perform faster than ADO and should be your tool of choice for your application. If you are using Access as a front-end for connecting to another source that can benefit from the newer ADO features, then ADO is for you."

But if I call SDK functions in the future... ADO, right?

For record, I eventually intend to split this database (as a learning experience) and intend to have my form copy text about the document which the record will describe and to paste it to the Adobe .pdf  file's meta tags.

Keep in mind, this is both a useful database and a learning process. I have a copy of the database for adding and changing, obviously.

So what would you both recommend, knowing what you know, with this in mind??
0
 
tbsgadiCommented:
For basic functionality in Access your easier off using DAO.
You should learn both & use ADO when needed.
0
 
9XqUwH3SAuthor Commented:
tbsgadi:

Article says, "Note that an Access2003 created database has both the DAO and ADO libraries selected (very dangerous)" but presumably it is okay in .accdb.

Well, I will close out this thread once I hear from anyone on wether or not I can call SDK functions with VBA, DAO or ADO.

Should I open that with another question.

Thank you all for your patience with my ADD concerning all of the above :  )
0
 
peter57rCommented:
I think there is a problem with the article that gary linked to.
It appears to have been originally written before A2007, and although it has been added to after that it seems that the earlier text has not been reviewed fully.  I particularly dislike the expression '"DAO is not going anywhere." which is as ambiguous as you can get.  It really means "DAO is not going to disappear".

The current situation for ADPs is that you should be using ADO - it has always been so, nothing has changed in Access 2007.

ADO is 'newer' than the original DAO and for a short time MS pushed it as the way forward even for use with mdb data.
However, it never really took off amongst Access-based developers, although was better received by VB-based developers.  

For mdb data, dao is the preferred method to use, although there are one or two things (some SQL commands particularly)  that require the use of ADO connections.  

For the Access 2007 accdb file structure MS renewed their interest in DAO and produced an updated DAO (ACEDAO) to address the new features of accdb files.  ADO has not been updated.  ACEDAO also deals with mdb files so is now definitely the current technology.

But ADPs still require ADO.

There has been a question mark over the future of ADPs for a while.  MS have not updated the technology and encourage alternatives for Access2007.  MS are going to review the 'Access as a front-end to SQL Server' approach for Access v15 (the one after A2010).  Whether this means upgrading ADPs or replacing them with something else I have no idea.

















0
 
tbsgadiCommented:
There are many viewpoints each way...Have a look here for some of them:
http://stackoverflow.com/questions/1039224/is-it-better-to-use-ado-or-dao-in-access-2007
0
 
9XqUwH3SAuthor Commented:
tbsgadi: The utteraccess article did a nice job of comparing the differences. I found it helpful.

Peter57r: Thank you for all of the typing you did to summarize the whole conversation.

(I think of  The Family Guy when I see your comments.)

The education was deeply appreciated. : )
0
 
tbsgadiCommented:
Glad to help!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now