Access doesn't always close (Excel VBA) & passing parameters

M

Michelle

Hello all!

I'm using Access 2003 and Excel 2000

(1) I'm trying to run an Access macro from Excel. The code I have works,
but Access doesn't always quit. Sometimes there's no problem. Other times I
can see the macro is done, but Access doesn't close. I have to click on
Excel which gives me an error message which i click "OK", and then closes
Access and everything runs as it should. This is the code that I currently
have.

Dim A As Access.Application

Set A = New Access.Application
A.Visible = True
A.OpenCurrentDatabase "G:\PC Reports New\PC Report.mdb"
A.DoCmd.RunMacro "Multiple Accounts Report"
A.CloseCurrentDatabase
A.Quit
Set A = Nothing
' more Excel VBA code that works properly

(2) On a related note, I've been scouring around looking to see if there's
any way to pass in parameters directly into the Access module. I have found
nothing useful. Currently Access starts from Excel and runs the macro which
only consists of the line RunCode LoopReport. Once LoopReport starts the
user has to input parameters that are then used for a make table query. It
would make my life easier if I could have the user input the parameters in
Excel and then pass them into Access. Something along the lines of
A.DoCmd.RunModule "LoopReport(x,y)"
to replace the line
A.DoCmd.RunMacro "Multiple Accounts Report"

LoopReport being the underlying module in the "Multiple Accounts Report"
macro and x,y being the parameters I want to pass from Excel to Access. It
appears there's no RunModule method though. Is there something else I can
use? Hopefully that all makes sense.

Any help would be much appreciated, particularly the first part. Thanks!
 
J

Joel

You have to modify the access macro or make a new macro in access that will
run either:

1) Using prompt for inputs
2) Take input from input parameters.

You need to post the Access macro.
 
M

Michelle

Joel,

Thanks for the input. I'm assuming you were responding to the 2nd part. I
understand how to modify the Access module to take inputs, and I know how to
use the InputBox in Excel to get the user to input the parameters. I just
don't know how to get the parameters that the user enters in the inputBox in
Excel into Access.

