How to query access and link it to a sheet in excel

Question

I'm working on a project where and I've hit a road block. I have a table in excel, I'd like to run a query by linking tables in access to a worksheet in the excel and then store the report into an excel sheet. I'm close where I can run access queries and store in excel from tables already existing in access, but I can't figure out how to to do it if one of the tables is instead in my excel sheet. Does anybody have know the vba code to link access tables to an excel worksheet?

I'm referencing the following link which gets me really close but not all the way.

http://www.myengineeringworld.net/2013/10/running-access-queries-from-excel-vba.html http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=173:import-export-data-from-access-to-excel-using-ado&catid=79&Itemid=475

Private Sub CommandButton1_Click()

'--------------
'DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim i As Long, n As Long, lastRow As Long, lFieldCount As Long

'instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

strDBName = "REPORT.MDB"
strMyPath = "C:\Program Files\SETROUTE 9.2.0\DATA"
strDB = strMyPath & "\" & strDBName

'Connect to a data source:
'For pre - MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: "Microsoft.Jet.OLEDB.4.0". For Access 2007 (.accdb database) use the ACE Provider: "Microsoft.ACE.OLEDB.12.0". The ACE Provider can be used for both the Access .mdb & .accdb files.
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB

'--------------
'OPEN RECORDSET, ACCESS RECORDS AND FIELDS

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Cables721")

'Set the ADO Recordset object:
Set adoRecSet = New ADODB.Recordset

'Opening the table named SalesManager:
strTable = "Cables with Incomplete Vias"
adoRecSet.Open "", Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic

'--------------
'COPY RECORDS FROM THE EXCEL WORKSHEET:
'Note: Columns and their order should be the same in both Excel worksheet and in Access database table

lFieldCount = adoRecSet.Fields.Count
'determine last data row in the worksheet:
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

'start copying from second row of worksheet, first row contains field names:

For i = 2 To lastRow
    adoRecSet.AddNew
    For n = 0 To lFieldCount - 1
        adoRecSet.Fields(n).Value = ws.Cells(i, n + 1)
    Next n
    adoRecSet.Update
Next i

'--------------

'close the objects
adoRecSet.Close
connDB.Close

'destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

posted some test I'm working with to try and do what I'm after — WildBanana
Do you have to do the link in code or can you use Linked Table Manager? What version of MS-Access? — SeraM

user4428267 user4428267 · Accepted Answer · 2015-01-07T09:53:40

Try using Connection String :

Sub Button1_Click()
  Dim cn As Object
  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:\Documents and Settings\XXXXXX\My Documents\my_access_table.accdb"
  strSql = "SELECT Count(*) FROM mytable;"
  cn.Open strConnection
  Set rs = cn.Execute(strSql)
  MsgBox rs.Fields(0) & " rows in MyTable"

  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing

End Sub

The Provider piece must be Provider=Microsoft.ACE.OLEDB.12.0 if your target database is ACCDB format. Provider=Microsoft.Jet.OLEDB.4.0 only works for the older MDB format.

Also refer to : How to query a MS-Access Table from MS-Excel (2010) using VBA

Also you can manually add the table as connection from excel - under Data Tab

How to query access and link it to a sheet in excel

1 Answers