?
Solved

Seeking an example database that uses ADO coding

Posted on 2010-01-12
17
Medium Priority
?
772 Views
Last Modified: 2013-12-05
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
Comment
Question by:9XqUwH3S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26293076
Have a look at the following:
http://support.microsoft.com/kb/281998


Gary
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26293091
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26293113
0
 
LVL 77

Expert Comment

by:peter57r
ID: 26293190
"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
 

Author Comment

by:9XqUwH3S
ID: 26293415
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
 

Author Comment

by:9XqUwH3S
ID: 26293435
ADP stands for Active-X Direct Object Programming, correct?
0
 
LVL 46

Accepted Solution

by:
tbsgadi earned 1000 total points
ID: 26293455
0
 

Author Comment

by:9XqUwH3S
ID: 26293544
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26293565
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
 

Author Comment

by:9XqUwH3S
ID: 26293623
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26293657
For basic functionality in Access your easier off using DAO.
You should learn both & use ADO when needed.
0
 

Author Comment

by:9XqUwH3S
ID: 26293695
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1000 total points
ID: 26293922
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26294022
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
 

Author Closing Comment

by:9XqUwH3S
ID: 31676076
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26303267
Glad to help!
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question