Looking for MS Access VBA code to import a table from another .mdb

Posted on 2004-11-12
Last Modified: 2012-05-05
I am looking for Access VBA code that will import a table from another Access mdb.

After importing the table, rename the table and then add a new column to that table all with VBA code so that it can be automated.

Any help would be great.

Question by:DougR_MS
    LVL 1

    Accepted Solution

    You can use the TransferDatabase method
    After that you can use the createfield method

    This is from the Access '97 documentation:

    The following example imports the NW Sales for April report from the Microsoft Access database NWSales.mdb into the Corporate Sales for April report in the current database:

    DoCmd.TransferDatabase acImport, "Microsoft Access", _
          "C:\DBS\NWSales.mdb", acReport, "NW Sales for April", _
          "Corporate Sales for April"


    DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

    The TransferDatabase method has the following arguments.

    Argument      Description
                   One of the following intrinsic constants:
    acImport (default)
    If you leave this argument blank, the default constant (acImport) is assumed.

          A string expression that's the name of one of the types of databases you can use to import, export, or link data. In the Macro window, you can view the database types in the list for the Database Type action argument of the TransferDatabase action.
          A string expression that's the full name, including the path, of the database you want to use to import, export, or link data.
                  One of the following intrinsic constants:
    acTable (default)
                  A string expression that's the name of the object whose data you want to import, export, or link.
                  A string expression that's the name of the imported, exported, or linked object in the destination database.
                  Use True (–1) to import or export only the structure of a database table. Use False (0) to import or export the structure of the table and its data.
    If you leave this argument blank, the default (False) is assumed.
                  Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password.
    If you leave this argument blank, the default (False) is assumed.

    And to create a new field:

          Dim tdfNew As TableDef

          Set tdfNew = currentdb.tabledefs("Your imported table name")

          ' Create and append new Field objects for the TableDef object.
          With tdfNew
                ' The CreateField method will set a default Size
                ' for a new Field object if one is not specified.
                .Fields.Append .CreateField("TextField", dbText)
                                    .Fields.Append .CreateField("IntegerField", dbInteger)
                .Fields.Append .CreateField("DateField", dbDate)
          End With


    Good Luck
    LVL 39

    Expert Comment

    Public Function ImportTable()

    Dim tdf as DAO.TableDef
    Dim fld As DAO.Field

    'use transferdatabase to pull the table, fill in parameters as appropriate
    DoCmd.TransferDatabase ....

    'get the table object
    Set tdf = CurrentDB.TableDefs("MyTable")

    'rename the table object
    tdf.Name = "MyNewTable"

    'crate a new field object
    Set fld = tdf.CreateField("MyField", dbText)

    'add the field object to your table object
    tdf.Fields.Append fld

    Set fld = Nothing
    Set tdf = Nothing

    End Function


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now