Re: [Oracle / Access]

If you haven't done this before, it's a really good idea to experiment with it. I've experienced data type changes that were unexpected (Currency, for example is exported as character data). I've also experienced data changes. This specifically occurred when trying to use the "plus/minus" character. It did not convert well at all.

Also, you can write a procedure that does this for you. It would look something like this:

---
Public Sub ExportTables

    Dim tbdTemp as TableDef
    Dim dbsCurrent as Database
    Dim strConnect as String 'ODBC Connection String

    Set dbsCurrent = DBEngine(0)(0)
    strConnect =
"ODBC;DSN=DataSource1;UID=AdminUser;PWD=xyz;SERVER=MYSERVER"

    For Each tbdTemp in dbsCurrent.TableDefs
        DoCmd.TransferDatabase acExport, "ODBC Database", _
        strConnect, acTable, tbdTemp.Name, tbdTemp.Name 
        'Add ",False" if you want to export data too.

    Next 'tbdTemp in dbsCurrent.TableDefs

End Sub 'ExportTables
---

Be sure to follow the rules for table naming outlined by Mr. McDaniels
below.  And, do use all upper case for table and field names.  Using
those quotes to cover mistakes can be a real pain.

Good Luck!
Chris.

Steve McDaniels wrote:


> 

> If the Access table has the following characteristics, you can simply export


> the table from Access to Oracle over an ODBC connection:

> 

> 1. Table Name conforms to Oracle naming conventions (i.e., upper case, no

> special chars, etc)

> 2. All fields names conform to Oracle naming conventions (see 1.)

> 3. All fields types are compatible with Oracle field types (no "memo"

> fields, no "double" fields, etc)

> 4. The table must have a primary key (no strictly true, but simplifies

> things)

> 

> Note:  if you export the Access table to Oracle (successfully) and later

> discover that you forgot to uppercase all names, you can fix this by

> referring to the name in double quotes:

> 

> select "my_lowercase_field_name" from "my_lowercase_table_name" where...

> 

> As an aside, I have found that for regular moving of large volumes of (the

> same tables') data from Access to Oracle is more quickly accomplished using

> an Access exported (fixed-width) file, then using SQL*Loader up into Oracle.

> (Access via ODBC is ungodly slow)

> 


<snip>

-- 
+---------------------------=*=---------------------------+
| Views expressed by Mr. Gidman are not necessarily those |
* of Pharmacia.  To reply to Mr. Gidman directly,  remove *
| the  first  five  characters  of  the  return   domain. |
+---------------------------=*=---------------------------+