Vista - How to open Access in VBS and leave it open

Part II: I have a daily task that involves using an Access database, a .txt
file in Notepad and a folder, the same ones every day. I've written a few
VBS programs but I'm trying to learn more. So I set out to write myself a
simple VBS to open all three for me each time. But it seems each of the
supposedly simple steps presents me with an unexpected difficulty. Let's
talk about the Access database:

I've tried two ways. I'm used to writing VBS programs for Excel, so I can
easily enough open an Access.Application object, then open my specified
database, then open the database's main form; this seems to work just like in
Excel. The problem is that unlike Excel, when the VBS terminates the Access
application goes away.

In a way this makes sense: The Access.Application object was created by VBS
and when VBS stops the object is deleted, right. So I shouldn't be surprised
that the application is deleted too. But it doesn't happen in Excel. What's
the difference between them, and how can I get VBS to open Access and leave
it open when VBS stops?

Alternatively, I tried using Wscript.Shell like this:

Set acc = WScript.CreateObject("WScript.Shell")
acc.Run "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

But when I run this, WSH tells me "The system cannot find the file
specified" (error code 80070002). It works fine from Run, though.

Which technique should I use, and what am I doing wrong?

"Bob Bridges" <BobBridges@xxxxxx> wrote in message
news:893DA85B-F7A7-42E2-B0BC-83B83B086614@xxxxxxSince your path has spaces in it, you need to wrap it inside quotes:

Set acc = WScript.CreateObject("WScript.Shell")
acc.Run """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"""

Oh, of course; that's what caused the problem in Part I, too, though it was a
completely different window and path. Thanks.

Anyone know about the other part -- about opening Access.Application in VBS
and having it stay around after the VBS terminates?

--- "James Whitlow" wrote:

Bob Bridges wrote:You can use OLE Automation:

set obj=createobject("access.application")
obj.visible = true

If you wanted your script to close Access, you would need to use the
Quit method:
obj.Quit

In your case, you want to leave it running so simply don't execute that
statement.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

"Bob Barrows [MVP]" <reb01501@xxxxxx> wrote in message
news:%236QqhQrBJHA.4580@xxxxxxI played around with this over the weekend and my results were the same
as Bob's. If I used 'CreateObject' in VBScript to instantiate Access, that
instance terminates along with VBScript, unlike Excel, Internet Explorer &
other objects I have used. They remain open unless the 'Quit' method is
called. I thought perhaps crashing (using 'Err.Raise1') the VBScript instead
of letting it terminate normally might orphan that instance of Access, but
it did not work. As soon as the error dialogue is closed, Access disappears.

James Whitlow wrote:My mistake - I guess my memory is failing me ... I could have sworn it
would stay open if Quit was not called
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

I did some playing around with it, too, an hour or so ago. I think I have
the answer: When I open a database in Access, I have the same problem we're
all talking about here, but when I open a form in that database, it acts like
Excel, ie the app stays open even after the script is completed. And of
course in Excel, my VBS programs always left an xls open when they were
through creating it, so I could check it and perhaps manipulate it on the
spot.

So I'm guessing that almost the same thing is happening in Access as in
Excel, it's just that an AccessObject has to be open to keep Access from
going away when the Application object does. Maybe Excel would close, too,
if I created it in VBS but never opened a particular workbook.

And this just in: When I open it in this way, and then close the form, the
rest of the app disappears immediately. So it's just that form that's
keeping Access alive.

If I learn more, I'll pass it on.

"Bob Barrows [MVP]" wrote:

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
"Bob Bridges" <rhbridg.RemoveThisNode@xxxxxx> wrote in message
news:B0D17071-DD58-47F9-AB28-7950C1306E30@xxxxxxhavewe'reacts likeofweretheinfromtoo,form, thethoughAccess.Applicationtheexecutediddisappears.ita

It looks like you meant to reply here, but hit the button too soon...?

--- "Bob Barrows [MVP]" wrote:

Actually, meant to cancel it but hit Send by mistake - I haven't come up
with a solution.

Bob Bridges wrote:--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.