middleware, automating Access from "scheduler"

[phleduc]

I am building an application that grabs data from one application (webbased)
with multiple linked tables,
mixes the data, and pushes them to update another Access based application.
The application was built to be form driven so we can see "what is going on"
but using functions.
Now there is a request to automate the process, meaning we need to run the
functionality automatically on a regular basis,
but without even opening the access database.
How would you handle this best? I have never even done anything similar,? I
was thinking about using windows scheduler,
building an exe file in classic VB that would use access as an automation
server calling the functions in there from outside?
Does this make sense? Just need to know at this point where to start doing
my reasearch?
How would the code look to call a VBA function in Access from VB.

Philip

 

[jacksonmacd]

Just build a conventional MDB file and load that file with the
scheduler. Use the startup command in Access to load your default
form. What I've done several times is to put a countdown timer on the
default form - when it hits Zero, then it executes the appropriate VBA
command. I also include a "Cancel" button to interrupt the countdown
timer in case I want to open the application without it running
automatically.

 

[david]

Only use Access automation if you need to produce reports.

For all other uses, use ADO automation or DAO automation.

You can't call Access VBA functions from anything else.
Still, using Access automation for unattended automation
is not a good idea.

ADO and DAO automation objects are much less likely to
break than an Access Automation.

ADO and DAO are much more likely to run correctly from
a system login than an Access application object is.

ADO and DAO don't need to have an installed printer.

ADO and DAO don't have "OK" message boxes that hang
when there is no interactive user.

ADO and DAO are easier to use than the Access Application
object.

If you need to print reports, you will need the Access Application
object. You will need to have an installed printer, you should run
a background process that automatically presses the "ok" button
on any Access dialogs, and it may not run correctly using the Windows
Scheduler.

You can choose to use ADO or DAO depending on which you
are more familiar with. DAO is better if you are using MDB's,
ADO is better if you are using SQL Server.

You can't use macro's or forms.

You want to get rid of your forms anyway, because of the
problems you can have running an unattended process.

Any macro's will have to be rewritten as VBscript, Cscript,
VB6, C executable, .Net or whatever. I normally start with
VBscript, because it is closest to Access VBA.

VBA functions can be ported to VBscript or VB6, but
queries can't call those new functions. Still the new functions
can call queries. Which do you need?

(david)

 

[phleduc]

I am not sure I understand what you mean by using ADO automation?
Ado is used within my functions inside the database but I think this is not
what you mean.
Is there a solution where I can just use the existing code in the database
but calling it from outside the database. I could probably reduce the code
to one function within the db that calls all the others we need and does it
all.

 

[david]

When you use ADO in your existing database, you probably
declare a Recordset to use? Or maybe a Connection? Those
are automation objects, just like the Access.Application object.
The Access.Application object already has an

Access.Application.dbEngine

object that you use for DAO automation like OpenDatabase.

And the Access.Application object already has a

Access.Application.CurrentConnection

object that you use for ADO automation.


You can use objects like those DAO and ADO objects from
VBA, or VB6, or VBScript, or ASP or whatever. The only
difference is that you outside Access.Application, you always
have to set them up - there is no default value, so you can't
just go
set rs=codedb.openrecordset(mytable),

you have to do

set dbe = createobject("dao.dbengine.36")
set codedb = dbe.OpenDatabase("c:\db.mdb")
set rs = codedb.OpenRecordset("mytable")

createobject is exactly the same method you would use
to get an Access.Application for scripting. And the
Access.Application object would have an ADO object
and a DAO object. We just want the ADO or DAO object,
not the forms, reports, macros and VBA objects, so it
will be smaller, faster, safer.

Just take all your code, move it into a file called "myfile.VBS"
or "myfile.bas" or "myfile.asp" depending on if you want
to use VBS, or VB6, or ASP. All of your code is already
compatible. You just have to get your declarations right and
create the right objects.

(david)

 

[phleduc]

thank you

When you use ADO in your existing database, you probably
declare a Recordset to use? Or maybe a Connection? Those
are automation objects, just like the Access.Application object.
The Access.Application object already has an

Access.Application.dbEngine

object that you use for DAO automation like OpenDatabase.

And the Access.Application object already has a

Access.Application.CurrentConnection

object that you use for ADO automation.


You can use objects like those DAO and ADO objects from
VBA, or VB6, or VBScript, or ASP or whatever. The only
difference is that you outside Access.Application, you always
have to set them up - there is no default value, so you can't
just go
set rs=codedb.openrecordset(mytable),

you have to do

set dbe = createobject("dao.dbengine.36")
set codedb = dbe.OpenDatabase("c:\db.mdb")
set rs = codedb.OpenRecordset("mytable")

createobject is exactly the same method you would use
to get an Access.Application for scripting. And the
Access.Application object would have an ADO object
and a DAO object. We just want the ADO or DAO object,
not the forms, reports, macros and VBA objects, so it
will be smaller, faster, safer.

Just take all your code, move it into a file called "myfile.VBS"
or "myfile.bas" or "myfile.asp" depending on if you want
to use VBS, or VB6, or ASP. All of your code is already
compatible. You just have to get your declarations right and
create the right objects.

(david)