Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How do I connect to a query with ADODB?

    I know how to connect to a table but how do I do it for a query. I want to connect to the query and run the query to get the information I needed from the query.

    Here is my connection to the table with ADODB.

    Dim conndb as ADODB.Connection
    Dim rsExportData as ADODB.Connection

    Set conndb = CurrentProject.Connection
    Set rsExportData = New ADODB.Recordset

    rsExportData.Open "Export_Date", conndb, adOpenKeyset, adLockPessimistic, adCmdTable

    Thanks in advance.

    Don

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    What you have should work, although you don't need the accmdTable.

    If you can't see the query as an otcome of the recordset it may contain criteria based on a form. This is not allowed in ADO.

    If this is the case you can create a function in a module to return the criteria instead.

    HTH

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    Thanks, I got it to work. Now I need to retrieve the information from the Access query(not hardcode written query). How do going about doing that?

    I try by

    Example:

    Private Sub Command0_Click()
    Dim conndb As ADODB.Connection
    Dim rsmwcas As ADODB.Recordset
    Dim rsCaseNumber As ADODB.Recordset
    Dim MaxCaseNumber As String

    Set conndb = CurrentProject.Connection
    Set rsmwcas = New ADODB.Recordset
    Set rsCaseNumber = New ADODB.Recordset

    rsmwcas.Open "mwcas", conndb, adOpenKeyset, adLockPessimistic, adCmdTable
    rsCaseNumber.Open "[qryGet CaseNumber]", conndb

    having a problem right here ----->MaxCaseNumber = rsCaseNumber("Case Number")

    End Sub

    Table:mwcas
    Fields: Chart Number, Case Number

    Access SQL Queries:qryGet CaseNumber
    SELECT mwcas.ChartNumber, Max(mwcas.[Case Number]) AS [MaxOfCase Number]
    FROM mwcas
    GROUP BY mwcas.ChartNumber;

    Last edited by lansing; 06-27-03 at 02:43.

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    Try

    rsCaseNumber.Fields("Case Number")

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    Change of thought, I just find out that I can't use the query I created in Access. I need to hard code it in vba. I have try the code below and can't seem to run the query string I created in vba. I also need to retrieve the query result as I mention above post but in a different way. Is my synatx correct for the query string?

    Private Sub Command0_Click()
    Dim conndb As ADODB.Connection
    Dim rsmwcas As ADODB.Recordset
    Dim rsmwpat as ADODB.Recordset
    Dim rsCaseNumber As ADODB.Recordset
    Dim MaxCaseNumber As String

    Set conndb = CurrentProject.Connection
    Set rsmwcas = New ADODB.Recordset
    Set rsCaseNumber = New ADODB.Recordset
    Set rsmwpat = New ADODB.Recordset

    rsmwcas.Open "mwcas", conndb, adOpenKeyset, adLockPessimistic, adCmdTable

    rsmwpat.open "mwpat", conndb, adopenkeyset, adlockpessemistic, adcmdtable

    rsmwpat.Find "[Social Security Number] = '" & mudtICD9(intIndex).strPatientID & "'"

    PatChart = rsmwpat("Chart Number")

    strSQL = "Select [Case Number] From rsmwcas Where [Chart Number] = "' & PatChart & "';"
    docmd.runsql strSQL, True

    MaxCaseNumber = rsmwcas.Fields("Case Number")

    End Sub


    Thank you dynamictiger for helping me out. I greatly thankful for it.

    I get an error "A RunSQL action requires an argument consisting an SQL statement." when I use the docmd.runsql strSQL, True


    Before I have try it in a different way, I try your method above:
    MaxCaseNumber = rsmwcas.Fields("Case Number") and I get an error as: "Item cannot be found in the collection corresponding to the requested name or ordinal."
    Last edited by lansing; 06-27-03 at 12:33.

  6. #6
    Join Date
    Apr 2003
    Posts
    280
    Ok, I got it to work with this code below:

    strSQL = "Select * From mwcas Where [Chart Number] = '" & PatChart & "';"

    rsmaxcase.Open strSQL, conndb

    MaxCaseNumber = rsmaxcase("Case Number")

    But I need to get the Max or Highest number for the case number field. I have try this and got an error for it: "Item can't be found in the cannot be found in the collection corresponding to the requested name or ordinal."

    strSQL = "Select Max([Case Number]) as MaxCaseNumber From mwcas Where [Chart Number] = '" & PatChart & "';"

    rsmaxcase.Open strSQL, conndb

    MaxCaseNumber = rsmaxcase("Case Number")

  7. #7
    Join Date
    Apr 2003
    Posts
    280
    Ok, I'm getting closer now. I was able to retrieve the high number from the query. When I try to filter it out, it can't the value in the table. The table with the case number is an autoincrement field. When I filter it in the Access table, it would show up a record for it but when I do a filter in vba, it can't find the record for the value I obtain from the previous coding.

    When I do a query in Access, it would show one record with a case number of 28 and the other is 38 but when I do a filter in the Access table for that particular person it would show the case number 28 for both records. Why is that? I'm confused.

  8. #8
    Join Date
    Apr 2003
    Posts
    280
    ok, I got data type mismatched with this code. Why?

    Dim MaxCaseNumber as Integer

    strSQL2 = "Select * From mwcas Where [Case Number] = '" & MaxCaseNumber & "';"
    rsmaxcase2.Open strSQL2, conndb

    Case Number field is an autocrement data type

  9. #9
    Join Date
    Feb 2002
    Posts
    403
    You don't need single aposthrephes when working with numbers only. They are only used for strings.

    So

    strSQL2="SELECT * FROM mwcas WHERE [Case Number] = " & MaxCaseNumber & ";"

    Should not give you a type mismatch.

Posting Permissions

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