VB6/VBA Creating Access Database via Code

[Siddharth Rout]

Inspired by this thread, I finally decided to put together a seperate thread on this.

You can create an MDB Access database either with DAO or ADO. DAO is almost obselete now but some people still use it so I am including both the codes.

ADO

Set Reference to the following
Microsoft ActiveX Data Objects x.xx Library
Microsoft ADO Ext. x.xx for DDL and Security

Sub ADOSample()
    Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table
    Dim tblCollection As Collection
    Dim Filenm As String, strConn As String
    
    '~~> MDB to be created. Change this to relevant path and filename
    Filenm = "C:\Temp\MyDatabase.mdb"
    
    '~~> Creating an instance of the ADOX-object.
    Set adoCat = New ADOX.Catalog
    '~~> Creating an instance of Collection-object.
    Set tblCollection = New Collection
    Set adoTable = New ADOX.Table
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Filenm & ";"
    
    '~~> Deleting the existing copy of the database, if any.
    On Error Resume Next
    Kill Filenm
    On Error GoTo 0
     
    '~~> Create the MDB.
    adoCat.Create (strConn)
    
    '~~> Replace "Table1" with a name you like for your table
    tblCollection.Add "Table1"
     
    With adoTable
        '~~> Name the table.
        .Name = "Table1"
        '~~> Creating a field which also is the Primary Key
        .Columns.Append "ID", adInteger
        '~~> Set the Parent Catalog.
        .ParentCatalog = adoCat
        .Columns("ID").Properties("AutoIncrement").Value = True
        '~~> Append the PrimaryKey
        .Keys.Append "PrimaryKey", adKeyPrimary, "ID"
                
        '~~> Add rest of the fields... I have taken 4 types
        .Columns.Append "intField1", adInteger  '~~ number
        .Columns.Append "numField2", adNumeric  '~~ number, decimal
        .Columns("numField2").Precision = 2
        .Columns.Append "dateFiled3", adDate    '~~ Date
        .Columns.Append "txtFiled4", adWChar    '~~ text
    End With
    
    '~~> Finally add the Table to the MDB.
    adoCat.Tables.Append adoTable
    
    '~~> Release the objects from the memory.
    Set adoTable = Nothing
    Set tblCollection = Nothing
    Set adoCat = Nothing
     
    '~~> Inform user.
    MsgBox "New .MDB Created - '" & Filenm & "'", vbInformation
End Sub

DAO

Set reference to Microsoft DAO x.x Object Library

Sub DAOExample()
    Dim tdefMDB As TableDef, txtFieldone As Field
    Dim dateFieldone As Field, memoFieldone As Field, dbDatabase As Database
    Dim sNewDBPathAndName As String
    
    '~~> MDB to be created. Change this to relevant path and filename
    sNewDBPathAndName = "C:\Temp\MyDatabase.mdb"
    Set dbDatabase = CreateDatabase(sNewDBPathAndName, dbLangGeneral, dbEncrypt)
 
    '~~> Create new TableDef (I am creating a table Table1)
    Set tdefMDB = dbDatabase.CreateTableDef("Table1")
 
    '~~> Add fields to MDB
    '~~> for eample I am creating One text field, 1 date field and
    '~~> 1 memo field. Amend as applicable
    Set txtFieldone = tdefMDB.CreateField("txtField1", dbText, 20)
    Set dateFieldone = tdefMDB.CreateField("dateField1", dbDate)
    Set memoFieldone = tdefMDB.CreateField("memoField1", dbMemo)
    
    '~~> Append the field objects to the TableDef
    tdefMDB.Fields.Append txtFieldone
    tdefMDB.Fields.Append dateFieldone
    tdefMDB.Fields.Append memoFieldone
 
    '~~> Save TableDef definition by appending it to TableDefs collection.
    dbDatabase.TableDefs.Append tdefMDB
    
    '~~> Inform user.
    MsgBox "New .MDB Created - '" & sNewDBPathAndName & "'", vbInformation
End Sub

Hope this helps...

[dilettante]

I see a few things worth mentioning in the ADO example above:

• ADOX objects should always be late-bound.
• Where the Precision property is being set I think you really wanted the NumericScale property.
• Neither Precision nor NumericScale will work as you use them here.
• Using Jet SQL DDL is generally more versatile than ADOX methods for defining tables.

Microsoft did a lot of work beginning in MDAC 2.5 to maintain multiple ADO interface versions in each new release. For example the msado15.dll for MDAC 2.8 not only has ADO 2.8 interfaces, it also carries them for 2.7, 2.6, 2.5, and 2.1. This means that a program compiled against ADO 2.5 will continue to work on an ADO 2.8 machine. They even did this as MDAC became DAC with the DAC 6.0 in Vista and Windows 7.

