Re: Access-to-Oracle

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Fri, 11 Jan 2002 13:35:42 GMT
Message-ID: <3c3ee81d.280687235@news.alt.net>

On 10 Jan 2002 10:59:01 -0800, astralwarrior_at_hotmail.com (Dan Barron) wrote:

>Thanks to everyone for your kind help.
>
>I'm writing a SQL script to make the tables, and will use Brian's
>suggestion to use insert queries from Access. Time is not really a
>huge concern, as long as it works.
>
>Thanks again!
>
>dan

Assuming the column names are the same, you can actually have a function do the transport. Let's see if I still have mine .....

<time passes>

When you import a table into Access via ODBC, it attaches the schema name (I think) to the beginning of the table name. Here I was actually using Access to transfer data from SQL Server to Oracle<shudder>. The table names were the same.

Function MoveDataToOracle2()

    ' This function will move data from the SQL Server ODBC connection to the Oracle ODBC connection.

    Const PREDICATE_FROM As String = "dbo_"     Const PREDICATE_TO As String = "BRIAN_"         

    Dim ThisDB As Database, Table_Count As Long, Table_Name As String, Length_Of_Predicate_To As Long

    Dim Columns As String, Columns_With_Trim As String, Column_Count As Long, The_Table As TableDef

    Length_Of_Predicate_To = Len(PREDICATE_TO)     

    Set ThisDB = CurrentDb()

    ' Go through all tables looking for the predicate in the name.     For Table_Count = 0 To ThisDB.TableDefs.Count - 1

        Set The_Table = ThisDB(Table_Count)
        
        ' Get the name of the table.
        Table_Name = The_Table.Name

        ' Check if the predicate is in the name.
        If Left(Table_Name, Length_Of_Predicate_To) = PREDICATE_TO

Then

            ' Get the table name itself.
            Table_Name = Mid(Table_Name, Length_Of_Predicate_To + 1)

            ' Make sure that the table (name with the predicate)
exists.
            If IsTable(PREDICATE_FROM & Table_Name) Then

                ' Reset variables for this time around.
                Columns = ""
                Columns_With_Trim = ""

                ' If we're here than both tables exist, so let's move
the data.
                ' First get all the columns in the table so that when
they are SELECTed, they can be trimmed.
                ' This is very helpful as Access does not support
VARCHARs and would just pad the data.
                For Column_Count = 0 To The_Table.Fields.Count - 1
                
                    ' Add the TRIM() functions to apply to all the
columns.
                    Columns_With_Trim = Columns_With_Trim & ", TRIM("
& The_Table(Column_Count).Name & ")"
                    Columns = Columns & ", " &
The_Table(Column_Count).Name
                
                Next Column_Count
                
                ' Finally, remove the first comma, since they are
extra.
                Columns = Mid(Columns, 2)
                Columns_With_Trim = Mid(Columns_With_Trim, 2)
                
                                
                ThisDB.Execute "INSERT INTO " & PREDICATE_TO &
Table_Name & "(" & Columns & ")" _
                             & " SELECT" & Columns_With_Trim & " FROM

" & PREDICATE_FROM & Table_Name, dbSeeChanges

            Else ' IsTable(PREDICATE_FROM & Table_Name)

                ' If it doesn't, than notify the user.
                MsgBox PREDICATE_FROM & Table_Name & " does not

exist.", , "Table Not found."

            End If ' IsTable(Table_Name)

        End If ' Left(Table_Name, Length_Of_Predicate_To) = PREDICATE_TO     Next Table_Count

    Set The_Table = Nothing
    Set ThisDB = Nothing

End Function

It relies on this.

Function IsTable(TableName As String) As Boolean

    ' This function will check if a table exists and reutrn True if it does.     

    Dim ThisDB As Database, Count As Long

    Set ThisDB = CurrentDb()

    For Count = 0 To ThisDB.TableDefs.Count - 1

        If ThisDB.TableDefs(Count).Name = TableName Then

            IsTable = True

            ' Since we have have it, there is no reason to check further.

            Exit For

        End If ' ThisDB(Count).Name = TableName

    Next Count

    Set ThisDB = Nothing

End Function

HTH,
Brian Received on Fri Jan 11 2002 - 07:35:42 CST