VBA, Access databases and SQL databases

Here i have compiled the most basic example of accessing the data in an Access SQL database.

First i created a table and some information.

Secondly i created a VBA function that accesses this information. For simplicity I have used indexing to msgbox each of the names of the database. This part needs to be changed to something more suiting to your needs. 

Code:

Sub dbAccess()

Dim rs As Object

Dim strSql As String

Dim strConnection As String

Set cn = CreateObject("ADODB.Connection")

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

        "Data Source=C:\Temp\People.accdb"

    strSql = "SELECT * FROM Team;"

    cn.Open strConnection

    Set rs = cn.Execute(strSql)

    

    rs.MoveFirst

    Do

        MsgBox (rs(1))

        rs.MoveNext

    Loop Until rs.EOF

    

End Sub

In Excel:

 This returns what we expect, BRAVO!

---

Related Sections

Python - Learn the python from the basics up. This fast track example code course will get you creating powerful python programs in no time.

---

Must Read Articles

VBA and Microsoft Excel - Getting the most out of excel with VBA programming
VBA and Microsoft Word - Getting the most out of word with VBA programming
Application Object - Using the applications features in your code.
Installing VBA and First Program - What do you need to start using visual basic application VBA.