command button to run an Acess query- I know this can be done

[chris23892 via OfficeKB.com]

Hi all! Here's a new twist:

I want to create a command button in Excel that will run a query in my
database.

Here's why:

Our data base is not that locked down, I don't want everyone poking around in
this. What I've done is create a query to run the data output that people
want. I already have a command button in my form in our data base to run this,

export it to a nice neat spreadsheet.

What I need is to create a tab in this spreadsheet with a command button that
will run this query in my database so people don'r poke around in the actual
data base.

I'm having issues pointing Excel to the query in VBA. I know this can be done.


Any thoughts?

 

[dmoney]

This should get you started -- just adjust the code to meet your dbase and
assign to a button -- you will need to make sure the microsoft activex data
objects 2.7 reference is selected in the excel vb editor under
tools..references as well.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String


'MAIN PROC
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "\\xxx\xxx\xxx\yyy.mdb", "admin", ""
cn.CursorLocation = adUseClient


strsql = "SELECT [xxx].[xx], [xx].[RxxxEQ_], [yyy].[yyyy] " _
& "FROM [sdfsdf];"
rs.Open strsql, cn

Sheets("xxx").Select


rs.MoveFirst
Do Until rs.EOF = True
ActiveCell = rs![xxx]
ActiveCell.Offset(0, 1) = rs![xxxx]
ActiveCell.Offset(0, 2) = rs![xxxxx]
ActiveCell.Offset(0, 3) = rs![xxxxxxx]
ActiveCell.Offset(1, 0).Activate
rs.MoveNext
Loop

rs.Close

 

[chris23892 via OfficeKB.com]

I got to thinking....I have buttons in acess that have code behind them to
run what I need. I guess I'm wanting to run this code from my excel sheet.
Here's an example:





Private Sub Command18_Click()
Dim sXL As String, oXL As Object
sXL = "\Full Database.xls" 'Full path to my spreadsheet
' Insert the actual name of your query between the quotes below
DoCmd.TransferSpreadsheet acExport, , "Master Export query", sXL
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
oXL.WorkBooks.Open FileName:=sXL
End Sub

This code is assigned to a command buttom in my data base. Works like a charm.
(note this code is in the MS access database)

How would I run this from a command button in a excel spreadsheet?

This should get you started -- just adjust the code to meet your dbase and
assign to a button -- you will need to make sure the microsoft activex data
objects 2.7 reference is selected in the excel vb editor under
tools..references as well.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String

'MAIN PROC
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "\\xxx\xxx\xxx\yyy.mdb", "admin", ""
cn.CursorLocation = adUseClient

strsql = "SELECT [xxx].[xx], [xx].[RxxxEQ_], [yyy].[yyyy] " _
& "FROM [sdfsdf];"
rs.Open strsql, cn

Sheets("xxx").Select

rs.MoveFirst
Do Until rs.EOF = True
ActiveCell = rs![xxx]
ActiveCell.Offset(0, 1) = rs![xxxx]
ActiveCell.Offset(0, 2) = rs![xxxxx]
ActiveCell.Offset(0, 3) = rs![xxxxxxx]
ActiveCell.Offset(1, 0).Activate
rs.MoveNext
Loop

rs.Close

Hi all! Here's a new twist:

[quoted text clipped - 16 lines]

Any thoughts?