Trigger a Macro in MS Access from an Excel Macro?

[DonRetd]

Can an existing Macro in MS Access be triggered from an Excel Macro?
I am using Windows 98SE and Office 2000 (Excel and Access 2000).

I tried recording an Excel Macro to open up an Access data base to trigger
an existing Macro in Access. I got a message directing me to the Data drop
down menu to do a query to get data. I just want to run the Macro, not do a
query.

Can I do what I hope to do or is it not possible?
If it can be done, can you guide me to a sample of the proper code syntax?

--
IHSWRKER

[Nate Oliver]

Hello, the following worked for me:

Sub yadda()
Dim accApp As Object
Set accApp = GetObject(, "Access.Application")
AppActivate "Microsoft Access"
accApp.Run "foobar"
Set accApp = Nothing
End Sub

There's another example and more on the Run Method in the Access VBE help
file.

Regards,
Nate Oliver

[DonRetd]

Hey thanks for your response.
I assume foobar would be the name of the Macro!?
I will give it a try as soon as I can get back to our church office. I'll
let you know what happens.

[Nate Oliver]

You are welcome.

Yes, in my example, foobar was the name of the Access VBA procedure (not an
Access Macro per se). I meant to ask you about that...

Good luck, if you need more help, post back.

Regards,
Nate Oliver

[DonRetd]

Hi Nate, I tried using the code as shown below:
I used your model and filled in what I thought ws intended. Although I have
been trying to interpret the syntax, I am not doing well understanding what
is required.

The 4th line below failed (AppActivate . . .) and caused a message "Invalid
procedure call or argument".
Any suggestions?

Sub Macro1()
Dim accApp As Object
Set accApp = GetObject("C:\Shared\Shared T&P\T&PFCC.mdb")
AppActivate "Microsoft Access"
accApp.Run "Macro1"

Set accApp = Nothing
End Sub

[Nate Oliver]

Hello again Don,

There's a difference between calling a VBA procedure in Access and a Macro
in Access, they're two different things. To call a macro, see the following
background info:

http://msdn.microsoft.com/library/en-us/vbaac11/html/acmthactRunMacro_HV05186508.asp

And the following example:

Sub Macro1()
Dim accApp As Object

Set accApp = GetObject(, "Access.Application")
AppActivate "Microsoft Access"

accApp.docmd.runmacro "Macro1"

Set accApp = Nothing
End Sub

Regards,
Nate Oliver

[DonRetd]

Hi Nate,

Thanks again for your reply. I thought you gave up on me.
Again, I'll try the RunMacro code you suggested, as soon as I can get back
to the church. I'll let you know what I find.
I appreciate you staying with me!

Peace,
Don

[Nate Oliver]

Hello Don,

You're welcome.

Sorry, I access this forum via:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming

And it doesn't always sort (in most cases it does not) by last post, so I
missed it.

Also note, the only reason I was using:

AppActivate "Microsoft Access

Is because I was using a Msgbox in my test and I wanted to see it, you
should not have to do this.

Regards,
Nate Oliver

[DonRetd]

Hello Nate,
Thanks for the information.
I am just thankfull that you are trying to help me. That's a big job since I
have not really worked with Access or VBA coding before.
I ran the following code:

Sub Macro1()

Dim accApp As Object

Set accApp = GetObject("C:\Shared\Shared T&P\T&PFCC.mdb")

accApp.docmd.runmacro "Macro1"
Set accApp = Nothing
End Sub

I ran the above code and got the following message.

Run time error "2501"
The RunMacro action was cancelled.

The RunMacro line was highlighted.
Using F1 brought up a "Keyword not found" message.
I stopped there, since I really don't understand what is wrong.

One other question, on the "GetObject" line, if the run did not fail on that
line, can I assume the path to the database syntax is correct? I am not sure
if the .mdb extension should be included in the database name.

Thank you once again for your patience,
Don

[Nate Oliver]

Hello again Don,

Do you mind my asking what your macro does? I can see what it doesn't do:

> I just want to run the Macro, not do a query.

The reason I ask is that your App might be more stable and maintainable if
you keep your process in-house, in Excel. You can automate Access from Excel.

Regards,
Nate Oliver

[DonRetd]

Hi Nate,

