Getting Access Data into Excel

[davetek]

Hi,

I have a query in Access that returns the results that i need..however i now need to show this data in Excel.
Can anyone briefly explain (sample code even), how i can launch this query through a button in Excel.

Thanks.

 

[AgatheB]

Hi DaveTek,
Try this:

If oRecordSet.RecordCount > 0 Then
ToRowx = ToRow1 + oRecordSet.RecordCount - 1
ToColx = ToCol1 + oRecordSet.Fields.Count - 1

Set Ws = Workbooks(ToWb).Worksheets(ToSh)

Ws.Cells(ToRow1, ToCol1).CopyFromRecordset oRecordSet

End If

 

[ETID]

ODBC...

in a blank excel worksheet, from the menu...

Data>get external data>new database query...select msaccess database from the left tab,...follow the wizard, or select the use MS query option(a scaled down gui similar to access),...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

when the data is returned, you can right click in the data area and set properties such as refresh on open


now when ever the database is updated the workbook will reflect the changes.(if you refresh...auto or manually)