Migrating from Access to Oracle

i used the 3rd option
but didn't solve the problem!

problem is not the space msg
the problem is the system resource exceeded
anyways, i think i know why because the columns are too many
i think i have to limit them to 16 columns at a time
i'll try this

Hi almaha,

Checkout http://kb.webecs.com/article.aspx?id=10100&cNode=8L7W6W

Gary

it didn't work
even when i reduced the query append columns
still
maybe the records are too many
590254
?

this makes more since that they are talking about the ODBC
but how can i solve this problem?
i linked my oracle tables into the access using the ODBC so i can migrate the data.
is there any other way to do this?

Maybe try appending in blocks, try 50,000 rows at a time?

how can i do that?

Maybe you should be importing the data from the Oracle side?

The message "system resource exceeded" isn't related to the number of rows, but to the number of table fields.
The Access database has a max of 255 fields for a table, a limitation Oracle doesn't have.

Check the number of Oracle columns first. When you don't need all fields, create a select for those needed, when more are needed, create multiple selects all with the unique key, thus enabling you to link them afterwards.

Nic;o)

The title of this question is "Migrating from Access to Oracle" - is this, in fact, what you are trying to do?

Good point Badotz, I was looking "the other way around".
Guess Access does hold less than 255 fields, but when the INSERT is executed it finds too many fields in the Oracle table and stops working. In a case like that an Oracle view with the fields to be inserted could be defined and used instead of the table, or you would need to use a pass through query holding an  INSERT statement for each row with the Access table Values as literals.

Nic;o)

But but but...if indeed the migration is from Access to Oracle, isn't there a way to import Access data from within Oracle itself?

I'm certainly not a DBA, but this sounds plausible, no?

yes i am trying to import data from an access table to an orcale table using access query
i'm not capable of using oracle itself because it is in the server and i don't have access to it
and i never used oracle
i only used the "SQL + 8" under "Oracle for Windows" in my programs
and entered the username and password and host string they gave me
and changed the password using ALTER
that's it

Looks to me that you should use a pass through query to prevent Access from crashing because of the number of fields in the Oracle table.
This implies that you need to write an INSERT statement in Oracle syntax like:
INSERT INTO tblOracle (Fieldname1, Fieldname2, ...) Values ('value1_text', Value2_num, 'Value3_date');
(Oracle surrounds dates with ' instead of the Access #)

For your number of rows I guess doing it by hand isn't an option, so I would first create a "dummy" pass through query (content isn't of any interest as it's changed dynamically from code) and use VBA to fill the string in a recordset processing loop like:

dim qd as DAO.Querydef
dim rs as DAO.Recordset

set qd = currentdb.querydefs("qryDummyPassThrough")
set rs = currentdb.openrecordset("tblMyTable")
while not rs.eof
   qd.SQL = "INSERT INTO tblOracle (Fieldname1, Fieldname2) Values ("
   qd.SQL = qd.SQL & chr(34) & rs!field1_text & chr(34)  ' for text and date fields
   qd.SQL = qd.SQL & rs!field1_num                                  ' for numeric fields
   '...etc..
   qd.SQL = qd.SQL & ");)
   qd.execute
   rs.movenext
Wend
set rs = nothing

Getting the idea ?

Nic;o)