Importing from Oracle into Access via macro - capture errors 2

[MwTV]

Each day at 7:00 a.m., a macro within Access appends records from Oracle 9 database into local Access table.
Specifically, I have a passthru query that is converted to an append query and within a macro.

Currently, trying to capture possible errors encountered when appending the records.

Upon arriving in office at 8:00, I would like to access a table that reveals any errors encountered during the import and then running the macro manually.

Is this possible?

 

[mp9]

You don't say which version of Access you're using, so I'm going to assume it's not A2007 (as that, I think, is the first version to do error handling in macros - somebody correct me if A2003 has it).

So what you need to do is convert the macro to VBA code - this can then be modified to capture errors and, depending on the error code, do different things like append the error message to a table or whatever you like.

You can read more about converting a macro to VBA in Access Help or here.

 

[MwTV]

I am using Access 2000 and based on what I have read - the append queries must be within a macro if I am using Windows scheduler to append the data from Oracle to Access on a daily basis.

Any additional comments concerning the automation of the import would be appreciated.

 

[PHV]

The macro launched by the windows scheduler (via the /x command line option) may call a VBA function (RunCode action I guess).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MwTV]

PHV,

Are you aware of coding that will capture import-related errors while the macro is running?

In other words, is there coding that will let me know if the Oracle application was maybe "down" during the time I tried to extract data? What would be the error message received?

 

[PHV]

If you want to catch errors you have to convert your macro to VBA as already suggested by mp9.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886