Data Validation

  • Thread starter Thread starter TP
  • Start date Start date
T

TP

Hi,

Can anyone help me please with a data validation issue. I have a set
of Oracle Tables which are being used in a PeopleSoft Financials
system. There is a requirement to validate all data being input into
an Excel Spreadshet against these Oracle Tables. What would be the
best way of doing this? I have looked at the Data Validation option
within Excel but the volumes of data we are talking about here makes
it not possible.

Any help would be gratefuly apprecitated.

Richard
 
Define 'validation' in your context? Do you mean that an entry into some
specific cell in the Excel sheet must match some entry in a field in one of
the Oracle tables?

I would presume that the size of the Oracle database precludes reading it
into an Excel sheet and doing the typical things like MATCH or even VLOOKUP().

If you have Read permissions to the Oracle database you may be able to use
Oracle Objects for OLE (OO4O)
http://www.oracle.com/technology/tech/windows/ole/index.html

You could use it to query the database for a match using coding in the
worksheet's _Change() event handler.

I used OO4O many years ago (think Office/Excel 97) in some work I did for SW
Bell and the SBC Corporation and it worked quite well. About 3 lines of code
was all it took to run a query against the database in Excel VBA. Your delay
in validation will be in waiting for the results to get back to you over the
network. You might consider waiting until all data is entered on the
worksheet and then doing a validation of all entered items at one time. It
would be a longish wait once, rather than possibly longish waits after each
data item entry.

Come up with a simple, quick to execute query that would return a minimum
number of records to improve performance.
 
Back
Top