Access to Oracle Datatypes?

[thomas goodwin]

I have an Access2002 table with one field, x, with the value 777.
It is defined as data type=Numeric, field size=long integer. Using
Access's TableExport/ODBC to Oracle9i(creating a new table), I get the
Oracle message-1401 "Inserted value too large for column". Defining the
Access table as Number with field size=Decimal solves the problem.
I actually have hundreds of these Number/LongInteger variables in
Access tables. How can I export them to Oracle? Is there an easy way
to change all field sizes to decimal? Or is there a different solution?
thanks, tom g

[Rich P]

I think your easiest bet would be to create the table(s) with the
correct datatypes from scrath and then copy the data over from the
existing table(s). Here is some code to create a table and a field.
You can use a loop to create several fields. More info in the help
files under "Field", "Type" keywords.

Sub NewField()
Dim dbs As Database, tdf As TableDef, fld As Field
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("tbl1")
Set fld = tdf.CreateField("fld1", dbDouble)
tdf.Fields.Append fld
Set dbs = Nothing
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

I left out the dbs.TableDefs.Append tdf part

Revision:

Sub NewField()
Dim dbs As Database, tdf As TableDef, fld As Field
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("tbl1")
Set fld = tdf.CreateField("fld1", dbDouble)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
Set dbs = Nothing
End Sub
If you don't append the tdf object, you will never get a table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!