I have an Access 2003 database which is soon going to run out of space when we hit the 2Gb limit for the linked MDB that holds our biggest table of 2.5 million records.
So, management decided that we had to migrate the table to linked-Oracle - however, the performance of the large queries that depend on this Oracle linked table is abominable - they take up to 15 times as long to return as when using an internal Access table - hours instead of minutes.
So management have decided to migrate the whole application to Oracle - but in the meantime until this new system goes live, I have to make a cut-down version of the original Access table with only the fields necessary for the queries that are slow under Oracle, and populate it from the Oracle table as a kind of cache. This also saves relinking all the other databases and spreadsheets that refer to the original Access table.
However, there seems to be no way that Access can get these records without taking a ridiculously long time, or raising errors that are so STUPID that it implies Microsoft didn't bother testing the Access Import methods (probably programmed by an intern) before releasing the product.
PLEASE can anybody help?
___________________________________
Here are the methods I have tried:-
METHOD 1.
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN={DSN as shown in ODBC Administrator};UID={uid};PWD={password};" & "DATABASE={Oracle Service name}", acTable, "{Oracle Table Name}", "tbl_ORACLE"
First, it returned the following error, as I hadn't created a target table (I thought it would auto-create one with the correct schema):-
"The Microsoft Jet database engine could not find the object 'tbl_ORACLE'. Make sure the object exists and that you spell its name and the path name correctly."
So, I used Copy/Paste (structure only) to make a blank target table 'tbl_ORACLE' with exactly the same schema as my linked Oracle source table. It then gave this error:-
"The Microsoft Jet database engine could not find the object 'tbl_ORACLE1'. Make sure the object exists and that you spell its name and the path name correctly."
So, it's added a "1" on the end of the name of the table I explicitly supplied ... and then said it couldn't find it!!! So, I created a copy table named 'tbl_ORACLE1' - it then said it couldn't find 'tbl_ORACLE2' !!!
METHOD 2.
I tried writing a simple SELECT...INTO statement, where I insert the linked-Oracle fields that I want to keep into the blank target Access table. 14 hours later overnight, and it still hadn't finished!!!
METHOD 3.
The Oracle programmers also have the data in a text flat-file format, so I tried importing using the
DoCmd.TransferText acImportDelim, "ORACLE_Import_Spec", ...
method, which works if the flat-file is empty apart from field definitions.
However, if there is any data in the Oracle flat-file, Access returns inexplicable "Numeric Overflow" errors (with no helpful debug text like row number, field name, etc) if there are any non-skipped numeric or date fields in the Import Spec - despite my spending about 2 working days trying all possible variations of datatypes in the Access Import Specification dialog.
METHOD 4.
In desperation, I decided to try loading the data the hard way, using LINE INPUT to get each line in turn from the flat-file and rs.AddNew, etc, to add the records one-by-one to the Access table.
However, since Microsoft didn't bother putting an optional "input date format" parameter on the CDate() function, I have to use the Format() function to convert the entirely normal "YYYYMMDD HH:MM:SS" date format in the flat-file to something that Access can understand with no possibility of confusing US (MM/DD) and non-US (DD/MM) date parameter orders.
So, I hit the bug in the Format() function where it always returns "Jan" regardless of what month you supply. Try this:-
Debug.Print format("09", "MMM")
or with any month you like instead of the "09".
Likewise, the other bug in the Format() function where it returns random dates. Try this:-
Debug.Print format("0109", "DDMMM")
You won't get 9th Jan or 1st Sept...
So, management decided that we had to migrate the table to linked-Oracle - however, the performance of the large queries that depend on this Oracle linked table is abominable - they take up to 15 times as long to return as when using an internal Access table - hours instead of minutes.
So management have decided to migrate the whole application to Oracle - but in the meantime until this new system goes live, I have to make a cut-down version of the original Access table with only the fields necessary for the queries that are slow under Oracle, and populate it from the Oracle table as a kind of cache. This also saves relinking all the other databases and spreadsheets that refer to the original Access table.
However, there seems to be no way that Access can get these records without taking a ridiculously long time, or raising errors that are so STUPID that it implies Microsoft didn't bother testing the Access Import methods (probably programmed by an intern) before releasing the product.
PLEASE can anybody help?
___________________________________
Here are the methods I have tried:-
METHOD 1.
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN={DSN as shown in ODBC Administrator};UID={uid};PWD={password};" & "DATABASE={Oracle Service name}", acTable, "{Oracle Table Name}", "tbl_ORACLE"
First, it returned the following error, as I hadn't created a target table (I thought it would auto-create one with the correct schema):-
"The Microsoft Jet database engine could not find the object 'tbl_ORACLE'. Make sure the object exists and that you spell its name and the path name correctly."
So, I used Copy/Paste (structure only) to make a blank target table 'tbl_ORACLE' with exactly the same schema as my linked Oracle source table. It then gave this error:-
"The Microsoft Jet database engine could not find the object 'tbl_ORACLE1'. Make sure the object exists and that you spell its name and the path name correctly."
So, it's added a "1" on the end of the name of the table I explicitly supplied ... and then said it couldn't find it!!! So, I created a copy table named 'tbl_ORACLE1' - it then said it couldn't find 'tbl_ORACLE2' !!!
METHOD 2.
I tried writing a simple SELECT...INTO statement, where I insert the linked-Oracle fields that I want to keep into the blank target Access table. 14 hours later overnight, and it still hadn't finished!!!
METHOD 3.
The Oracle programmers also have the data in a text flat-file format, so I tried importing using the
DoCmd.TransferText acImportDelim, "ORACLE_Import_Spec", ...
method, which works if the flat-file is empty apart from field definitions.
However, if there is any data in the Oracle flat-file, Access returns inexplicable "Numeric Overflow" errors (with no helpful debug text like row number, field name, etc) if there are any non-skipped numeric or date fields in the Import Spec - despite my spending about 2 working days trying all possible variations of datatypes in the Access Import Specification dialog.
METHOD 4.
In desperation, I decided to try loading the data the hard way, using LINE INPUT to get each line in turn from the flat-file and rs.AddNew, etc, to add the records one-by-one to the Access table.
However, since Microsoft didn't bother putting an optional "input date format" parameter on the CDate() function, I have to use the Format() function to convert the entirely normal "YYYYMMDD HH:MM:SS" date format in the flat-file to something that Access can understand with no possibility of confusing US (MM/DD) and non-US (DD/MM) date parameter orders.
So, I hit the bug in the Format() function where it always returns "Jan" regardless of what month you supply. Try this:-
Debug.Print format("09", "MMM")
or with any month you like instead of the "09".
Likewise, the other bug in the Format() function where it returns random dates. Try this:-
Debug.Print format("0109", "DDMMM")
You won't get 9th Jan or 1st Sept...
