Results 1 to 4 of 4

Thread: Dynamic Access Parameters via Excel

  1. #1

    Dynamic Access Parameters via Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I am trying to create a code that will run an Access 2007 query from within an Excel 2007 spreadsheet and have the spreadsheet supply the dynamic parameters that the Access query needs. I have it working perfectly (see code below) just as long as the parameter is a specific number or a specific date, i.e. not a range (for example >123 or >=123 and <=456). I really need it to work with a range. In the code below, if I supply a fixed number such as 123 in the Excel Spreadsheet in cell L2, the code works fine. If I replace the contents in cell L2 with >123, it crashes with a "Run-time error '3464: Data type mismatch in criteria expression" error message. How can I get it to work with a range of data??? Please Help!!

    Code begins here.....
    Sub RunCNC_Restock()
    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    'Step 2: Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase("C:\Documents and Settings\USER\My Documents\DATABASE.accdb")
    Set MyQueryDef = MyDatabase.QueryDefs("query name")
    'Step 3: Define the Parameters
    With MyQueryDef
    .Parameters("[enter invoice]") = Range("L2").Value
    End With
    'Step 4: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
    'Step 5: Clear previous contents
    Sheets("C & C restock").Select
    ActiveSheet.Range("U20:Z27").ClearContents
    'Step 6: Copy the recordset to Excel
    ActiveSheet.Range("U21").CopyFromRecordset MyRecordset
    'Step 7: Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
    ActiveSheet.Cells(20, i + 20).Value = MyRecordset.Fields(i - 1).Name
    Next i
    'MsgBox "Your Query has been Run"
    End Sub

  2. #2
    Sounds like your query is lacking a where statement . if your query is just looking for all records and you are entering >233 in Cell L2 it is not going to work. Maybe i am not understanding the issue .

    look at sample query with where ststement is looking for quantities larger than 233




    Code:
    SELECT Quote.Company_Name, Quote.Material, Quote.Quantities
    FROM `C:\RFQ.mdb`.Quote Quote
    WHERE (Quote.Quantities>'233')

  3. #3
    nevermind my reply and possible solution ... after i read your post again i see where i misunderstood the issue.

  4. #4
    This might be an option. configure a query with where >[enter invoice start] and <[enter invoice end]
    in the code below there is a new parameter for cell M2



    Code:
    Sub RunCNC_Restock()
    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    'Step 2: Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase("C:\Documents and Settings\USER\My Documents\DATABASE.accdb")
    Set MyQueryDef = MyDatabase.QueryDefs("query name")
    'Step 3: Define the Parameters
    With MyQueryDef
    .Parameters("[enter invoice start]") = Range("L2").Value
    .Parameters("[enter invoice end]") = Range("M2").Value
    End With
    'Step 4: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
    'Step 5: Clear previous contents
    Sheets("C & C restock").Select
    ActiveSheet.Range("U20:Z27").ClearContents
    'Step 6: Copy the recordset to Excel
    ActiveSheet.Range("U21").CopyFromRecordset MyRecordset
    'Step 7: Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
    ActiveSheet.Cells(20, i + 20).Value = MyRecordset.Fields(i - 1).Name
    Next i
    'MsgBox "Your Query has been Run"
    End Sub

Tags for this Thread

Posting Permissions

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