Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    11

    Unanswered: How do I access data with VBA

    How do I obtain the value of a table field and place it in a variable with visiual basic?
    How do I obtain the value of a query result and place it in a variable with visual basic?

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    alterseneca,

    There are two disparate models for accessing data from VBA: the Data Access Objects (DAO) model and the ActiveX Data Objects (ADO) model. Which one you use is up to you but beware of trying to use both simultaneously.

    DAO is the native access method for the Jet database but (as far as I know) does not contain provision for accessing other ODBC databases. Of the two models it is more complex in that it contains many more objects and methods. It is also the older of the two and Microsoft recommends switching to ADO. In my opinion the only reasons for using DAO are if you will be accessing the Jet database and only the Jet database and are interested in response times; DAO using native calls is faster that ADO.

    ADO is at first a little abstruse but once learned much simpler. It has the advantage of communicating with all ODBC databases using identical syntax. In fact relating to your two questions, ADO hardly distinguishes between tables and queries.

    Let me give you a taste of ADO for accessing a table.

    Dim rs as ADODB.Recordset
    dim strSQL as string
    strSQL = "SELECT * FROM MyTable"
    Set rs = New ADODB.Recordset
    With rs
    .ActiveConnection = CurrentProject.Connection
    .LockType = adLockReadOnly
    .Open strSQL
    ..........
    .Close
    End With
    Set rs = Nothing

    The Open method returns a result set (recordset) positioned at the first row. You can then move through the result set using the .MoveNext method. To insert a value into your form control you would use syntax similar to:

    Me.MyControlName = !MyFieldName

    The attributes of the Open method can contain the name of a table or query:

    .Open "[MyTable]"
    .Open "[MyQuery]"

    or an explicit SQL statement:

    .Open "SELECT * FROM MyTable"

    You can also specify all the required parameter values as Open method attributes but I find it clearer to set the ActiveConnection and LockType parameters separately.

    I suggest get hold of an introductory text on the subject.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Oct 2004
    Posts
    11
    Thank you for your help, this seems to be what I want. I'm going to get a book on ADO soon.

    ---------
    Private Sub Command0_Click()

    Dim rs As ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT FieldOne FROM tblTableOne WHERE AutoID = 1"
    Set rs = New ADODB.Recordset
    With rs
    .ActiveConnection = CurrentProject.Connection
    .LockType = adLockReadOnly
    .Open strSQL
    Debug.Print rs.RecordCount
    .Close

    End With
    Set rs = Nothing


    End Sub
    ---------

    This is what I have so far (thanks for the code). How do I make a variable hold the returned value?

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi again,

    One way is to wrap your table access code in a function. For example:

    Public Function Retrieve_Count() as Variant

    .. code ..

    .Open strSQL
    Retrieve_Count = .RecordCount
    .Close

    ... code ...

    End Function

    Instead of a variant your function could be Long or perhaps Integer. Then inside an appropriate event on your form or whatever simply specify:

    Me.Variable = Retrieve_Count

    Now, I have at the back of my mind that the RecordCount property is not always accurate. In fact Help has this to say about the ADO property:

    Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

    You can overcome this by altering your SQL to something like:

    "SELECT COUNT(Field1) AS NumRecords FROM TableOne WHERE AutoId = 1"

    Then between the Open and Close simply code:

    Retrieve_Count = !NumRecords

    Note the use of "!" in this situation.
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Oct 2004
    Posts
    11
    Will that just not count the number of records I have? I want to take the field entry (value etc.). How do I place that in a variable?

  6. #6
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Quote Originally Posted by alterseneca
    I'm going to get a book on ADO soon.
    hi. may i recommend Access VBA Programming by Charles E. Brown and Ron Petrusha (McGraw Hill/Osborne (publ)). (Note: it's ADO-based). i picked it up in the summer and it's very helpful. i also tried the Weekend Crash Course which is also helpful but is more like a Weekend Crash *Refresher* Course. (Note: it's mainly ADO but also discusses DAO).

    w

    P.S. Someone give Rod a raise for one HELL of a reply!
    Last edited by wazz; 10-09-04 at 14:17.

  7. #7
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Yo alter...


    Don't waste your money on an ADO book yet. The net has valuable resources and a good place to start is msdn.microsoft.com

    My 2 cents..

    -Warren

Posting Permissions

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