CODEWITHSUNDEEP
vbams-accessms-officeetl
sion_corn
sion_corn

Reputation: 3141

DoCmd.TransferText into another DB, using an Import Spec in CurrentDB

The challenge is in the title.

How can I use <DB>.DoCmd.TransferText... to import a file into a separate DB, using an Import Spec in CurrentDB?

Please don't reply if your suggestion is to create a linked table to the destination DB, or to create the import spec in the destination DB.

Here's my code so far, but obviously it does not work because the file spec is in CurrentDB instead of the destination DB.

Option Compare Database

Function job1()

    Dim sFinalDB As String, sTableName As String, sSpecName As String, sFileName As String

    sFinalDB = "path\db.mdb"
    sTableName = "tblName"
    sSpecName = "specName"
    sFileName = "path\test.csv"


    fn_ImportTxt sFinalDB, sSpecName, sTableName, sFileName, True

End Function



Function fn_ImportTxt(sDBPath As String, _
    sSpecName As String, _
    sTableName As String, _
    sFileName As String, _
    bHeaders As Boolean)

    Dim acApp As Access.Application
    Set acApp = New Access.Application

    acApp.OpenCurrentDatabase sDBPath

    acApp.DoCmd.TransferText acImportDelim, sSpecName, sTableName, sFileName, bHeaders

    acApp.CloseCurrentDatabase
    acApp.Quit acQuitSaveNone
    Set acApp = Nothing

End Function

Upvotes: 0

Views: 3111

Answers (2)

Jim Snyder
Jim Snyder

Reputation: 99

I am able to answer "why not create a linked table to the destination DB". I work in an environment where ADO, DAO, ODBC, and any direct access are impossible due to network security. I can do a direct query against the remote DB using a module with the DoCmd method. If network security eliminates that, I will have to scrabble deeper, but I can import data and use a remote query to work on the data.

Upvotes: 0

Derek Cheng
Derek Cheng

Reputation: 525

I stumbled upon this question today trying to do the same thing myself. I found that the specs are stored in two system object tables "MSysIMEXColumns" and "MSysIMEXSpecs". What I did was copying the two system object table to the destination database every time before I run the import procedure, that why the specs are also available in the destination database.

    DoCmd.TransferDatabase acExport, "Microsoft Access", DBPath, acTable, "MSysIMEXColumns", "MSysIMEXColumns"
    DoCmd.TransferDatabase acExport, "Microsoft Access", DBPath, acTable, "MSysIMEXSpecs", "MSysIMEXSpecs"
    Dim NewAccessApp As Access.Application
    Set NewAccessApp = New Access.Application
    With NewAccessApp
        .OpenCurrentDatabase strDBPath, True
        .DoCmd.TransferText acImportDelim, ImportSpec, TabelName, FilePath, True
        .CloseCurrentDatabase
        .Quit
    End With

Obviously this would only work if you don't mind overwriting the spec table in the destination database.

Upvotes: 2

Related Questions