Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    8

    Unanswered: Retrieve sql resultset from local database object

    Hi there,
    I want to retrieve the comapany IDs from a table named companyA from the local database. For that, I am using DAO object..and I have to fetch these IDs to do the further processing.. the code is as follows:
    =====================
    Sub Comp_IDs ()
    Dim dbs As DAO.Database
    Dim sSQL As String

    On Error GoTo ErrorHandler

    Set dbs = CurrentDb()
    sSQL = "SELECT DISTINCT CompanyA.IDs"
    dbs.Execute sSQL
    dbs.Close
    Set dbs = Nothing
    ErrorHandler:
    MsgBox "Error Number: " & Err.Number & vbCrLf _
    & "Source: " & Err.Source & vbCrLf _
    & "Error Description: " & Err.Description

    End Sub

    ==============

    The error description says that it cannot execute sql statement..
    Any suggestions?? Also, how would I fetch these IDs into one more Subroutine?
    Please help me out.. I would really appreciate it.
    Thanks.

    -Rakheevissa

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by rakheevissa
    Hi there,
    sSQL = "SELECT DISTINCT CompanyA.IDs"
    Try:
    sSQL = "SELECT DISTINCT CompanyA.IDs From CompanyA;"

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Since the Execute function performs only action queries then you have to change your sql
    sSQL = "SELECT DISTINCT CompanyA.IDs INTO anyTempTableName From CompanyA;"

    The result will be stored in the temp table

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so you need a recordset (even with hammbakka's temp table and FROM hint)

    dim dbs as dao.database
    dim rcs as dao.recordset
    set dbs = currentdb
    set rcs = dbs.openrecordset("SELECT DISTINCT IDs FROM CompanyA;")
    if rcs.bof and rcs.eof then
    msgbox "ZERO records"
    else
    with rcs

    *****************
    end with
    endif


    where ***************** is whatever you want to do with the recordset
    e.g. to dump them to immediate window
    do while not .eof
    debug.print !IDs
    .movenext
    loop


    izy
    currently using SS 2008R2

Posting Permissions

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