Update Access Query and Return Results to excel

[stapuff]

From Excel button click I would like to open the following path -S:Production Control\Information Requests\Range Select.mdb

Update the following query fields using the activeworkbooks ranges - Query1
Field [Warehouse] = sheet1 range B2 value
Field [Part Number] = sheet1 rangeB3 value
Field [Program Code] = sheet1 rangeB4 value
Field [Transaction Date] = sheet1 rangeB5 value

Run the Query
Copy the results
paste the results on sheet1 range A8

Any thoughts on how this could be done.

Thanks,

Kurt

[Ken Puls]

Hi Kurt,

Do you know how to write the SQL? I tend to do mine in the Access Query screen till I get it the way I want.

Once you have your SQL working, then check out this method for retrieving it to Excel.

HTH,

[stapuff]

Hey Ken......... been awhile.

To answer the Q simply - No. I have about 15 minutes of experiance in Access. The database is actually in a shared directory. I have been doing some searching and I felt quite overwhelmed (lacking Access) so I thought I would post a question for help.

[Ken Puls]

Quite a while.

Okay... first thing we'll need to do is work out your query. You have access to the Access database, right? Can you tell me what the field names are that hold the data you referenced above? And what tables they are in?

You'll need to go into Access and open up the tables. Look through them till you find the data that resembles what you want. I need the EXACT field names, and the corresponding names of the tables that the fields are in.

Also, what is the criteria for the data that you want to retrieve? Between a certain date that you provide? Certain part number?

[XLGibbs]

YOu can write the query in MS Query, and you can then set up your paramaters to filter the query based on those cell entries.

It sounds like you want to simply change the criteria being used to filter the query to what your cells are containing. Once you get that data you want to store it elsewhere in excel. This also is pretty easy.

Once the MS Query is written to filter the data set for the 4 fields you indicate, your VB Code can simply loop through refreshing the data, copying the results to your desired location. YOu could also have it so you are prompted for each parameter....

[stapuff]

Ken -

I have a lot of success with your code since Friday. This is what I did.

On sheet1 I put a button with the following code attached:

Sub Button3_Click()
'Macro Purpose: To retrieve a recordset to an Excel worksheet
    Dim sSQLQry As String
    Dim rngTarget As Range
    Range("A8:I20000").ClearContents
    'Generate the SQL query and set the range to place the data in
    sSQLQry = "SELECT PRDDTAY2K_OIVF111A.WAREHOUSE, PRDDTAY2K_OIVF111A.PART_NUMBER, " & _
            "PRDDTAY2K_OIVF111A.PROGRAM_CODE, PRDDTAY2K_OIVF111A.PART_DESCRIPTION, " & _
            "PRDDTAY2K_OIVF111A.TRANSACTION_DATE, PRDDTAY2K_OIVF111A.ORDER_NUMBER, " & _
            "PRDDTAY2K_OIVF111A.PO_PRICE, PRDDTAY2K_OIVF111A.INVENTORY_UM_PRICE, " & _
            "PRDDTAY2K_OIVF111A.RECEIVED_QTY_IN_SUOM FROM PRDDTAY2K_OIVF111A WHERE " & _
            "(((PRDDTAY2K_OIVF111A.WAREHOUSE)='" & Sheet1.Range("B2") & "') AND " & _
            "((PRDDTAY2K_OIVF111A.PART_NUMBER)='" & Sheet1.Range("B3") & "') AND " & _
            "((PRDDTAY2K_OIVF111A.PROGRAM_CODE)='" & Sheet1.Range("B5") & "') AND " & _
            "((PRDDTAY2K_OIVF111A.TRANSACTION_DATE)>='" & Sheet1.Range("B4") & "'));"
    Set rngTarget = ActiveSheet.Range("A8")
    'Retrieve the records
    Call RetrieveRecordset(sSQLQry, rngTarget)
End Sub

In a module I put the following code:

Option Explicit
'Constant for Database connection string
Private Const glob_DBPath = "S:\Production Control\Information Requests\PU15 Requested Info.mdb"
Private Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_DBPath & ";"
Private Sub RetrieveRecordset(strSQL As String, clTrgt As Range)
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To retrieve a recordset from a database (via an SQL query) and place
'               it in the supplied worksheet range
'NOTE         : Requires a reference to "Microsoft ActiveX Data Objects 2.x Library"
'               (Developed with reference to version 2.0 of the above)
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rcArray As Variant
    Dim lFields As Long
    Dim lRecrds As Long
    Dim lCol As Long
    Dim lRow As Long
    'Open connection to the database
    cnt.Open glob_sConnect
    'Open recordset based on Orders table
    rst.Open strSQL, cnt
    'Count the number of fields to place in the worksheet
    lFields = rst.Fields.Count
    'Check version of Excel
    If Val(Mid(Application.Version, 1, InStr(1, Application.Version, ".") - 1)) > 8 Then
        'EXCEL 2000 or 2002: Use CopyFromRecordset
        'Copy the recordset from the database
        On Error Resume Next
        clTrgt.CopyFromRecordset rst
        
        'CopyFromRecordset will fail if the recordset contains an OLE
        'object field or array data such as hierarchical recordsets
        If Err.Number <> 0 Then GoTo EarlyExit
    
    Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel
        'Copy recordset to an array
        rcArray = rst.GetRows
        'Determine number of records (adds 1 since 0 based array)
        lRecrds = UBound(rcArray, 2) + 1
        'Check the array for contents that are not valid when
        'copying the array to an Excel worksheet
        For lCol = 0 To lFields - 1
            For lRow = 0 To lRecrds - 1
                'Take care of Date fields
                If IsDate(rcArray(lCol, lRow)) Then
                    rcArray(lCol, lRow) = Format(rcArray(lCol, lRow))
                    'Take care of OLE object fields or array fields
                ElseIf IsArray(rcArray(lCol, lRow)) Then
                    rcArray(lCol, lRow) = "Array Field"
                End If
            Next lRow
        Next lCol
        'Transpose and place the array in the worksheet
        clTrgt.Resize(lRecrds, lFields).Value = TransposeDim(rcArray)
    End If
EarlyExit:
    'Close and release the ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0
End Sub
Private Function TransposeDim(v As Variant) As Variant
'Function Purpose:  Transpose a 0-based array (v)
    Dim x As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant
    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)
    ReDim tempArray(Xupper, Yupper)
    For x = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(x, Y) = v(Y, x)
        Next Y
    Next x
    TransposeDim = tempArray
End Function

Everything is working out perfectly, however, not exactly as I need.

How can I use wild cards with this code?
Example -

B2 (which is Warehouse) is left blank
B3 (which is Part #) is ABCDE
B4 (which is starting Date) is 2006-10-01
B5 (which is program code) is PU15

I would expect all ABCDE part #'s from all warehouses starting 10/1/2006 to current under program code PU15 to show up in Excel. I am getting nothing.

Another thing I have not figured out yet is in B4 I have a starting date and no end date which will always return from Starting Date to Current Date. What if I want to return a specific date range?

Your thoughts would be greatly appreciated.

Again - I thank you for what you have given me thus far.

[Ken Puls]

Hi Kurt,

You've got your query working in the Access Query part, yes? Just take your values from your cells, and manually enter those in as the parameters in Access to see your results. Test and modify your SQL until you get it right. Once you've done that, then paste it back here so we can help you integrate the Excel portion in.