CODEWITHSUNDEEP
ms-accessvba
steven Sharrard
steven Sharrard

Reputation: 1

Paremeter Query from Access 2007 to Excell 2007 in VBA

I am very new to VBA and what I am trying to do is call a Parameter Query from a PM scheduling Database, based on two factors Airport and Dept. I found some code in another forum that does exactly what I want, and will allow other users to use a spreadsheet to call their specific schedules without having to log into my database (I really don't want others in the DB). However I can't quite get it to work and I think it has to do with the fact that I am using Access and Excel 2007.

I set up this spreadsheet us like this article states to do:

http://datapigtechnologies.com/blog/index.php/running-an-access-parameter-query-from-excel/

Then I input this code

'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 _
    ("W:\Program Management Databases\PM Master schedule\PM Master Database.accdb")
    Set MyQueryDef = MyDatabase.QueryDefs("PM Schedule Parameter Query")

    'Step 3: Define the Parameters


     With MyQueryDef
    .Parameters("[Enter Segment]") = Range("D3").Value
    .Parameters("[Enter Region]") = Range("D4").Value


    End With

    'Step 4: Open the query

    Set MyRecordset = MyQueryDef.OpenRecordset


    'Step 5: Clear previous contents
    Sheets("Main").Select
    ActiveSheet.Range("A6:K10000").ClearContents

   'Step 6: Copy the recordset to Excel

    ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

   'Step 7: Add column heading names to the spreadsheet


    For i = 1 To MyRecordset.Fields.Count
    ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
    MsgBox "Your Query has been Run"

    End Sub

However I get an error with user defined error on the first Dim line. I read all the questions on the post referenced above and everyone says it works, but I think it has to do with me using 2007 Excel and Access and the reference library is different.

This seems to at least the first problem:

Dim MyDatabase As DAO.Database

Upvotes: 0

Views: 49

Answers (1)

Sergey
Sergey

Reputation: 243

Excel doesn't have references to DAO library by default. Go to VBA Editor, Tools > References and put flag on Microsoft DAO 3.6 Object Library.

Upvotes: 0

Related Questions