How to access ACCESS from Excel!

  • Thread starter Thread starter Gordon Cartwright
  • Start date Start date
G

Gordon Cartwright

Hi

I'm trying to run a macro from Excel that must open
Access, open a table within access, copy the contents of
said table, close Access and paste the information back
into Excel in exactly the same numerical and text format.
But its not recording the bit before the copy command...

A cool beer to the guys who helps me...

Gordon.
 
Gordon,

Set a reference to the Microsoft ActiveX Data Objects library and use code
like the following:


Dim CN As ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim DBName As String
Dim TableName As String
Dim StartCell As Range
'
' Change these there lines to the appropriate values.
'
DBName = "C:\Path\Database.mdb"
TableName = "TableName"
Set StartCell = Range("A1")

Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBName & ";"
Set RecSet = New ADODB.Recordset
RecSet.Open "SELECT * FROM " & TableName, CN
StartCell.CopyFromRecordset RecSet
RecSet.Close
CN.Close



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Back
Top