Re: Extracting data from MS Access

Chuck Hamilton wrote:
>
> Is there a way to do an unattended extract of data directly from an
> Access database into an ORACLE database?
> --
> Chuck Hamilton
> chuckh_at_dvol.com
>
> Never share a foxhole with anyone braver than yourself!Chuck,
I'll assume you have an ODBC connection to Oracle. If not, this will need to be your first step. I would suggest creating a table, call it tblMetaTable that contains a list of the names of all of the tables or queries that you want to extract. Then write some Access Basic code that loops through each of the tables and exports them via ODBC to Oracle. You then could set up a form that has a button to click when you want to run the export. Here's some code for the extract: Good luck, Charlie

Function modExport (strStudy As String)

    Dim strODBC As String

    Dim STRTABLE As String
    Dim db1 As Database
    Dim dyn1 As Recordset

        'open current database
    Set db1 = dbengine.Workspaces(0).Databases(0)     

    Set dyn1 = db1.OpenRecordset("tblMetaTable", DB_OPEN_DYNASET)

    dyn1.MoveFirst
    Do Until dyn1.eof = True

        STRTABLE = dyn1.[table name]

	'use the oracle odbc specifications
        strODBC = "ODBC;DSN=oracle odbc"
    
        Debug.Print STRTABLE
        DoCmd SetWarnings False
        DoCmd TransferDatabase A_EXPORT, "<SQL Database>", strODBC, 
A_TABLE, STRTABLE, STRTABLE
        
        DoCmd SetWarnings True

    dyn1.MoveNext
    Loop

End Function Received on Tue Apr 30 1996 - 00:00:00 CEST