The following lets you define your query as a string variable, creates a connection to an Access database you define (with or without username and password), opens a recordset to the open database, returns the result of your query (including headers) to the active sheet in excel, closes and resets the recordset and connection, and autofits the column width.
Insert the code into a module in an Excel workbook of your choice. The red color indicates code you have to adjust to your settings.
CODE
Sub Connect_to_Access_DB_and_execute_SQL_Query()
Dim Conn As New ADODB.Connection Dim RS As New ADODB.Recordset Dim SQL As String Dim MyDB As String Dim R As Long, C As Long Dim ConnStr As String Dim User As String, PW As String ' ' Enter your SQL Query as string, like this example
SQL = "SELECT * FROM MyTable"
' ' Enter path to your Access Database as string, like ' this example
MyDB = "C:\MyDB.mdb"
' ' Enter Username and password to the database ' (empty string ("") if there is no user / pw set)
User = "" PW = ""
' ' Don't mess around with this code, unless you know what ' you are doing ' It defines the connectionstring, and opens a ' connection to the database stated above (MyDB)
' ' This opens a recordset with the query defined as SQL, ' using the connection opened previously
RS.Open SQL, Conn
' ' Inserts headers and data
Do While Not RS.EOF R = R + 1 For C = 1 To RS.Fields.Count If R = 1 Then ActiveSheet.Cells(R, C) = RS.Fields(C - 1).Name Else ActiveSheet.Cells(R, C) = RS.Fields(C - 1).Value End If Next If Not R = 1 Then RS.MoveNext Loop
' ' Closes the database and resets the connection and ' recordset variables
RS.Close Conn.Close Set RS = Nothing Set Conn = Nothing
' ' Autofits the column widths
ActiveSheet.Columns.AutoFit
End Sub
All the variables are defined in order to get an overview of the process for people not used to ADODB connections and recordsets.
The code can easily be adjusted to fit Microsoft SQL Server, by adjusting the connection string (ConnStr).
There are (of course) other ways of achieving the same things, like using .execute on the connection. However, defining a recordset opens up the possibility to adjust the code to fit other purposes.
Talk To Other Members