importing table from oracle into external access database using TransferDatabase

user_453116 0
Viewed 61

i have a bunch of large Oracle tables that need to be imported into MS Access. They each go into its own database, because they're huge. Right now i'm linking them into the database that runs the procedure and then exporting into external databases using INSERT INTO. Horribly slow. TransferDatabase is much faster but i don't see how i can use that for external databases.

Is there a way? Or is there a way to do this quickly using some other method? This is part of an automation, so it can't be any manual solutions, has to be VBA.

In case someone is wondering why I'm doing this - these are files that go to requestors as results of a report.

ms-access vba oracle
1 Answers

Here's one way you could do it.

Create an Access database called importfile_template.accdb that contains the following:

– a table named import_config with three fields, all ShortText(255)

odbc_connection_string
source_tablename
destination_tablename

– a module containing

Option Compare Database
Option Explicit

Public Function do_import()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim tbd As DAO.TableDef
    On Error Resume Next
    Set tbd = cdb.TableDefs("import_config")
    If Err.Number = 0 Then
        Dim rst As DAO.Recordset
        Set rst = cdb.OpenRecordset("import_config", dbOpenTable)
        DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;" & rst!odbc_connection_string, _
                acTable, rst!source_tablename, rst!destination_tablename
        rst.Close
        Set rst = Nothing
        DoCmd.DeleteObject acTable, "import_config"
        Set cdb = Nothing
        Application.Quit
    End If
End Function

– and a macro named AutoExec that simply does

RunCode (do_import())

Then in your main database you can use VBA code like this:

Sub perform_import()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    
    Dim this_filespec As String
    this_filespec = CurrentProject.FullName
    Dim current_folder As String
    current_folder = Left(this_filespec, InStrRev(this_filespec, "\"))
    Dim import_filespec As String
    import_filespec = current_folder & "zzz_test.accdb"  ' my test setting
    ' VBA project reference required:
    ' Windows Script Host Object Model
    Dim fso As New FileSystemObject
    fso.CopyFile current_folder & "importfile_template.accdb", import_filespec
    Set fso = Nothing
    Dim qdf As DAO.QueryDef
    Set qdf = cdb.CreateQueryDef("")
    qdf.SQL = _
            "UPDATE [" & import_filespec & "].import_config SET " & _
            "odbc_connection_string = prm_odbc, " & _
            "source_tablename = prm_source, " & _
            "destination_tablename = prm_destination"
    qdf!prm_odbc = "DSN=mssqlLocal"        '
    qdf!prm_source = "ThousandRows"        ' my test settings
    qdf!prm_destination = "thousand_rows"  '
    qdf.Execute dbFailOnError
    qdf.Close
    Set qdf = Nothing
    Set cdb = Nothing
    Dim wsh As New WshShell
    wsh.Run import_filespec
    Set wsh = Nothing
End Sub

Note: When opening importfile_template.accdb from within Access be sure to hold down the Shift key to prevent the AutoExec macro from running.

user_21443900
Related