Advanced Search

Running Access Parameter Queries from Excel

/ Published in: Visual Basic
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

You need to login to view comments.