Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Setting an MS Query as an ADO SQL source

    Is this possible? Take this example:

    Code:
    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    sQRY = "SELECT ID " & _
    "FROM tblData1;"
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    Is it somehow possible to set sQRY to an existing Access Query rather than having to use SQL code?

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Have you tried? It would normally work flawlessly, unless the query is complex (some of the issues might be stored subqueries) or contains parameters (from the top of my head)

    rs.Open "qryMyQuery", cnn, adOpenStatic, adLockReadOnly, adcmdstoredproc
    Roy-Vidar

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Cheers, that worked perfectly.

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Out of interest, how would I specify a table instead of a query?

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Table name

    rs.Open "tblMyTable", cnn, adOpenStatic, adLockReadOnly, adcmdtable

    (or adcmdtabledirect if needed, not available for all providers, but necessary if you're going to do .seek on native Access tables)
    Roy-Vidar

Posting Permissions

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