Automated Batch File

[ctr1085]

I currently run reports from Oracle using a *.bat file on my machine with a
connection string similar to this.
sqlplus user/password@server @script.sql

The question i have is about automating an sql script for Microsoft Access in
a similar way to auto-populate data from a text file I export from an oracle
database. My goal is to take the report from oracel and validate the date
with a table in Access and append the new data in the Access table. The
problem I have is figuring out how to automate the process so that I don't
have to actually open an access application and run a script. Any help would
be appreciated.

 

[Albert D. Kallal]

Well, you going to have to open and run ms-access if you going to open and
run ms-access code!!!

(did I miss something here?????).

If you write some code in ms-access in a standard code module, then you can
most certainly schedule that code to be run by the windows scheduler.

So, go ahead, write your procedure code in a standard module. That code can
use sql statements, record sets, and do any kind of imaginable type
processing you need. Once you completed this, the you can run that code as a
windows scheduled task.

I explain how to schedule ms-access code as a windows task (batch job) here:

http://www.members.shaw.ca/AlbertKallal/BatchJobs/Index.html

 

[ctr1085 via AccessMonster.com]

Thanks for the info, I will have to check out how to schedule a task through
the windows scheduler.
I was doing some further research on the subject and found the connection
strings for microsoft access, which can be used to start a macro in the
application itself. Here is the microsoft page with all the command line
switches that can be used to start up the access application and run a macro.

http://support.microsoft.com/kb/209207

 

[ctr1085 via AccessMonster.com]

Do you by chance know how to program the user_name and password into the
access connection string using vbscript? I got the *.vbs to work, but I have
security files set up and need to be able to connect to them. Any help would
be appreciated.

 

[ctr1085 via AccessMonster.com]

Albert,

Thanks for the help. I did figure out how to write my batch file to execute
the access script using a macro connection. I just added the commands to my
batch script.

@echo off

sqlplus user/password@server @U:\_BatchFiles\SQL\_script.sql

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\
Rrs_Checks_Update.mdb" /wrkgrp "C:\DBSECURITY.MDW" /user username /pwd
password /x macro
exit

basically it starts up the front end db and runs the macro. once the macro
is finished, the db will close.

 

[Albert D. Kallal]

Your approach is great if you using macros...

I never use them). Further, your macro code will need to exit the appcation
when done.....

My example is nice because don't have to hard code the path name to
ms-access,a nd further you running VBA code, not a ms-access macro.

This means that your script can execute MORE THEN one code routine in the
access application.

However, since you do have a workgroup file, then your approach is likely
the best (and, you CAN have your macro call the vba code).

 

[ctr1085 via AccessMonster.com]

Thanks for the info. It was informative.