Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    9

    Unanswered: ADODB.Recordset using Union Query as source

    Hello,

    I'm using MS Access 2003, MS Visual Basic 6.3 and creating an ADO type VB module. I am trying to use a saved union query as the record source but the VB code always returns 0 records. If I run the saved union query by itself, it returns all of the expected data.

    Is there something with the VB code I'm using that makes it return 0 records?

    BOF and EOF always return true... RecordCount always returns -1 which is normal from what I have read. Also I was able to substitute a normal Select query as the record source and then the VB code will return records. It just does not seem to like the Union Query for some reason... Access does not generate any errors with the following code.


    -------------------------------------------------
    [Acad_Union_Query] Saved Union Query in SQL
    -------------------------------------------------
    SELECT
    Connectors.Noun_1,
    Connectors.Manufacturer AS MFR,
    Connectors.Con_PN AS [MFR P/N],
    Connectors.Monaco_PN AS [Local P/N],
    Connectors.Description AS Notes,
    Connectors.Con_PN
    FROM Connectors
    WHERE (((Connectors.Active) Like "*Yes*"));

    UNION ALL SELECT
    Terminals.Noun_1,
    Terminals.Manufacturer AS MFR,
    Assoc_Term.Term_PN AS [MFR P/N],
    Terminals.Company_PN AS [Local P/N],
    Terminals.Description AS Notes,
    Assoc_Term.Con_PN
    FROM Connectors INNER JOIN (Terminals INNER JOIN Assoc_Term ON Terminals.Term_PN = Assoc_Term.Term_PN) ON Connectors.Con_PN = Assoc_Term.Con_PN
    WHERE (((Connectors.Active) Like "*Yes*"));

    UNION ALL SELECT
    Locks.Noun_1,
    Locks.Manufacturer AS MFR,
    Assoc_Lock.Lock_PN AS [MFR P/N],
    Locks.Company_PN AS [Local P/N],
    Locks.Description AS Notes,
    Assoc_Lock.Con_PN
    FROM Locks INNER JOIN (Connectors INNER JOIN Assoc_Lock ON Connectors.Con_PN = Assoc_Lock.Con_PN) ON Locks.Lock_PN = Assoc_Lock.Lock_PN
    WHERE (((Connectors.Active) Like "*Yes*"));

    UNION ALL SELECT
    Seals.Noun_1,
    Seals.Manufacturer AS MFR,
    Assoc_Seal.Seal_PN AS [MFR P/N],
    Seals.Company_PN AS [Local P/N],
    Seals.Description AS Notes,
    Assoc_Seal.Con_PN
    FROM Seals INNER JOIN (Connectors INNER JOIN Assoc_Seal ON Connectors.Con_PN = Assoc_Seal.Con_PN) ON Seals.Seal_PN = Assoc_Seal.Seal_PN
    WHERE (((Connectors.Active) Like "*Yes*"))
    ORDER BY [Con_PN], [Noun_1];

    --------------------------------------------------------
    Module Code
    ----------------------------------------------------------
    Private Sub Command2_Click()

    Dim MyRecSet As New ADODB.Recordset

    MyRecSet.Open "Acad_Union_Query", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

    MsgBox MyRecSet.RecordCount & " records found"

    MsgBox "BOF is " & MyRecSet.BOF

    MsgBox "EOF is " & MyRecSet.EOF

    MyRecSet.Close

    Set MyRecSet = Nothing

    End Sub

    Thank-you, Bill

  2. #2
    Join Date
    Jul 2008
    Posts
    9
    Still no idea why the above does not work, but I did try the older DAO code (see below) and it pulls all the records...

    Sub Acad_Cons()
    Dim Database As DAO.Database
    Dim MyRecSet As DAO.Recordset

    Set Database = CurrentDb
    Set MyRecSet = Database.OpenRecordset("Acad_Union_Query", dbOpenDynaset)

    MsgBox MyRecSet.RecordCount & " records found"
    MsgBox "BOF is " & MyRecSet.BOF
    MsgBox "EOF is " & MyRecSet.EOF

    MyRecSet.Close
    Set MyRecSet = Nothing

    End Sub

    Thanks, Bill

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just a guess, but I'd try:

    MyRecSet.Open "SELECT * FROM Acad_Union_Query", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

    If you use the code to open a simple query, does it work?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2008
    Posts
    9
    Thanks for the reply StarTrekker,

    Both
    MyRecSet.Open "SELECT * FROM Acad_Union_Query", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    and
    MyRecSet.Open "Acad_Union_Query", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

    will open simple queries with records (BOF and EOF false). Neither of them work with the above Union Query however (BOF and EOF both true). I have found this same question asked in various places on the web, none of them had an answer. Kind of a mystery still...

    Good news is that the older DAO code seems to work fine and the records are coming through in the sort order dictated by the saved union query.

    Thanks again, Bill

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Out with the new and in with the old eh? ^^

    This is another one that would be interesting to learn the reason behind this symptom. I've certainly never experienced anything like it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just a thought but in ADO the wild card characture is a %, but in DAO its * (as in your code).

    Pehaps this is why DAO returns records, but ADO does not ??


    MTB

Posting Permissions

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