This was not being done for ADOX however. In the past (i.e. most of Win2K and WinXP's lives) you always had an ADOX version supporting a single interface. This version had settled in at ADOX 2.8 after some time. When Vista RTM shipped though it only contained an ADOX library supporting the version 6.0 interface.

What this means is that a VB6 program compiled with early binding to ADOX 2.8 on an XP system would not run on Vista RTM (or Vista SP1). And a VB6 program compiled on Vista RTM/SP1 early binding to ADOX 6.0 would not run on any XP (or Win2K, etc.) systems.

In Vista SP2 this was mildly rememdied: there is now a version of the ADOX library exposing both the 6.0 and the 2.8 interfaces, as well as a typelib for the 2.8 interface.

There is still a large "pothole" which are those Vista RTM and SP1 systems in the wild. There are also issues running on Win9x systems with an old MDAC release. You can still run across XP systems locked in at ADOX 2.6. DO NOT EARLY BIND ADOX.


As for Precision (number of digits) and NumericScale (number of dgits to the right of the decimal point), the documentation states:

NumericScale is read-only for Column objects already appended to a collection.

NumericScale Property (ADOX)

The same comment applies to Precision. Maybe the documentation is wrong? I haven't tested this but I suspect it is silently failing as you've used it in your example.


Using SQL DDL can be a lot more expressive as well as easier to read. It also makes it easier to create inter-table relationships so that we are not treating a Jet MDB as just some collection of ISAM files. You can also define views and stored procedures:

    With cnDB
        .Execute "CREATE TABLE Fruits (" _
               & "FruitID IDENTITY NOT NULL CONSTRAINT PK_FruitID PRIMARY KEY," _
               & "Fruit TEXT(50) WITH COMPRESSION NOT NULL UNIQUE" _
               & ")", _
                 , adCmdText
        .Execute "CREATE TABLE Pies (" _
               & "PieID IDENTITY NOT NULL CONSTRAINT PK_PieID PRIMARY KEY," _
               & "Pie TEXT(50) WITH COMPRESSION NOT NULL," _
               & "FruitID INTEGER NOT NULL CONSTRAINT FK_FruitID " _
               & "REFERENCES Fruits (FruitID)" _
               & ")", _
                 , adCmdText
        .Execute "CREATE VIEW PiesView (ID, Pie, Fruit) AS " _
               & "SELECT PieID AS ID, Pie, Fruit " _
               & "FROM Pies LEFT OUTER JOIN Fruits " _
               & "ON Pies.FruitID = Fruits.FruitID", _
                 , adCmdText
        .Execute "CREATE PROC InsertPie(NewPie TEXT(50), FruitName TEXT(50)) AS " _
               & "INSERT INTO Pies (Pie, FruitId) " _
               & "SELECT NewPie, Fruits.FruitId FROM Fruits " _
               & "WHERE Fruit = FruitName", _
                 , adCmdText
    End With

[Amerigo]

I am finding this whole database issue to be very confusing. I have a database for my app called ImageDatabase.mdb for storing images. Nothing I have found about creating a database programmatically addresses the types required for images. I get a lot of errors when coping to my code as well. The data base I have was downloaded from elsewhere, but after initial use, it's too big to upload. I am therefore trying to be able to upload my app w/out the database and have the program create it the first time it's ran.
The database has a table called Pic and 3 Fields?, Columns?, or Rows? named "ID", "Picture", & "Name".

Here's what I have so far:

Private Sub Album_Load......

        If Not IO.File.Exists(Application.StartupPath & "\ImageDatabase.mdb") Then
            CreateDatabase()
        End If
end sub

    Private Sub CreateDatabase()

    End Sub

Alternatively, if possible, I would prefer to have the existing database "deflate" when deleting images.

Any help would be greatly appreciated.

[dilettante]

"Deflation" would be a function of a Compact and Repair operation. This can be done via DAO, JRO, or by using Microsoft's JetComp.exe utility.

Most people store images as a field of type adLongVarBinary... when they actually put them in the database. Of course this is strongly advised againt for many reasons, and most people create a unique name to store the image as a file in a folder related to the database and only store this filename in the database itself.

[bPrice]

Dim tblCollection As Collection

Can't see what is this variable used for.
I deleted everything about it and the code still works fine.

-WIN7 OS, VB6

[georgekar]

?&Amerigo
You can pass dib data to a string I do that in M2000 code, you can find in the signature. You have to include cDIBsection class (also define a MDM() global array, usedfor printer properties..If you forget it then an error occur..to remind you). Then search the code for DIBtoSTR and the reverse function. You can place a the bits without compression. You can use it for mini portraits, but a database isn't a store for images.

The other way is to make links to images...and If you want to "lock" that images, you can scramble the first 100 to 1000 bytes with an XOR function. So when you like to use one, you do the reverse, you open it, and do the reverse again..to scramble it.

You gain from cDIBsection class because you get some functions to display images with various ways (scale, auto scale with fit in, auto scale with crop). These are old routines, but runs good in vb6. Look that as an idea..

[Schmidt]

Can't see what is this variable used for.
I deleted everything about it and the code still works fine.

-WIN7 OS, VB6

It's not used - though I'd consider what dilettante already said -
all you need from ADOX (when dealing with JET-DBs) will be the DB-Create-Call,
which will boil down to this single line here:

CreateObject("ADOX.Catalog").Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileNameOfNewJetDB

All the rest (e.g. Table-, or Index-creation) is better approached using DDL-SQL-Statements,
(per: Cnn.Execute SQL_DDL_String)
learning how to formulate "Create Table ..." or "Create Index ..." or "Alter Table ... Add Column ..."
will help you with a whole lot of other DB-Engines too ... all will support DDL.

Olaf

[bPrice]

all you need from ADOX (when dealing with JET-DBs) will be the DB-Create-Call,

CreateObject("ADOX.Catalog").Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileNameOfNewJetDB

Olaf

Duly noted.

All the rest (e.g. Table-, or Index-creation) is better approached using DDL-SQL-Statements,
(per: Cnn.Execute SQL_DDL_String)
learning how to formulate "Create Table ..." or "Create Index ..." or "Alter Table ... Add Column ..."
will help you with a whole lot of other DB-Engines too ... all will support DDL.

Olaf

And thanks for the guidance here. I also checked definition for DML, DDL, DCL and TCL.

Improviding gradually.

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

[Schmidt]

A nice *.mdb file to play around with, is the "official NorthWind.mdb" ... and I've just uploaded a small
DDL-Schema-generator (which scans an existing *.mdb, and creates the appropriate DDL-representations
of the Tables, Indexes and Views it contains) here into the CodeBank, under the following Link:
http://www.vbforums.com/showthread.p...created-*-mdb)

Olaf