CODEWITHSUNDEEP
excelms-accessvba
Ashley Y
Ashley Y

Reputation: 1

linking an access database to excel (without importing the whole table)

Is there any way to link tables in an Access database to Excel without importing the entire table? I need to reference/lookup cells in the Access table but don't want to import the whole table into the excel workbook (tables are too big).

My second option is to export the Access tables into a separate excel workbook, then just reference this new workbook instead of the Access database itself. When I try to do this only around 65,000 rows of data from any Access table actually export to Excel, as the rest 'couldn't be copied to the clipboard'. Is there a simple way around this? (I want to actually have a connection between the excel/access files, so the data can be refreshed, not just copy and paste the rows over)

Upvotes: 0

Views: 8116

Answers (3)

user3103059
user3103059

Reputation:

You can work with Access database tables from Excel without importing the table into an Excel worksheet:

Dim cnn As ADODB.Connection  ' Needs a reference to the Microsoft ActiveX                         
Dim rs  As ADODB.Recordset   ' Data Objects Library

Set cnn = CreateObject("ADODB.Connection") 
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=database-path\Data.accdb;"
Set rs = cnn.Execute("SELECT * FROM MyTable")
While Not rs.EOF
  Debug.Print rs(1)
  rs.MoveNext
Wend

You may need the Microsoft Access Database Engine 2010 Redistributable which you should install with the /passive option if you are using a x86 Access version on a x64 OS.

Upvotes: 0

ASH
ASH

Reputation: 20342

Try the following script. This should give you what you want.

Option Compare Database

Private Sub Command0_Click()

Dim InputFile As String
Dim InputPath As String

InputPath = "C:\your_path_here\Desktop\"
InputFile = Dir(InputPath & "*.xlsx")

Do While InputFile <> ""

    DoCmd.TransferSpreadsheet acLink, , InputFile, InputPath & InputFile, True '< The true is for column headers
    InputFile = Dir
Loop


End Sub

Upvotes: -1

jkpieterse
jkpieterse

Reputation: 2986

See this ancient article, which should help you get the data you actually need rather than everything:

http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

Upvotes: 1

Related Questions