Query my Access 2007 Query in Excel 2007 VBA 2

[tammy23]

Working in Access 2007 and Excel 2007....

I have an .accdb (WellCardPilot.accdb) that pulls information from multiple tables (some linked, some built into the DB). The following code works fine to return all records from a query called BridgeportWellCard to a specific worksheet in Excel (Sheet 2). In this case its 50 unique records. I would like to be able to Query the results of the Access Query and have only 1 record returned based on a value that I type into 1) a cell, 2) a pull down box, 3) a message box, etc....I don't really care as long as I can enter a value somewhere, run the query, and return 1 record instead of 50. Here's my code...I know I will have to change when it executes from "open workbook" to something else...I'm more concerned with how to query my query results I suspect I can do something with the red line of code but just not sure..any help is appreciated!!

Private Sub Workbook_Open()

Dim TARGET_DB As String
Dim cnn As ADODB.connection
Dim rcs As ADODB.Recordset
Dim mQry As String
'mQry = "select * from [DB$]"
TARGET_DB = "WellCardPilot.accdb"
mQry = "BridgeportWellCard"

Set cnn = New ADODB.connection
Set rcs = New ADODB.Recordset

'create the connection to the database
myconn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open myconn
End With

With rcs
.Open mQry, cnn
End With

MsgBox rcs.Fields.Count

With Sheet2
.Activate
Range("A2", ["XFD1048376"]).Clear
MsgBox ("Cool")
Cells(2, 1).CopyFromRecordset rcs
End With

rcs.Close
cnn.Close

End Sub

 

[FractalWalk]

Is the code to reside in Excel? If so, you can use an input box to identify the value you want:

response = InputBox("Enter Value Here", "Value", empty)

And are you saying you have already returned the 50 and now want to idenitfy a single record from the table? Or are you saying you want to just return one value from Access to start with? If the former then you can read the data into an array and read each record.

mQry = Sheets("Sheet2").Cells(2, 1).CurrentRegion

If the latter then I am guessing that mQry wouold already be an array that you could read and you would just need to loop through each record. I don't know what your data looks like so I can't be specific here but maybe:

For a = 1 To UBound(mQry)
    'this assumes the matching field is in the first field
    If mQry(a, 1) = response Then
        'Do whatever it is you want to do here
        Exit For
    End If
    Next b
Next a

 

[tammy23]

Thanks FractalWalk!!...I'll try both methods and let you know how it goes...honestly I don't care if I get 50 and then query that 50 in Excel or if I query Access and only get one in Excel as long as I can eventually get it down to one record based on a number entered and then hitting "Enter" will execute the code...thanks for the reply

 

[SkipVought]

If your table is small enough, i'd just query the entire table and use a lookup formula to return a value

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tammy23]

Thanks SkipVought...its not that big of a table...I'll probably just try a lookup like you suggested thanks for the reply!! don't know why I thought it would be a good idea to start this thread on a Friday but I will definitely post the solution (if I can figure it out) when I get back to the office Monday thanks so much!!

 

[tammy23]

I got the code below to work (resides in Excel 2007)...what it does is when I open my workbook, it asks me if I'm ready and then automatically opens the connection to my Access Database, runs an Access query, pulls that information into Sheet 2 of my workbook, and then queries the data (within the wokrsheet) based on a value I put into a msgbox. Its a little cumbersome in that it places the Access recordset into Excel sheet2 starting at the 3rd row, copies the row I'm looking for into the 2nd row, and then deletes everything I just imported....but this way I can keep my 1st row as a header and leaves only the data I want in the 2nd row...thanks for all your help!!

Private Sub Workbook_Open()
              
    Dim Response1 As VbMsgBoxResult
    Response1 = MsgBox("Ready to Update the Data?", vbQuestion + vbYesNo)
    If Response1 = vbNo Then Exit Sub
    If Response1 = vbYes Then
        
        Dim TARGET_DB As String
        Dim cnn As ADODB.connection
        Dim rcs As ADODB.Recordset
        Dim acQry As String
        Dim a As Double
                
        a = 3
        
        TARGET_DB = "WellCardPilot.accdb"
        acQry = "BridgeportWellCard"
                
        Set cnn = New ADODB.connection
        Set rcs = New ADODB.Recordset
           
        'create the connection to the database
        myconn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
        'Use this connection if Wksht & DB are in Different folders
        'myconn = "\\clknetapp2\gasslog_doc\WellCardPilot" & Application.PathSeparator & TARGET_DB
        
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open myconn
        End With
        
        'Open and Run Query
        With rcs
            .Open acQry, cnn
        End With
  
        'Copy Recordset to Excel and Query based on WellID
        With Sheet2
            .Activate
            Range("A2", ["XFD1048376"]).Clear
            Cells(a, 1).CopyFromRecordset rcs
            Dim Response As VbMsgBoxResult
            Response = InputBox("What Well Number Are You Looking For?", "Value", Empty)
            Do
            If Response = 0 Then
            End If
            If Response = Cells(a, 1) Then
            Cells(a, 1).EntireRow.Copy
            Cells(2, 1).PasteSpecial
            End If
            a = a + 1
            Loop Until a = 100
            Range("A3", ["XFD1048376"]).Clear
        End With
        
        With Sheet1
        .Activate
        End With
                
        'Close Query and Database
        rcs.Close
        cnn.Close
    
    End If

End Sub