Parameter query automation

[CS45]

yes it is a make table query. I’m currently working on a function to properly execute it. Side question, is there a way to create a dummy duplicate of my Access database so I can toy with functions and all that without messing things up in the one my boss uses? This process is vital so I’d like to mess with a dummy duplicate if possible...

 

[Isaac]

I think Paul suggested that earlier - good suggestion, Sure, just copy and paste your database file somewhere to another place in the folder. Or did you mean something else ?

 

[CS45]

It is a shared file, so would working in the copy-pasted file change the original if I were to save it?

 

[Isaac]

would working in the copy-pasted file change the original if I were to save it?

No, not if I understand you correctly. But it will be nice to have a separate file to test with.
Note - if your database has linked tables, and you work in a COPY of that database, and all else is equal and you do nothing to prevent it, any data you change in those linked tables will be live (as they will still be linked to the real back end). If nothing I am saying about linked tables and back end makes sense to you, then please study and follow this

 

[CS45]

Yes that makes sense. I was just told that if I open the live file and edit it, the hit “save as” and save it somewhere not on the server, then the live one will not be changed. Can anyone fact check this?

 

[Isaac]

I never use Save As, so hopefully someone else will chime in. I would guess that you are correct.
But why wonder - just copy and paste the file, then work in the copy. Seems easier.

 

[pbaldy]

I also copy/paste and then work on the copy.

 

[isladogs]

Another way to do this is create a macro to run your function.
Then use task scheduler to open your database using the /macroname switch.
It will open your database and automatically run your macro.
If you include code to close your database as part of the macro, that can also be automated.

BTW File...SaveAs is equivalent to copying the file. Use either method to make a copy.

 

[NauticalGent]

Very welcome! Just one more tool for the toolbelt

And the day that I would find this useful has come... A quick question: When this code runs, the Access App displays on screen, stays displayed just long enough to do it's thing and the closes (I added "objAccessApp.Quit"). Is there a way to turn screen-updating off? Dr. Google is no help, plenty of threads about how to do it with VBA (Application.ScreenUpdating = False), but they do not work in VBS.

If it isnt possible I can live with it but most users get easily distracted...

 

[Isaac]

And the day that I would find this useful has come... A quick question: When this code runs, the Access App displays on screen, stays displayed just long enough to do it's thing and the closes (I added "objAccessApp.Quit"). Is there a way to turn screen-updating off? Dr. Google is no help, plenty of threads about how to do it with VBA (Application.ScreenUpdating = False), but they do not work in VBS.

If it isnt possible I can live with it but most users get easily distracted...

You should be able to reference it through your application instance variable. So if your VBS was:

dim accessapp
set accessapp=createobject("access.application")
accessapp.screenupdating=false

2 disclaimers: 1) I've never used Application.ScreenUpdating in Access and didn't even know it was available, nor what it really does.
2) I do not know if this will necessarily hide the Access app.

Finally, it does look like you can do: Application.Visible=False (or in VBS, YourApplicationVariable.Visible=False), although I've never used it before so can't offer any more than that.

 

[isladogs]

This may not be relevant here but Application.ScreenUpdating works in Excel not Access. You use Application.Echo in Access

I don't know whether that still applies when run from VBS as I haven't tried it.

 

[NauticalGent]

ScreenUpdating throws a "method not supported" and .Visible throws "You entered an expression that has an invalid reference to the property Visible".

I went over all the Methods available in VBS and didn't find either one. However, the .Visible error leads me to beleve that the syntax I am using is wrong.

Here is what is used:

dim objAccessApp, strDBPath

strDBPath="MyPath.accdb"

set objAccessApp=createobject("Access.Application")
    objAccessApp.OpenCurrentDatabase(strDBPath)
    objAccessApp.Visible = False
    'objAccessApp.ScreenUpdating = False
    objAccessApp.MyProc "UpdateTable" 'Name of your vba procedure
    objAccessApp.CloseCurrentDatabase
    objAccessApp.Quit

 

[Isaac]

This may not be relevant here but Application.ScreenUpdating works in Excel not Access. You use Application.Echo in Access

I don't know whether that still applies when run from VBS as I haven't tried it.

No wonder I "haven't tried it in Access" !

 

[Isaac]

ScreenUpdating throws a "method not supported" and .Visible throws "You entered an expression that has an invalid reference to the property Visible".

I went over all the Methods available in VBS and didn't find either one. However, the .Visible error leads me to beleve that the syntax I am using is wrong.

Here is what is used:

dim objAccessApp, strDBPath

strDBPath="MyPath.accdb"

set objAccessApp=createobject("Access.Application")
    objAccessApp.OpenCurrentDatabase(strDBPath)
    objAccessApp.Visible = False
    'objAccessApp.ScreenUpdating = False
    objAccessApp.MyProc "UpdateTable" 'Name of your vba procedure
    objAccessApp.CloseCurrentDatabase
    objAccessApp.Quit

Interesting, I'm not sure why that Visible error might be happening? According to here
Application.Visible property (Access) | Microsoft Docs
All it really does is minimize the app screen anyway, but still, seems like it should work.

Wonder if it has to do with any required combination of UserControl first? Like maybe usercontrol has to be either true or false for visible to work? Just grasping at straws there as you can tell.....so don't be surprised if all I got was a straw : )

But, MS also says: "When an Application object is created by using Automation, the Visible and UserControl properties of the object are both set to False."
however, I confess I also have a Scheduled Task that runs a VBS that opens an Access database through automation - and it sometimes comes up on screen, whether I want it to or not (my vbs code has no specification either way so the defaults should work, but... Wonder if this is a YMMV thing..

 

[Gasman]

Apparently it can be done one way the way is to daisy chain the VBS scripts?
https://superuser.com/questions/62525/run-a-batch-file-in-a-completely-hidden-way

Edit:
John, try this

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.Usercontrol = False
MSGBOX accessApp.visible 
'MsgBox "App is invisible"
accessApp.OpenCurrentDataBase("C:\Users\Paul\Documents\Test.accdb")

with your file of course

 

[isladogs]

1. You need the full path to the file
2. Try app.usercontrol=false instead of visible
3. As already mentioned, try app.echo instead of app.screenupdating

Or just open your database using the macro switch where the specified macro runs your UpdateTable function

 

[Isaac]

Ok, I thought NG wanted the app minimized or invisible, rather than out of user control, sorry all if I was mistaken I'll bow out now

Also, both visible and usercontrol are always default to False when app started using automation..

 

[NauticalGent]

Also, both visible and usercontrol are always default to False when app started using automation..

True. as Paul's "MsgBox" suggestion confirmed. Although it DID return a "False" the Access App Window did display for a split second and then close.

app.Echo threw a "Method not supported" error.

Paul's link in post #35 suggests that the feature is not available in VBS but if you call it from a different Script that creats a Shell object: CreateObject("Wscript.Shell").Run """" & WScript.Arguments(0) & """", 0, False

you can then call it from the same Scripts with:
wscript.exe "C:\Wherever\invisible.vbs"

Too much work and my users can live with the flicker...

 

[Gasman]

Strange, I do not get to see hide or hare of Access using that code?

 

[isladogs]

You used accessapp instead of app! So try that with .echo

I think VBS definitely has its uses for complex tasks but for something as 'simple' as this, I'd personally just start Access with a macro switch as previously suggested