Posted By

CaptainProton on 04/26/11


Tagged

parameter query excel access


Versions (?)

Running Access Parameter Queries from Excel


 / Published in: Visual Basic
 

  1. Sub RunAccessQuery()
  2.  
  3. Dim MyDatabase As DAO.database
  4. Dim MyQueryDef As DAO.QueryDef
  5. Dim MyRecordset As DAO.Recordset
  6. Dim i As Integer
  7.  
  8. Set MyDatabase = DBEngine.OpenDatabase _
  9. ("D:\my Documents\Database2.accdb")
  10. Set MyQueryDef = MyDatabase.QueryDefs("Query3")
  11. With MyQueryDef
  12. .Parameters("[jahrpar]") = Range("A1").Value
  13. End With
  14.  
  15. Set MyRecordset = MyQueryDef.OpenRecordset
  16.  
  17.  
  18. Sheets("Main").Select
  19. ActiveSheet.Range("A6:K10000").ClearContents
  20.  
  21.  
  22. ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
  23.  
  24.  
  25. For i = 1 To MyRecordset.Fields.Count
  26. ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
  27. Next i
  28. End Sub

Report this snippet  

You need to login to post a comment.