A
Arly Q.
I am having problems with an automation issue made more complex by
limitations on my access to the queries being called. I have an Excel sheet
which needs to run a series of three SQL queries contained in an Access
database, then extract data from the generated table. Currently, those
queries are being run from an Access form that accepts a single parameter.
Unfortunately, some of the SQL queries being called themselves call other
queries; the parameter is used by some of the nested calls. I suspect that
there is a better way to do the SQL side of this, but I do not have the
ability to address that.
I have tried a couple solutions to this. Ideally, I would like to handle
this with DAO.
Sub GetScheduleTable(MDBFile as String, MDBTable as String, ReqParam as
String)
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim TVA, Sched1, SchedNames as DAO.QueryDef
Set db = OpenDatabase(MDBFile)
Set TVA = db.QueryDefs("qryTVA")
TVA.OpenRecordset
Set Sched1 = db.QueryDefs("qryschedule")
Sched1.OpenRecordset
Sched1.Parameters("Forms!frmScheduleRefresh!DATA")=ReqParam
Sched1.OpenRecordset
Set SchedNames = db.QueryDefs("qryScheduleNames")
SchedNames.OpenRecordset
' I then extract the data from tblGenSchedule; cut for space since it
works
End Sub
This doesn't work, apparently because SchedNames (at least) contains other
SQL queries in FROM, some of which depend on the Parameter I set for Sched1.
Unfortunately, they don't inherit that knowledge, and I have no idea how to
pass parameteres to queries whose names I don't even know.
I've also tried doing this without DAO, by calling the Access macro that
runs all 3 queries.
Sub GetScheduleTable(MDBFile as String, MDBTable as String, ReqParam as
String)
Dim AccessApp as Object
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase MDBFile
AccessApp.DoCmd.openform "frmSched"
AccessApp.Forms("frmSched").Controls("DATA").Value = ReqParam
AccessApp.DoCmd.RunMacro "mcrScheduleRefresh"
' Again, cutting the table extraction. That much always works.
End Sub
This has a different problem. It spams the user with the Excel "waiting for
an OLE application to finish" box, about every 10 seconds. I can suppress
the box with Application.DisplayAlerts = False, but then I don't seem to get
the message box prompting the user to login to the secure ODBC source called
from somewhere in the nested SQL tree, so the data fails to generate.
I have also considered ShellandWait, running the macro off the command line.
But I do not know how to pass the parameter that way. If I had the ability
to rewrite the Access side of this system, I could pass it over the command
line via /Cmd, but, again, I can't do anything about the lousy Access
implementation here...
Any thoughts?
limitations on my access to the queries being called. I have an Excel sheet
which needs to run a series of three SQL queries contained in an Access
database, then extract data from the generated table. Currently, those
queries are being run from an Access form that accepts a single parameter.
Unfortunately, some of the SQL queries being called themselves call other
queries; the parameter is used by some of the nested calls. I suspect that
there is a better way to do the SQL side of this, but I do not have the
ability to address that.
I have tried a couple solutions to this. Ideally, I would like to handle
this with DAO.
Sub GetScheduleTable(MDBFile as String, MDBTable as String, ReqParam as
String)
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim TVA, Sched1, SchedNames as DAO.QueryDef
Set db = OpenDatabase(MDBFile)
Set TVA = db.QueryDefs("qryTVA")
TVA.OpenRecordset
Set Sched1 = db.QueryDefs("qryschedule")
Sched1.OpenRecordset
Sched1.Parameters("Forms!frmScheduleRefresh!DATA")=ReqParam
Sched1.OpenRecordset
Set SchedNames = db.QueryDefs("qryScheduleNames")
SchedNames.OpenRecordset
' I then extract the data from tblGenSchedule; cut for space since it
works
End Sub
This doesn't work, apparently because SchedNames (at least) contains other
SQL queries in FROM, some of which depend on the Parameter I set for Sched1.
Unfortunately, they don't inherit that knowledge, and I have no idea how to
pass parameteres to queries whose names I don't even know.
I've also tried doing this without DAO, by calling the Access macro that
runs all 3 queries.
Sub GetScheduleTable(MDBFile as String, MDBTable as String, ReqParam as
String)
Dim AccessApp as Object
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase MDBFile
AccessApp.DoCmd.openform "frmSched"
AccessApp.Forms("frmSched").Controls("DATA").Value = ReqParam
AccessApp.DoCmd.RunMacro "mcrScheduleRefresh"
' Again, cutting the table extraction. That much always works.
End Sub
This has a different problem. It spams the user with the Excel "waiting for
an OLE application to finish" box, about every 10 seconds. I can suppress
the box with Application.DisplayAlerts = False, but then I don't seem to get
the message box prompting the user to login to the secure ODBC source called
from somewhere in the nested SQL tree, so the data fails to generate.
I have also considered ShellandWait, running the macro off the command line.
But I do not know how to pass the parameter that way. If I had the ability
to rewrite the Access side of this system, I could pass it over the command
line via /Cmd, but, again, I can't do anything about the lousy Access
implementation here...
Any thoughts?