Question about linking Access to Excel.

[matrix7410]

Hello,
I have a access database that stores all the tables and the queries
that I want to dump on an excel sheet. On the excel sheet, I want to
create a button call Refresh Data & Calculate Report, so whenever the
user opens the excel sheet and clicks on the Refrest button, the new
data that has been stored in Access gets feed into the Excel. How do
I set up that button in Excel and how do I create that link between
Access and Excel?

thanks!

 

[FSt1]

hi
the easiest way would be to create a database query.
on the menu bar....
data>get external data>new database query>follow the wizard.

for you button, say your put the query at sheet1:A1,
Private Sub CommandButton1_Click()
sheets("sheet1").range("A1").querytable.refresh Backgroundquery:=false
End Sub

once refreshed, you can add code to manipulate the imported data if needed.

there are other ways to do this, this is about the simpliest.
Regards
FSt1

 

[matrix7410]

hi
the easiest way would be to create a database query.
on the menu bar....
data>get external data>new database query>follow the wizard.

for you button, say your put the query at sheet1:A1,
Private Sub CommandButton1_Click()
sheets("sheet1").range("A1").querytable.refresh Backgroundquery:=false
End Sub

once refreshed, you can add code to manipulate the imported data if needed.

there are other ways to do this, this is about the simpliest.
Regards
FSt1






- Show quoted text -

Thank you!

 

[Mike]

You could also use adodb. Here is an example of copyfromrecordset.
Option Explicit
Sub getData()
'Needs reference the Axtive X Library 2.0 or higher
Const shName As String = "Sheet1"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String, sProvider, sDataSource, strConn
Dim iCol As Long

Dim wks As Worksheet

Set wks = Worksheets(shName)
'Clear sheet before refresh
wks.Cells.ClearContents
'use ACE for 2007 mdb or less
'sProvider = "Provider=Microsoft.ACE.OLEDB.4.0; "
'use Jet for 2003 mdb or less
sProvider = "Provider=Microsoft.Jet.OLEDB.4.0; "
'change data source with the path to your database
sDataSource = "Data Source=C:\ilsa\data\Ilsa.mdb;Persist Security
Info=False"

strConn = sProvider & sDataSource
'sSQL = "Replace with your query"
sSQL = "SELECT AdjustLog.* FROM AdjustLog;"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic

iCol = 1
For Each fld In rs.Fields
wks.Cells(1, iCol) = fld.Name
iCol = iCol + 1
Next
wks.Range("A2").CopyFromRecordset rs

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub