Code for pulling data from Access to Excel

[dgrimm]

Hello Folks -

I have recently started a new job and they have no MS Access experience so there is nothing to go by here. I am needing code to pull data from query in Access DB in to my excel file. Secondly, I need to know what Tool References I need to check in order for this code to connect and work. I am using Excel 2013 and Access 2013. I have tried code from my previous job but it is crashing. I will put it below.

Public Cn As New ADODB.Connection
Public Const varDefaultPathAccess = "C:\Users\dgrimm\Desktop\HR_Data.accdb"
Public xnn As New ADODB.Connection
Public Const varXNNProvType = "Provider=Microsoft.ACE.OLEDB.12.0"

Sub OpenCN()
If Cn.ConnectionString = "" Then
Cn.Open varXNNProvType & ";Data Source=" & varDefaultPathAccess
End If
End Sub
Sub get_table()

Application.ScreenUpdating = False

Dim i As Long
Dim rstTable As New ADODB.Recordset
Dim TableName As String
Dim VERSION_DATE As String


Sheets("Data").Select
TableName = "Retention_Table"

Range("A2:AZ100000").Select
Selection.ClearContents

OpenCN

rstTable.Open "SELECT * from " & TableName, Cn


Range("A1").CopyFromRecordset rstTable

Cn.Close

Set rstTable = Nothing
Set Cn = Nothing

'Sheets("Report").Select
'Range("A1").Select

End Sub

Thirdly, I don't know if the code would be different for a Query as opposed to a Table in Access as I am trying to pull from a query.

Thank you

Dave

 

[Kenneth Hobson]

Ron de Bruin has several ADO examples. e.g. Copy data from an Access database into Excel with ADO


Here is one way to run a stored query.

'http://msdn.microsoft.com/en-us/library/office/aa188518%28v=office.10%29.aspx
'http://www.vbaexpress.com/forum/showthread.php?t=24118
'http://www.vbaexpress.com/forum/showthread.php?t=24575
'http://www.vbaexpress.com/forum/showthread.php?t=23783
'http://www.vbaexpress.com/forum/showthread.php?t=26145
Sub Test()
  Dim mdbPath As String, dbName As String, cmdText As String
  Dim rngDestination As String
  'mdbPath = "E:\ADO\NWind2003.mdb"    'change the path here to suit your needs
  'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
  mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
  dbName = "NWind2003_1"              'change the database name here to suit your needs
  cmdText = "Aug94"                   'change the stored SQL here to suit your needs
  rngDestination = "A1"               'change the destination range here to suit your needs
  
  'Clear previous data
  Cells.Delete

  InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
  
  'Insert other data to the right of A1 with a blank column separating the two
  rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
  cmdText = "Sales by Category"
  InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

' http://www.vbaexpress.com/forum/showthread.php?t=43307
Sub Test_InsertTableWithStoredSQL()
  Dim databaseName() As Variant, cmdText() As Variant, i As Integer
   '
  databaseName = Array("qOne", "qTwo", "qThree")
  cmdText() = Array("Aug94", "Order Subtotals", "Sales by Category")
       
  For i = LBound(databaseName) To UBound(databaseName)
    Debug.Print databaseName(i), cmdText(i), Range("A" & Rows.Count).End(xlUp).Offset(1).Address
      InsertTableWithStoredSQL "c:\myfiles\edrive\excel\ado\NWind2003.mdb", _
        CStr(databaseName(i)), CStr(cmdText(i)), Range("A" & Rows.Count).End(xlUp).Offset(1).Address, _
        True
  Next i
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
  cmdText As String, rngDestination As String, _
  Optional bFieldNames = True)

    With ActiveSheet.QueryTables.Add(Connection:=Array( _
                                                 "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
       , """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
       , "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
       , "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
       , "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
        .CommandType = xlCmdTable
        .CommandText = Array(cmdText)
        .Name = dbName
        .FieldNames = bFieldNames
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = mdbPath
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[CalcSux78]

Dave,

I think the reference you're looking for is "Microsoft ActiveX Data Objexts X.X Library". I usually use the 6.1 version.


There is tons of great info out there on using Access & Excel together. Try a few of the links:
Globaliconnect.com
W3Schools.com
Erlandsen Data

 

[dgrimm]

Thank you to both of you for your help.