Import Specifications

[exm206]

I do a lot of importing text files into Access databases. A lot of times I need to import the same large file into different databases. I end up having to create the same import specifications in different databases. To make a long story short, it would make my life a whole lot easier if I could import the import specifications I already created into another database. Does anyone know if this possible? Thanks for your help.

 

[smozgur]

Hi,

1- Open the target database.
2- Tools->Options->View : Check "System Objects" to show system objects.
3- Goto Tables
4- See MSysIMEXSpecs and make sure it is empty table (if it is not empty then it means you have saved specifications in this database file) (Also If it doesn't exist then it means there is no specification saved)
5- Delete MSysIMEXSpecs table if exists
6- File->Get External Data->Import : Select database file which has specifications that you want to copy
7- Select MSysIMEXSpecs table to import
8- Click OK to import.

Now you have all specifications imported.

This is already possible by using code in VB(A): This code is supposed to be used in the target database file VBA module (You can goto VBA by pressing Alt+F11 in Office Applications and use Insert->Module to insert a new module).

Sub ImportSpecs()
    ' C:\SourceDB.mdb is the source database which has specifications
    ' All existing specifications (if any) will be deleted in target database
    CurrentDb.TableDefs.Delete "MSysIMEXSpecs"
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\SourceDB.mdb", acTable, "MSysIMEXSpecs", "MSysIMEXSpecs"
End Sub

It is also possible to use this more than one database (say all databases in a specific folder - for example C:\TargetDBPath\)

You can run the following code in Excel/Word VBA or in an Access database which is not the Source database has specifications (a temporary database). You should change the source database file name and target files' path in code with yours.

Please make sure that you get all databases backups before running this code. Better you can try it in a sample folder with a few sample database. Remember, it will REMOVE database specifications from the target database file if any already exists.

Sub GetSpecs()
Dim accApp As Object
Dim SourceDBName As String
Dim DbPath As String
Dim CurrDB As String

    SourceDBName = "C:\SourceDB.mdb"
    DbPath = "C:\TargetDBPath\"  'Make sure about last slash
    
    Set accApp = CreateObject("Access.Application")
    CurrDB = Dir(DbPath & "*.mdb", vbNormal)
    Do Until Len(CurrDB) = 0
        If Not DbPath & CurrDB = SourceDBName Then
            accApp.OpenCurrentDatabase DbPath & CurrDB
            On Error GoTo errhandler
            accApp.CurrentDb.TableDefs.Delete "MSysIMEXSpecs"
            accApp.DoCmd.TransferDatabase acImport, "Microsoft Access", SourceDBName, acTable, "MSysIMEXSpecs", "MSysIMEXSpecs"
            accApp.CloseCurrentDatabase
            CurrDB = Dir()
        End If
    Loop
    accApp.Quit
    MsgBox "Done", vbOKOnly + vbInformation, "Error"
    Exit Sub
errhandler:
    If Err = 3265 Then
        Resume Next
    Else
        MsgBox Err & " - " & Err.Description, vbOKOnly + vbExclamation, "Error"
        If Not accApp Is Nothing Then accApp.Quit
    End If
End Sub

I hope these help.
Suat

 

[smozgur]

If you need to see something about this issue:

http://www.mvps.org/access/general/gen0035.htm
http://www.hammerdata.com/Newsgrp/292.html

And lots of others, I don't know if one of them has a code to do that for more than one db at once but my code basically does Import automaticially that you would do manually as described.

Suat