ans = InputBox("How many accounts would you like to look at?" & vbNewLine &
vbNewLine & _
"Please note that the more accounts you choose the longer the queries will
take to run.", "PC Report")
ReDim accts(1 To ans)
For i = 1 To ans
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
startDate = InputBox("Enter the start date for the range of the report." &
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
endDate = InputBox("Enter the end date for the range of the report." &
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")

Function LoopReport(accts() as String, startDate as Date, endDate as Date)
'do all this stuff
End Function

I more need to know the proper code to write in Excel to pass the parameters
into Access. Something along the lines of ...
A.DoCmd.RunModule "LoopReport(" & accts() & "," & startDate & "," endDate &
")" ??

Does that make sense? I didn't want to post the current Access module
because it's long, but if you feel it's necessary please see below. Thanks.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

---------------------------------------------
Option Compare Database

Function LoopReport()
'Make sure the "Microsoft DAO 3.6 Object Library" is checked in
'Tools -> References menu (should be checked already though)

Const rawFile As String = "G:\PC Reports New\RawAcctDump.xls"
Dim i As Integer, ans As Integer, tmp As Integer
Dim startDate As Date, endDate As Date
Dim varStatus As Variant
Dim accts() As String
Dim qdf As QueryDef

'User input boxes to get parameters for the MakeTable query
ans = InputBox("How many accounts would you like to look at?" &
vbNewLine & vbNewLine & _
"Please note that the more accounts you choose the longer the
queries will take to run.", "PC Report")
ReDim accts(1 To ans)
For i = 1 To ans
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
startDate = InputBox("Enter the start date for the range of the report."
& vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
endDate = InputBox("Enter the end date for the range of the report." &
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")

DoCmd.SetWarnings False
For i = 1 To ans
'To notify user which acct is going through the process
varStatus = SysCmd(acSysCmdSetStatus, "Processing account " &
accts(i) & "...")
If i = 1 Then Kill rawFile

'Run MakeTable query based on user input
CurrentDb.TableDefs.Delete "DailyInfo"
Err.Clear
Set qdf = CurrentDb.QueryDefs("PC_Report__DailyInformation")
qdf.Parameters("Account") = accts(i)
qdf.Parameters("firstDate") = startDate
qdf.Parameters("lastDate") = endDate
qdf.Execute
Set qdf = Nothing

'all the other queries necessary
DoCmd.OpenQuery "BegEndDate"
DoCmd.OpenQuery "CreateDates"
DoCmd.OpenQuery "PC_Report_Prepymt"
DoCmd.OpenQuery "PC_Report_MBS"
DoCmd.OpenQuery "PC_Report_TS1"
DoCmd.OpenQuery "PC_Report_TS2"
DoCmd.OpenQuery "PC_Report_FX1"
DoCmd.OpenQuery "PC_Report_FX2"
DoCmd.OpenQuery "PC_Report_Corp_Credit1"
DoCmd.OpenQuery "PC_Report_Corp_Credit2"
DoCmd.OpenQuery "PC_Report_Corp_Credit_Syn"
DoCmd.OpenQuery "PC_Report_Structured"
DoCmd.OpenQuery "PC_Report_EM1"
DoCmd.OpenQuery "PC_Report_EM2"
DoCmd.OpenQuery "PC_Report_Sort"
'Export data for use in Excel macro
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_Report_FINAL", _
rawFile, _
True, _
accts(i) & "_RangeData"

DoCmd.OpenQuery "PC_MaxDayInfo"
DoCmd.OpenQuery "PC_Report_PrepymtD"
DoCmd.OpenQuery "PC_Report_MBS_D"
DoCmd.OpenQuery "PC_Report_TS1D"
DoCmd.OpenQuery "PC_Report_TS2D"
DoCmd.OpenQuery "PC_Report_FX1D"
DoCmd.OpenQuery "PC_Report_FX2D"
DoCmd.OpenQuery "PC_Report_Corp_Credit1D"
DoCmd.OpenQuery "PC_Report_Corp_Credit2D"
DoCmd.OpenQuery "PC_Report_Corp_Credit_SynD"
DoCmd.OpenQuery "PC_Report_StructuredD"
DoCmd.OpenQuery "PC_Report_EM1D"
DoCmd.OpenQuery "PC_Report_EM2D"
DoCmd.OpenQuery "PC_Report_SortD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_Report_FINALd", _
rawFile, _
True, _
accts(i) & "_LastDay"

DoCmd.OpenQuery "PC_Contr_bottomD"
DoCmd.OpenQuery "PC_Contr_bottomMTD"
DoCmd.OpenQuery "PC_Contr_topD"
DoCmd.OpenQuery "PC_Contr_topMTD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrBottomD", _
rawFile, _
True, _
accts(i) & "_BottomDaily"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrBottomMTD", _
rawFile, _
True, _
accts(i) & "_BottomMTD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrTopD", _
rawFile, _
True, _
accts(i) & "_TopDaily"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrTopMTD", _
rawFile, _
True, _
accts(i) & "_TopMTD"
Next i
'Set status bar back to normal to signify module is done running.
varStatus = SysCmd(acSysCmdClearStatus)
DoCmd.SetWarnings True
End Function
---------------------------------------------
 
J

Joel

I suspect that Access isn't closing because you are getting some errors in
Access and it is not returning to excel

Does the code below help with the parameter passing? With the IsMissing you
can either call the function with or without parameters. The call wil be
with

Sub ExcelCall
Dim Accts as Variant

NumberofAccounts = 10
Redim Accts(5)
Accts(0) = "Michelle Account"

startDate = "3/1/09"

Results = LoopReport(NumberofAccounts, accts, startDate)

end Sub


Function LoopReport(Optional NumberofAccounts as Integer, Optional accts As
Variant, Optional startDate as String)

If IsMissing(NumberofAccounts) Then
NumberofAccounts = InputBox("How many accounts would you like to look
at?" & _
vbNewLine & vbNewLine & _
"Please note that the more accounts you choose the longer the " & _
"queries will take to run.", "PC Report")
End If

If IsMissing(accts) Then
ReDim accts(1 To NumberofAccounts)
For i = 1 To NumberofAccounts
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
End If
If IsMissing(startDate) Then
startDate = InputBox("Enter the start date for the range of the report."
& _
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
End If


End Function
 
M

Michelle

Joel,

Thank you for your idea. I actually can't use the line
Results = LoopReport(NumberofAccounts, accts, startDate)
because LoopReport is a function in Access not Excel where I'm trying to
call it from. Excel won't recognize LoopReport and error out using the above
line. I did, however, figure out the syntax to properly pass parameters into
Access from Excel.

Dim A As Access.Application
Set A = New Access.Application
A.Visible = True
A.OpenCurrentDatabase "G:pC Reports New\PC Report.mdb"
A.Run "LoopReport", ans, accts(), startDate, endDate

Where the function is set up as
Function LoopReport(NumAccts As Integer, accts() As String, startDate As
Date, endDate as Date)

Now for my other question as to why Access doesn't always close, I'm still a
little perplexed. I have tried running the procedure directly from Access
and not using Excel. (I was testing my function before moving on to the
Excel portion.) I never received any errors in Access. Everything seemed to
run properly. Do you think it has something to do with the fact that I have
the database automatically compact and repair the .mdb file every time it
closes? I do this because otherwise the .mdb file seems to grow very big
very quickly. I neglected to mention this before, my apologies. Your
thoughts?
 
J

Joel

Why do you have LoopReport as a function when it is not returning any
variables. Why not make it a Sub. I believe the crashing is occuring
because you have a stack error. When you declare the routine a function it
is expecting something to be put on the stack and something to be taken of
the stack. You are calling LoopReport from excel like a Sub which isn't
putting anything on the stack. Then Access is leaving a space on the stack
for a return variable where excel didn't make room for the return variable on
the stack. This create a stack mis-match and the cause of the crash.
 
M

Michelle

Joel,

I had it as a function because originally I was trying to run the code by
calling an Access macro in Excel, and I thought the code had to be set up as
a function in order to use the line RunCode LoopReport in a macro. Perhaps I
was misinformed on this? Since I now know how to run a function or sub
directly using A.Run instead of A.DoCmd.RunMacro I can try your suggestion.
Thanks. Will let you know how it goes.
 
M

Michelle

Joel,

I tired making LoopReport a sub instead of a function. When I ran it did
not close again. This time I took note of the message that pops up if I
click on Excel, "Microsoft Excel is waiting for another application to
complete an OLE action." As soon as I click OK the .mdb seems to close fine
and my Excel VBA code continues as it should. Below is the code I now have
in LoopReport since it has changed a bit from before. Thoughts? See below.
Thanks for taking time to look into this.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

--------------------------------------------------------------------------
Option Compare Database
Option Explicit

Sub LoopReport(accts() As String, startDate As Date, endDate As Date)
'Make sure the "Microsoft DAO 3.6 Object Library" is checked in
'Tools -> References menu (should be checked already though)
Dim rawFile As String
Dim i As Integer, tmp As Integer, numAccts As Integer
Dim varStatus As Variant
Dim qdf As QueryDef

rawFile = "G:\PC Reports New\RawAcctDump.xls"
numAccts = UBound(accts)

DoCmd.SetWarnings False
For i = 1 To numAccts
varStatus = SysCmd(acSysCmdSetStatus, "Processing account " &
accts(i) & "...")
If i = 1 Then Kill rawFile

CurrentDb.TableDefs.Delete "DailyInfo"
Err.Clear
Set qdf = CurrentDb.QueryDefs("PC_Report__DailyInformation")
qdf.Parameters("Account") = accts(i)
qdf.Parameters("firstDate") = startDate
qdf.Parameters("lastDate") = endDate
qdf.Execute
Set qdf = Nothing

DoCmd.OpenQuery "BegEndDate"
DoCmd.OpenQuery "CreateDates"
DoCmd.OpenQuery "PC_Report_Prepymt"
DoCmd.OpenQuery "PC_Report_MBS"
DoCmd.OpenQuery "PC_Report_TS1"
DoCmd.OpenQuery "PC_Report_TS2"
DoCmd.OpenQuery "PC_Report_FX1"
DoCmd.OpenQuery "PC_Report_FX2"
DoCmd.OpenQuery "PC_Report_Corp_Credit1"
DoCmd.OpenQuery "PC_Report_Corp_Credit2"
DoCmd.OpenQuery "PC_Report_Corp_Credit_Syn"
DoCmd.OpenQuery "PC_Report_Structured"
DoCmd.OpenQuery "PC_Report_EM1"
DoCmd.OpenQuery "PC_Report_EM2"
DoCmd.OpenQuery "PC_Report_Sort"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_Report_FINAL", _
rawFile, _
True, _
accts(i) & "_RangeData"

DoCmd.OpenQuery "PC_MaxDayInfo"
DoCmd.OpenQuery "PC_Report_PrepymtD"
DoCmd.OpenQuery "PC_Report_MBS_D"
DoCmd.OpenQuery "PC_Report_TS1D"
DoCmd.OpenQuery "PC_Report_TS2D"
DoCmd.OpenQuery "PC_Report_FX1D"
DoCmd.OpenQuery "PC_Report_FX2D"
DoCmd.OpenQuery "PC_Report_Corp_Credit1D"
DoCmd.OpenQuery "PC_Report_Corp_Credit2D"
DoCmd.OpenQuery "PC_Report_Corp_Credit_SynD"
DoCmd.OpenQuery "PC_Report_StructuredD"
DoCmd.OpenQuery "PC_Report_EM1D"
DoCmd.OpenQuery "PC_Report_EM2D"
DoCmd.OpenQuery "PC_Report_SortD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_Report_FINALd", _
rawFile, _
True, _
accts(i) & "_LastDay"

DoCmd.OpenQuery "PC_Contr_bottomD"
DoCmd.OpenQuery "PC_Contr_bottomMTD"
DoCmd.OpenQuery "PC_Contr_topD"
DoCmd.OpenQuery "PC_Contr_topMTD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrBottomD", _
rawFile, _
True, _
accts(i) & "_BottomDaily"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrBottomMTD", _
rawFile, _
True, _
accts(i) & "_BottomMTD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrTopD", _
rawFile, _
True, _
accts(i) & "_TopDaily"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrTopMTD", _
rawFile, _
True, _
accts(i) & "_TopMTD"
Next i
varStatus = SysCmd(acSysCmdClearStatus)
DoCmd.SetWarnings True
End Sub
 
M

Michelle

To anybody else running into this problem of Access not closing when called
from Excel...

I thought I would post the solution. I finally realized that if Access
takes too long (which the LoopReport function does take a while in my
particular example) Excel pops up a MsgBox that the user then needs to click
"OK". Obviously the MsgBox stalls the VBA code. The solution is so simple
it makes me mad I didn't figure it out before. (Since on my screen Access
was in front of Excel I didn't realize this was happening for quite some
time.) All you have to do is add the DisplayAlerts = False before opening
Access and then turning them back on afterward, like below...

Dim A As Object

Application.DisplayAlerts = False
'Instantiate Access and run sub
Set A = CreateObject("Access.Application")
A.Visible = True
A.OpenCurrentDatabase "G:\TIG\Jaclyn\Portfolio Construction\PC Reports
New\PC Report.mdb"
A.Run "LoopReport", accts(), startDate, endDate
A.DoCmd.Quit
Set A = Nothing
Application.DisplayAlerts = True

Hopefully somebody else finds this information helpful. I know I had a hard
time finding information on this problem.
 
L

Lee Harper-Smith

Hi Michelle,

I just wanted to say thank you for posting the solution to this issue. i
have spent a couple of hours going round in circles until I read your
simple solution (displayalerts = false).

All the best

Lee

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running an Access function (with parameters) from Excel 6
Run Access Subroutine (or Function) from Excel 2
Macro to run an Existing Microsoft Access Macro 1
close excel; leave access open 1
Query from Excel to Access - Passing Parameters? 8
Query from Access Database based on user selected parameters in Excel 2
Variable number of parameters in Excel User Defined Function 1
Excel VBA Passing Parameters Through Cell 4

Top