I don't mind your asking, at all.
Frankly I am more comfortable with Excel (not VBA or Access).
The reason I made the comment (I just want to run the Macro, not do a
query.) is that I had already created a simple Access query, follow on report
and Macro.
The Macro is just 2 steps, "Outputto" and "Quit". It outputs the report data
to an Excel file.
Since you asked, the only reason I go to Access is to use the report feature
"hide repeating data". The report sort is by Day and Date and shows scheduled
events in our church. The first event of each day shows the Day and Date and
doesn't repeat for the other events of each day. The new Day and Date are
much easier to identify.

You can see the results at: http://www.firstchristianstow.org/ and select
"Monthly Calendar" (Left side of screen).

If you tell me this could be done in Excel, that would be the greatest. I
would not go to Access at all.
Logically, I believe it could, but I am not sure if there are formulas
and/or functions capability in Excel, that would allow doing it.
If you know it can be done and point me in the direction of what can be used
to do it, I am definitly open to try it.

Thanks once more for your help,
Don

[Nate Oliver]

Hello again Don,

You should be able to do this with VBA, I'll provide a couple of examples.
The first one is make a copy of Recordset, e.g.,
http://www.able-consulting.com/ADO_Faq.htm#Q42

For this you'll need a reference to Microsoft ActiveX Data Objects 2.5+
Library (my system jumps from 2.1 to 2.5 and ADODB.Stream isn't supported in
2.1, not sure when it was introduced):

'--------------
Sub GrbAccessData1()
Dim cn As ADODB.Connection
Dim oRs1 As ADODB.Recordset
Dim oStm As ADODB.Stream
Dim oRs2 As ADODB.Recordset
Dim lstField As Date
Dim I As Integer

Const dbFullname As String = "P:\DATA\test.mdb"

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbFullname & ";"

Set oRs1 = New ADODB.Recordset
With oRs1
.Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _
"From [Events] Where Month([Date_F])=3 Order By [DATE_F]", _
cn, adOpenStatic, adLockReadOnly
Set oStm = New ADODB.Stream
oStm.Open
.Save oStm, adPersistXML
.Close
End With

Set oRs1 = Nothing
cn.Close: Set cn = Nothing

Set oRs2 = New ADODB.Recordset
With oRs2
.Open oStm, , , adLockOptimistic
oStm.Close: Set oStm = Nothing
If Not .EOF Then
Let lstField = .Fields(1).Value
.MoveNext
For I = 2 To .RecordCount
If .Fields(1).Value = lstField Then
.Fields(0).Value = Null
.Fields(1).Value = Null
Else: lstField = .Fields(1).Value
End If
.MoveNext
Next
.MoveFirst: .Update
End If
End With

Sheets(1).Range("a1").CopyFromRecordset oRs2
oRs2.Close: Set oRs2 = Nothing
End Sub
'--------------

You'd want to change your DB filepath & name and the sql you're passing,
i.e., change:

.Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _
"From [Events] Where Month([Date_F])=3 Order By [DATE_F]"

Approach 2 didn't attempt to create another recordset, it simply used an
array:

'--------------
Sub GrbAccessData2()
Dim cn As ADODB.Connection
Dim oRs1 As ADODB.Recordset
Dim myArr() As Variant
Dim lstField As Date
Dim I As Integer

Const dbFullname As String = "P:\DATA\test.mdb"

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbFullname & ";"

Set oRs1 = New ADODB.Recordset
With oRs1
.Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _
"From [Events] Where Month([Date_F])=3 Order By [DATE_F]", _
cn, adOpenStatic, adLockReadOnly
Let myArr = oRs1.GetRows
.Close
End With

Set oRs1 = Nothing
cn.Close: Set cn = Nothing

myArr = WorksheetFunction.Transpose(myArr)

Let lstField = myArr(1, 2)
For I = 2 To UBound(myArr, 1)
If myArr(I, 2) = lstField Then
myArr(I, 1) = Null
myArr(I, 2) = Null
Else: lstField = myArr(I, 2)
End If
Next

Sheets(2).Range("a1").Resize( _
UBound(myArr, 1), UBound(myArr, 2)).Value = myArr

End Sub
'--------------

Again, you'll need a reference to ADO. Both are dynamic.

