INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

How can I use VBA to get my Access into Excel? by 01Patrik
Posted: 29 Apr 05

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)

    ConnStr = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & MyDB & ";" & _
    "Persist Security Info=False"
    
    Conn.Open ConnStr, User, PW
  
 '
 '  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.

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close