Creating a Microsoft Database (MDB) and add tables, fields to it on the fly

  • Creating a Microsoft Database (MDB) and add tables, fields to it on the fly.


    A common misunderstanding is that the file format MDB only can be handled with MS Access and therefore it’s referred to as “Access-database”.


    However, per se MDB is associated with the Microsoft Jet Database Engine. The Jet Database Engine itself is shipped with Windows and other Microsoft softwares like Office et al. MS Access is only one of several softwares that can manipulate the Database Engine.


    What MS Access provide us with is a user friendly UI and tools to work with MDBs but as we know Excel and other softwares can easily create UI for working with the data stored in the MDBs.


    In this post I’ll show how we can create a MDB on the fly, populate it with tables and fields as well as manipulating some properties of the tables and fields.


    Since OzGrid is first of all an Excel-forum the example is based on that we have added the below information about the tables and the fields we want to append in a worksheet:

    • Table names in the C-column
    • Field names in the D-column
    • Datatype in the E-column
    • Format (if any) in the F-column – Not used in the example
    • Number of digits for the datatype Decimal in the G-column



    What we need:
    * Microsoft Windows 2000 and later
    * Microsoft Excel 2000 and later
    * MDAC 2.5 and later


    Step 1
    Add references to the following external libraries via the command Tools | References… in the VB-editor:
    * Microsoft Ext. ADO 2.5 for DDL Security and later.
    * Microsoft ActiveX Data Object 2.5 Library and later.


    Step 2
    Add a standard module to the workbook and then add the following procedure:



    If we need we can also append foreign keys and also create relations between the tables. The above example shows how easily and fast we can create temporarily as well as permanent MDBs for different purposes.

  • Re: Creating a Microsoft Database (MDB) and add tables, fields to it on the fly


    Quote

    but how would I add data


    Well, normally we might use Access & enter data via a form :?


    However, you could also use a form from Excel if you do not have Access - you would then need to use a SQL INSERT statement to enter data into the tables via VBA & ADO

  • Re: Creating a Microsoft Database (MDB) and add tables, fields to it on the fly


    I am trying to convert some of my automation from text files to .BD format.
    But I am having trouble with saving to .MDB.
    I can read with queries, but not write.
    THanks...