User Form Cmd Button in Excel to run Access macro

I have a mdb that has a macro that transfers query results to excel. I would
like to use a UserForm in excel VB for a user to click a command button and
run the macro that is in Access. Is this possible and if so, what would the
code look like.

FSt1
Tue Jul 22 17:04:01 CDT 2008

hi
yes it is. but.....
I think that, from excel, you would be better off using MSQ(MicroSoftQuery)
than trying to do it the other way round.
here's the code

dim dbs as database
dim wrkjet as workspace
fname = "filename of database plus full path"
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase(fname, True)
sql = "copy code from your query builder in Access"
dbs.execute sql
dbs.close

I got this code from access newgroups. i do not know the author and
apoligize for not being able to name the author. i used it once then went
back to MSQ. seems stupid to write a query in access then write a query in
excel to run the access query. skip the access query-do the MSQ. less code.
less typing. quicker??? opions vary i hear.

my thoughts
regards
FSt1

"D" wrote:

> I have a mdb that has a macro that transfers query results to excel. I would
> like to use a UserForm in excel VB for a user to click a command button and
> run the macro that is in Access. Is this possible and if so, what would the
> code look like.

Peter
Tue Jul 22 17:12:27 CDT 2008

Look at "Run" in help
In Excel, assumes one instance of Access running (warning aircode !)

Sub Test
Dim sMacro as String
Dim oA as Object

On error Resume next
Set oA = GetObject(, "access.application")
' or if you know the file name
' Set oA = GetObject("theDB.db1").Parent

On error goto 0
If oA is nothing then
msgbox "Can't attach to Access"
Else
sMacro = "myAccessMacro"
' or maybe say
' sMacro = "db1.myAccessMacro"

oA.Run sMacro
End if

End Sub

Regards,
Peter T

"D" <D@discussions.microsoft.com> wrote in message
news:7901B2ED-D7BC-47D6-ADCD-BC0450520D7E@microsoft.com...
> I have a mdb that has a macro that transfers query results to excel. I
would
> like to use a UserForm in excel VB for a user to click a command button
and
> run the macro that is in Access. Is this possible and if so, what would
the
> code look like.

gary
Tue Jul 22 17:12:04 CDT 2008

This should give you a decent start...

'/===========================================/
' Sub Purpose: Run macro in Microsoft Access
'
' ***A reference to the 'Microsoft Access xx.x Library'
' Library is required
' ***A reference to the 'Microsoft DOA x.x Object Library
' Library is required
'/===========================================/
'
Public Sub Run_Access_Macro()
Dim AccApp As Access.Application
Dim dbD As DAO.Database

'open Microsoft Access
Set AccApp = CreateObject("Access.Application")

'open the desired database
AccApp.OpenCurrentDatabase _
"C:\Temp\db2.mdb"

'run the desired macro
AccApp.DoCmd.RunMacro "Macro1"

'give Microsoft Access time to load and run
DoEvents

'get out of Microsoft Access
AccApp.Quit

exit_Sub:
On Error Resume Next
Set AccApp = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Run_Access_Macro - " & Now()
GoTo exit_Sub

End Sub
'/===========================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"D" wrote:

> I have a mdb that has a macro that transfers query results to excel. I would
> like to use a UserForm in excel VB for a user to click a command button and
> run the macro that is in Access. Is this possible and if so, what would the
> code look like.