Access Download from Oracle-based HR system

[zoroaster]

The set-up: Oracle-based HR system (MidlandHR Delphi Millennium), from which we run reports (outputting large chunks of the DB tables) on a weekly basis. These are output in a text format, and manually FTP'd across to be picked up by Access download, which converts them into Access tables, so we can run ad-hoc queries on the data for reporting purposes. the advantage of which is that requests for wildly different kinds of info come in frequently, and Access is a very flexible tool to do the job.

The nuts and bolts are as follows, using macros and queries: A 'Raw data' table with no keys and all datatypes set to 'text' is populated using TransferText (fixed width with Import Spec) from the FTP file (text), A table duplicating the datatypes and keys of the original HR system-table is cleared using a Delete query, then repopulated using an Append query pulling data from the Raw table.

However, I think that Access is reaching its limit in terms of the amount of data it can convert. It frequently 'hangs' during the process where it is repopulating the larger tables, whereby I mean that nothing appears to be happening, no visible progress is made for hours on end, but according to Taskmanager Access is 'running', and although I get no error messages, it ties up the PC's CPU to the extent that I can't do anything else with the PC whilst it's running. If I use taskmanager to quit Access (the only way to quit out of it in this circumstance), and reopen the db and rerun the queries, they appear to run more efficiently for a time, but performance is compromised by the number of queries that are run within a macro. Is Jet still holding onto something from each successive query which takes up memory? I'm really at the limit of my knowledge here, so some advice would be most appreciated.

Would more processing power on the PC I'm using to download help? (At least in short-term)
Are we going to have to move away from Access as an ad-hoc reporting tool? We've tried Business Objects but haven't found it flexible enough.

Laters, Z

"42??? We're going to get lynched!

 

[lupins46]

Can you post some indication of the data volumes.

 

[JerryKlmns]

Are you saying that the big fat file form Oracle is imported and then cut to smaller normallized tables?

Then link to that file, and run your action queries like
(VBA in module)

currentproject.connection.execute YourActionQuerySQLstatement,,adCmdText+adExecuteNoRecords

This would be much faster.

A note: When a freshly built query is executed and then saved, it is compilied by the mdb and runs faster

 

[zoroaster]

Thanks JerryKlmns - a little clarification: most of the normalized tables in the AccessDB are simply duplications of tables in the (OracLe-based) HR system, with one major exception (a report of all employees), which is a composite report pulling data from various tables on the HRsystem. They are each then FTP'd from Oracle folder as text delimited files with an *.out file extension, transferred into Access as text delimited files and converted into normalized tables.

Not sure I fully understand your meaning here:

link to that file, and run your action queries like
(VBA in module)

currentproject.connection.execute YourActionQuerySQLstatement,,adCmdText+adExecuteNoRecords

Is it possible to link to the text delimited files, or am I misunderstanding you?

Lupins46 - a few rough stats on the larger tables:
Absence table: 48MB, 178,000 records
ContractNotepad table: 46MB, over 1 million recs
Employees:63MB, 83,000 recs
By their nature, some of the tables will increase in size indefinitely!

Laters, Z

"42??? We're going to get lynched!

 

[lupins46]

Importing this volume of records can be difficult.
I'd be inclined to see whether it would be better to import to a table with no indexes set and then run some code to set the indexes you need. It will still take a while though.

 

[JerryKlmns]

About linking to text delimited files the answer is YES. It is also a YES for fixed length.

About the number of records, is it necessary to import all of them or could you import just the new ones? A date field would be sufficient to import previous month records

Dim TheMonth As String

TheMonth= "#" & Year(DateAdd("m",Date,-1)) & "/" & Month(DateAdd("m",Date,-1)) & "/01#;"

strSQL="Insert Into tblAbsence From txtAbsence Where (txtAbsence.DateRecorded>=" & TheMonth

 

[zoroaster]

Good suggestion, this will work for MOST of the tables, as the records are simply added and shouldn't change, but the Employee table records update and change by the week, and without that table we lose a lot of our lightfooted flexibility!

Laters, Z

"42??? We're going to get lynched!

 

[CosmoKramer]

zeroaster,

Can you link to this Oracle database realtime through an ODBC connection?? I do this with our Oracle clinical data respository (it's 75 GB and growing) using Access for ad hoc reporting, and for the most part it works very well.



Si hoc legere scis, nimis eruditionis habes

 

[zoroaster]

Not 100% sure Cosmo, but don't think so.

Laters, Z

"42??? We're going to get lynched!

 

[zoroaster]

Just a thought, does anyone know if there's a Registry Edit that will allow me to add a file extension to those listed that Access can link to as text delimited (*.txt,*.csv,*.tab,*.asc); I know there's one for file extensions you can import as text (MS Knowledgebase article 304206)...

Laters, Z

"42??? We're going to get lynched!

 

[JerryKlmns]

zoroaster,

I can't play with registry but I can with the file extension

Name "C:\FileName.xxx" As "C:\FileName.txt"

Not your answer but a trick ..........

 

[zoroaster]

Found it! Same neck of the woods as described in the Knowledge Base Article (Method 3) 304206.
Once the instructions have been followed as per the MSKB article, which basically stops Access erroring on the import/link of a file-type it doesn't recognise, run REGEDIT and locate the ImportFilter value under the following subkey in the registry:
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\ISAM Formats\Text
Add the file extension to the list - in my case:
Text Files (*.txt;*.csv;*.tab;*.asc;*.out)
Then when you link to a text file, the Wizard will show files of the new type (*.out) as well.

Anyway, this removes one step from my process - I now no longer have to TransferText in, I can just link to the *.out files which are overwritten by each FTP. I can even use the import spec to define the field-types. The slow bit, which is proving the sticking point, is where I append the data from these tables to normalized tables across the network. Access is notoriously slow when running across a network, and the overhead added by having to create the indexes and check each record against all the others as it appends is what really makes the thing creaky. Any ideas how I can supercharge that a little?

Laters, Z

"42??? We're going to get lynched!