GrbAccessData2 is about 10% faster in my tests. But, I'm using Excel's
Transpose() function to flip the array in that example, and if you're using
Excel 2000-, Transpose will have a fit if the array is holding more than
5,461 elements.

Regards,
Nate

[DonRetd]

Oh Nate,

So far my experience with VBA has not been good, because I am so inept with
it.
The examples look like an even more sofisticated use of VBA.
Wish me luck!
I don't know if we have Microsoft ActiveX or ADO on our church PC (or at
home).
Does the statement Sub GrbAccessData1() infer that these examples are going
after MS Access Data? Also, if Access is not required, I may be able to
experiment with this at home first.
I assume the examples are accomplishing the elimination of the repeating
data in DAY and DATE. If so, I would not need to go after Access data. Can I
assume the examples are not dependant on using Access (syntax, etc)?
Is my first impression correct, that example 2 would be less complicated to
impliment?
Sorry to be so inexperienced with VBA and such. I don't even have a book,
but trying to get one at the library. I hope I can pick up enough to
impliment your examples.

Thanks again,
Don

[Nate Oliver]

Hello again Don,

> The examples look like an even more sofisticated use of VBA.
> Wish me luck!

Good luck!

> I don't know if we have Microsoft ActiveX or ADO on our church PC (or at
> home).

You'd want to look in the VBE. Go into the module you want to use, click
Tools-References and Browse for: Microsoft ActiveX Data Objects 2.5+
Library. You'd want to set a reference to this library by checking it and
clicking 'Ok'.

> Does the statement Sub GrbAccessData1() infer that these examples are going
> after MS Access Data? Also, if Access is not required, I may be able to
> experiment with this at home first.

I assumed that your data was starting in Access. Does it not? If not, no
need to place it in Access then run either procedure.

> I assume the examples are accomplishing the elimination of the repeating
> data in DAY and DATE. If so, I would not need to go after Access data. Can I
> assume the examples are not dependant on using Access (syntax, etc)?
> Is my first impression correct, that example 2 would be less complicated to
> impliment?
> Sorry to be so inexperienced with VBA and such. I don't even have a book,
> but trying to get one at the library. I hope I can pick up enough to
> impliment your examples.

#2 is still connecting to Access, but if your data was sitting in a
spreadsheet, you could simply do something like the following:

'----------------
Sub GrbData()
Dim myArr() As Variant
Dim i As Integer

With Sheets(1)
Let myArr = Range(.Range("a1"), _
.Range("f65536").End(xlUp)).Value
End With

Let lstField = myArr(1, 2)

For i = 2 To UBound(myArr, 1)
If myArr(i, 2) = lstField Then
myArr(i, 1) = Null
myArr(i, 2) = Null
Else: lstField = myArr(i, 2)
End If
Next

Sheets(2).Range("a1").Resize(UBound(myArr, 1), _
UBound(myArr, 2)).Value = myArr

End Sub
'----------------

Regards,
Nate Oliver

[Nate Oliver]

Sorry, I had an undeclared Var and was using Let inconsistently. Try:

'----------------
Sub GrbData()
Dim myArr() As Variant

Dim i As Integer, lstField As Date

With Sheets(1)
Let myArr = Range(.Range("a1"), _
.Range("f65536").End(xlUp)).Value
End With

If IsDate(myArr(1, 2)) Then Let lstField = myArr(1, 2)

For i = 2 To UBound(myArr, 1)
If myArr(i, 2) = lstField Then

Let myArr(i, 1) = Null
Let myArr(i, 2) = Null
Else: Let lstField = myArr(i, 2)

[Joshua Barker]

Hello Don,

Error 2501 stems from the system not being able to find the table or
query or database that you specified. It is also a symptom of a
corrupted database.

Just a thought, but have you tried removing the ampersand "&" character
and blank spaces from your path/file names?

Example - change:

"C:\Shared\Shared T&P\T&PFCC.mdb"

to
"C:\Shared\Shared_TP\TPFCC.mdb"

Also, check the file size of your database. If it is more than a couple
of megabytes in size, then you should think of a ways to clean it up and
remove unecessary tables, queries and reports.

Finally, run 'Compact & Repair' from the 'Tools -> Database Utilities'
drop-down menu to fix any broken links.

Good luck!

Joshua

*** Sent via Developersdex http://www.developersdex.com ***