Results 1 to 6 of 6

Thread: ADO v/s DAO

  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: ADO v/s DAO

    Hi,

    I am new to Data Access methods and have some basic questions. Appreciate if anyone can help.

    1. I have to integrate between Excel and Access. Excel VB will do data manipulation in Access, the results of which needs to be populated back in Excel. Should I use ADO instead of DAO?[/indent]
    2. In DAO FindFirst, Edit and Nomatch() methods are available. What are the similar methods available in ADO?
    3. I am using the following code for ADO initialization. Is this fine? Critical questions are in red.

      Code:
      Sub ExIm_AccessData() 
      Dim cnt As ADO.Connection
      Dim rst ADO.RecordSet
      Dim stDB, stConn as String
      Dim wbBook As Workbook 
      Dim wsSheet1 As Worksheet 
      
      'Instantiate 
      Set cnt = New ADODB.Connection 
      Set rst = New ADODB.Recordset 
      
      'Path to the database
      stDB = "c:\db1.mdb"  'Is relative reference possible for file?
      
      'Create the connectionstring 
      stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
      "Data Source=" & stDB & ";" 
      
       Need help after this to open a record and edit it in a table in db1.mdb 
    Thanks

  2. #2
    Join Date
    Apr 2004
    Posts
    4
    Assuming your code is in Access and Not in Excel.

    'Path to the database
    stDB = "c:\db1.mdb" 'Is relative reference possible for file?

    CurrentDb.Name

    It would probably be much easier to do this with DAO.

    Sub ExIm_AccessData()
    Dim db as DAO.Database
    Dim rst DAO.Recordset
    Dim wbBook As Workbook
    Dim wsSheet1 As Worksheet

    'Instantiate
    Set db = dbengine(0)(0)
    Set rst = db.OpenRecordse(<<your recordset>>)

    Need help after this to open a record and edit it in a table in db1.mdb
    -What is it you want to do at this point?

  3. #3
    Join Date
    Feb 2004
    Posts
    47

    Exclamation

    Thanks jfdemulling.

    Here are replies and some questions...

    -What is it you want to do at this point?
    I want VB code in Excel to call a function to manipulate data in Access. Access stores Budget, all monthly Actuals and Forecast data (which is cumbersome in spreadsheet).

    I want Excel to Forecast... i.e. it will pass a forecasted price-set to Access and the VB code will:-
    1. calculate the Forecasts
    2. store these Forecasts in Access tables
    3. populate back the result in Excel for analyses


    I have made a working code in Access using DAO. Though Excel gives me the ability to integrate with Access using "Get External Data" and run SQL queries, there is nothing in standard Excel that allows me to run VBA functions. Hence I thought I could have an ADO code that connects to the Access database with capability to run the code in both Excel and Access.

    I have hit the following snags (and this is where I could really use some help).

    1. In DAO FindFirst, Edit and Nomatch() methods are available. What are the similar methods available in ADO?
    2. When I run the ADO code below (pl. refer previous post) in Access, I get an error stating that the database is in use (guess it is because of the ldb file lock). Is there a way to circumvent this?


    Appreciate your response

    Rgds,

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Using ADO open the database non-exclusivly as follows:

    Dim cn as ADODB.Connection
    Dim rs as ADODB.Recordset

    Set cn = new ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Test.mdb;Persist Security Info=False;Exclusive=0"
    cn.open

    set rs = new ADODB.recordset
    rs.ActiveConnection = cn
    rs.Open "SELECT * FROM Table1"

    rs.Close
    cn.Close
    Set rs=Nothing
    Set cn = Nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Feb 2004
    Posts
    47
    Hi SCIROCCO,

    Thanks for your reply.

    Can you please let me know the alternative methods for FindFirst, NoMatch and Edit in ADO.

    Rgds,

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    For FindFirst in ADO you would use the Find method with the start parameter set to 1

    rsData.Find(criteria, SkipRows, searchDirection, start)

    i.e.

    rsData.Find "FIELD1 LIKE 'Can%'", 1, adSearchForward, 1

    For NoMatch there is no similar property in ADO however with the Find method if the criteria is met, the recordset position is set on the found record; otherwise, the position is set on the end of the recordset.

    So if you test for rsData.EOF after the calling the Find method and it is true then no match has been found.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •