CODEWITHSUNDEEP
sqlexcelvbams-access
user11091170
user11091170

Reputation:

Returning MS Access information to Excel spreadsheet using SQL

I'm looking to run an SQL query on VBA which will return information from MS Access to cell A1.

At the moment, I'm running the SQL query, SELECT AccountNumber, BorrowerName from AccountTable; and it only returns the account numbers in column A - it doesn't return BorrowerName. Likewise, if I were to run SELECT * from AccountTable; , it will return just the AccountNumber column. Any ideas?

(ignore until Dim conn As New Connection)

'runs an sql query
Sub RunQueryOnAccess()

    'Declaring year value of 1 month & 2 month
    'This is important to compare datasets from 2 months ago & last month
    Year_1M = Format(Date - 27, "YYYY")

    'Declaring month value of 1 month & 2 month
    'This is important to compare datasets from 2 months ago & last month
    Month_1M = Format(Date - 27, "MM")

    'This translates the current month from number to character format
    MonthChar_1 = MonthName(Month_1M, False)

    'opens the workbook before
    'specifiying the file locations
    pStr = "Z:\Danny Tool Test Folder\Monthly Files" & "\" & Year_1M & "\" & _
    Month_1M & ". " & MonthChar_1 & " " & Year_1M & "\"

    otherFile = "Monthly Reporting Tool"
    
    'checking to see that the reporting tool is open, declaring it as MonthlyRepTool
    For Each wb In Application.Workbooks
        If wb.Name Like otherFile & "*" Then
           Set MonthlyRepTool = Workbooks(wb.Name)
        End If
    Next wb
    
    Dim RepDash As String
    
    RepDash = "Reporting Dashboard"

    Dim conn As New Connection, rec As New Recordset
    Dim DBPATH, PRVD, connString, query As String
    'Declaring fully qualified name of database. Change it with your database's location and name.
    DBPATH = "Z:\Danny Tool Test Folder\Database\MasterFile_February2021.accdb"
    'This is the connection provider. Remember this for your interview.
    PRVD = "Microsoft.ace.OLEDB.12.0;"
    'This is the connection string that you will require when opening the the connection.
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH
    'opening the connection
    conn.Open connString
    'the query I want to run on the database.
    query = "SELECT AccountNumber, BorrowerName from AccountTable;"

    
    'running the query on the open connection. It will get all the data in the rec object.
    rec.Open query, conn
    'clearing the content of the cells
    Cells.ClearContents
    'getting data from the recordset if any and printing it in column A of excel sheet.
    If (rec.RecordCount <> 0) Then
        Do While Not rec.EOF
            Range("A" & Cells(Rows.count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
            rec.Fields(0).value
            rec.MoveNext
        Loop
    End If
    
    'closing the connections
    rec.Close
    conn.Close


End Sub

Upvotes: 0

Views: 60

Answers (1)

iDevlop
iDevlop

Reputation: 25252

use this:

Range("A2").CopyFromRecorset rec

This will copy the whole recordset into you sheet, quickly, no loop.

Upvotes: 4

Related Questions