Guest User

Untitled

a guest
Aug 18th, 2018
90
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.26 KB | None | 0 0
  1. How to query a MS-Access Table from MS-Excel (2010) using VBA
  2. strFile = "C:UsersbwallDesktopExcel Query Access Testing"
  3.  
  4. Dim cn As Object
  5. Dim rs As Object
  6. Dim strSql As String
  7. Dim strConnection As String
  8. Dim AppPath As String
  9. Set cn = CreateObject("ADODB.Connection")
  10. AppPath = Application.ActiveWorkbook.Path
  11.  
  12.  
  13. strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  14. "Data Source=" & AppPath & "Masterlist_Current_copy.accdb;"
  15. Debug.Print strConnection
  16. strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"
  17. cn.Open strConnection
  18. Set rs = cn.Execute(strSql)
  19. MsgBox rs.Fields(0) & " rows in MyTable"
  20. rs.Close
  21. Set rs = Nothing
  22. cn.Close
  23. Set cn = Nothing
  24.  
  25. End Sub
  26.  
  27. Dim conn As Object
  28. Set conn = CreateObject("ADODB.Connection")
  29.  
  30. Public Sub foo()
  31. Dim cn As Object
  32. Dim rs As Object
  33. Dim strSql As String
  34. Dim strConnection As String
  35. Set cn = CreateObject("ADODB.Connection")
  36. strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  37. "Data Source=C:Accesswebforumswhiteboard2003.mdb"
  38. strSql = "SELECT Count(*) FROM MyTable;"
  39. cn.Open strConnection
  40. Set rs = cn.Execute(strSql)
  41. MsgBox rs.fields(0) & " rows in MyTable"
  42. rs.Close
  43. Set rs = Nothing
  44. cn.Close
  45. Set cn = Nothing
  46. End Sub
Add Comment
Please, Sign In to add comment