How to create database (.mdb) file in vb6 code

mrleokarthik · November 27th, 2003, 01:28 AM

How to create MS Access database (.mdb) file in vb6 code.

And what is Compact in database.

Imar · November 27th, 2003, 03:04 AM

Hi there,

You may want to read this article: http://imar.spaanjaars.com/QuickDocID.aspx?QUICKDOC=143

It describes how to create a database in ASP, but the concepts are identical for VB6. Just change lines like this:

Dim catNewDB ' As ADOX.Catalog

to this:

Dim catNewDB As ADOX.Catalog

By removing the ' you're making the code strongly typed. Then, instead of using Server.CreateObject, use CreateObject, or Set your objects to a new instance of your required type (Set catNewDB = New ADOX.Catalog)

If you need help, please post a reply here. I could do a VB6 version of the article.....

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

mrleokarthik · November 28th, 2003, 08:42 AM

thank u.
I will go through it.

joefawcett · November 28th, 2003, 09:35 AM

To compact a Jet database you need Jet Replication Objects (JRE). There are a few articles about them on the web. They used to come with MDAC so long as you have a version before 2.6 I believe.

--

Joe

ecaro · January 12th, 2004, 11:45 AM

Quote:

quote:Originally posted by Imar
Hi there,

You may want to read this article: http://imar.spaanjaars.com/QuickDocID.aspx?QUICKDOC=143

It describes how to create a database in ASP, but the concepts are identical for VB6. Just change lines like this:

Dim catNewDB ' As ADOX.Catalog

to this:

Dim catNewDB As ADOX.Catalog

By removing the ' you're making the code strongly typed. Then, instead of using Server.CreateObject, use CreateObject, or Set your objects to a new instance of your required type (Set catNewDB = New ADOX.Catalog)

If you need help, please post a reply here. I could do a VB6 version of the article.....

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hello Imar,

I try to convert your ASP code in Visual Basic mode. I need your help, because I have problems with Data Types declaration when define the columns the CreateAccessTable routine. Please send me your code in VB mode or write me the solution of this problem to fix it.)

Thankyou,

Edgar Caro

Imar · January 12th, 2004, 11:48 AM

Hi Edgar,

There is no "code in VB" to send you. However, as I suggested, you should be able to change the ASP code to VB code by removing the comments for the strong data type declarations.

What problems are you having? Are you getting any errors?

Maybe you can post the code you have so far so I can take a look at it.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

ecaro · January 12th, 2004, 11:18 PM

Quote:

quote:Originally posted by Imar
Hi Edgar,

There is no "code in VB" to send you. However, as I suggested, you should be able to change the ASP code to VB code by removing the comments for the string data type declarations.

What problems are you having? Are you getting any errors?

Maybe you can post the code you have so far so I can take a look at it.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar,

    This routine works fine "CreateAccessDatabase".
I send you the complete of routine called "CreateAccessTable"
I put an asterisk at the fail line in VB compilation runtime.
This is the error message in the VB msgbox :
  "Runtime error '3001':"
"Arguments are of the wrong type, are out of acceptable range, or are conflict with one another"

******** Check the asterisk at the line **********
    Sub CreateAccessTable(sDatabaseToCreate)
        'Dim catDB ' As ADOX.Catalog
        Dim catDB As Object
        'Set catDB = Server.CreateObject("ADOX.Catalog")
        Set catDB = CreateObject("ADOX.Catalog")
        ' Open the catalog
        catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate
        'Dim tblNew ' As ADOX.Table
        Dim tblNew As Object
        'Set tblNew = Server.CreateObject("ADOX.Table")
        Set tblNew = CreateObject("ADOX.Table")
        tblNew.Name = "Contacts"

        ' First Create an Autonumber column, called ID.
        ' This is just for demonstration purposes.
        ' We could have done this below with all the other columns as well
        'Dim col ' As ADOX.Column
        Dim col As Object
        'Set col = Server.CreateObject("ADOX.Column")
        Set col = CreateObject("ADOX.Column")
        With col
            .ParentCatalog = catDB
            .Type = adText * The error appears when trace here.
            .Name = "ID"
            .Properties("Autoincrement") = True
        End With
        tblNew.Columns.Append col

        ' Now add the rest of the columns
        With tblNew
            ' Create fields and append them to the
            ' Columns collection of the new Table object.
            With .Columns
                .Append "NumberColumn", adinteger *Also the same
                .Append "FirstName", adVarWChar *Also the same
                .Append "LastName", adVarWChar *Also the same
                .Append "Phone", adVarWChar *Also the same
                .Append "Notes", adLongVarWChar *Also the same
                End With

                Dim adColNullable ' Is not defined in adovbs.inc,
                ' so we need to define it here.
                ' The other option is adColFixed with a value of 1
                adColNullable = 2
                With .Columns("FirstName")
                .Attributes = adColNullable
            End With
        End With

        ' Add the new Table to the Tables collection of the database.
        catDB.Tables.Append tblNew
        Set col = Nothing
        Set tblNew = Nothing
        Set catDB = Nothing
    End Sub

**** This is the complete VB program ****

    Sub CreateAccessDatabase(sDatabaseToCreate)
        'Dim catNewDB ' As ADOX.Catalog
        Dim catNewDB As Object
        'Set catNewDB = Server.CreateObject("ADOX.Catalog")
        Set catNewDB = CreateObject("ADOX.Catalog")
        catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate & _
        ";Jet OLEDB:Engine Type=5;"
        ' Engine Type=5 = Access 2000 Database
        ' Engine Type=4 = Access 97 Database
        Set catNewDB = Nothing
    End Sub
    Sub CreateAccessTable(sDatabaseToCreate)
        'Dim catDB ' As ADOX.Catalog
        Dim catDB As Object
        'Set catDB = Server.CreateObject("ADOX.Catalog")
        Set catDB = CreateObject("ADOX.Catalog")
        ' Open the catalog
        catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"_
        & "Data Source=" & sDatabaseToCreate
        'Dim tblNew ' As ADOX.Table
        Dim tblNew As Object
        'Set tblNew = Server.CreateObject("ADOX.Table")
        Set tblNew = CreateObject("ADOX.Table")
        tblNew.Name = "Contacts"

        ' First Create an Autonumber column, called ID.
        ' This is just for demonstration purposes.
        ' We could have done this below with all the other columns as
        ' well
        'Dim col ' As ADOX.Column
        Dim col As Object
        'Set col = Server.CreateObject("ADOX.Column")
        Set col = CreateObject("ADOX.Column")
        With col
            .ParentCatalog = catDB
            .Type = adText
            .Name = "ID"
            .Properties("Autoincrement") = True
        End With
        tblNew.Columns.Append col

        ' Now add the rest of the columns
        With tblNew
            ' Create fields and append them to the
            ' Columns collection of the new Table object.
            With .Columns
                .Append "NumberColumn", adinteger
                .Append "FirstName", adVarWChar
                .Append "LastName", adVarWChar
                .Append "Phone", adVarWChar
                .Append "Notes", adLongVarWChar
                End With

                Dim adColNullable ' Is not defined in adovbs.inc,
                ' so we need to define it here.
                ' The other option is adColFixed with a value of 1
                adColNullable = 2
                With .Columns("FirstName")
                .Attributes = adColNullable
            End With
        End With

        ' Add the new Table to the Tables collection of the database.
        catDB.Tables.Append tblNew
        Set col = Nothing
        Set tblNew = Nothing
        Set catDB = Nothing
    End Sub
Private Sub Form_Load()
    sDatabaseName = "C:\MyNewDatabase.mdb"
    ' First call the Create Database method
    CreateAccessDatabase sDatabaseName
    ' Then add a table and columns to this database
    CreateAccessTable sDatabaseName
    MsgBox "Database has been created successfully!"
End Sub

Thanks,

  Any question reply to me :)

Edgar

Edgar Caro

Imar · January 13th, 2004, 07:02 AM

Hi Edgar,

There are two problems with the solution you have right now.

First of all, you need a reference to ADOX, so stuff like adInteger gets a proper definition. To add the reference, choose Project | References... and then select Microsoft ADO Ext. 2.x for DDL and Security from the list. With the reference, the constants like adInteger and adVarWChar you're using now work.

The second problem is that you're using a constant that doesn't exist. adText is not a valid type for a column. If you're trying to get a Memo field (Text in SQL Server), try adLongVarWChar. If you're trying to get a normal (short) text field, try adVarWChar. In relation to this, you can't use a text type, like adVarWChar, and then expect the Autoincrement property to function correctly. A field with Autoincrement set to True needs to be of a Numeric type.

There is also a third "issue" with the code. By directly converting your code from the ASP code, you're using Late Binding with Objects instead of early binding with strongly typed objects like ADOX.Table. Both options should work fine, but using strongly typed variables performs better and is considered a "better practice". I fixed the code here and there and pasted the new version below.

Hope this helps, and if you have any further questions, let me know.

Cheers,

Imar

Code:

Option Explicit
 Sub CreateAccessDatabase(sDatabaseToCreate)
    Dim catNewDB As ADOX.Catalog
    Set catNewDB = New ADOX.Catalog
    catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate & _
        ";Jet OLEDB:Engine Type=5;"
        ' Engine Type=5 = Access 2000 Database
        ' Engine Type=4 = Access 97 Database
   Set catNewDB = Nothing
End Sub

Sub CreateAccessTable(sDatabaseToCreate)

    Dim catDB As ADOX.Catalog
    Dim tblNew As ADOX.Table
    Set catDB = New ADOX.Catalog

    ' Open the catalog
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate

    ' Create new Table
    Set tblNew = New ADOX.Table
    tblNew.Name = "Contacts"

    ' First Create an Autonumber column, called ID.
    ' This is just for demonstration purposes.
    ' We could have done this below with all the other
    ' columns as well
    Dim col  As ADOX.Column
    Set col = New ADOX.Column

    With col
        .ParentCatalog = catDB
        .Type = adInteger ' adText does not exist
        .Name = "ID"
        .Properties("Autoincrement") = True
        .Properties("Description") = "I am the Description " & _
            "for the column"
    End With
    tblNew.Columns.Append col

     ' Now add the rest of the columns
     With tblNew
         ' Create fields and append them to the
         ' Columns collection of the new Table object.
         With .Columns
             .Append "NumberColumn", adInteger
             .Append "FirstName", adVarWChar
             .Append "LastName", adVarWChar
             .Append "Phone", adVarWChar
             .Append "Notes", adLongVarWChar
             End With

             Dim adColNullable ' Is not defined in adovbs.inc,
             ' so we need to define it here.
             ' The other option is adColFixed with a value of 1
             adColNullable = 2
             With .Columns("FirstName")
             .Attributes = adColNullable
         End With
     End With

     ' Add the new Table to the Tables collection of the database.
     catDB.Tables.Append tblNew
     Set col = Nothing
     Set tblNew = Nothing
     Set catDB = Nothing
End Sub

Private Sub Form_Load()
    Dim sDatabaseName As String
    sDatabaseName = "C:\MyNewDatabase.mdb"
    ' First call the Create Database method
    CreateAccessDatabase sDatabaseName
    ' Then add a table and columns to this database
    CreateAccessTable sDatabaseName
    MsgBox "Database has been created successfully!"
End Sub

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

ecaro · January 14th, 2004, 11:16 AM

Quote:

quote:Originally posted by Imar
Hi Edgar,

There are two problems with the solution you have right now.

First of all, you need a reference to ADOX, so stuff like adInteger gets a proper definition. To add the reference, choose Project | References... and then select Microsoft ADO Ext. 2.x for DDL and Security from the list. With the reference, the constants like adInteger and adVarWChar you're using now work.

The second problem is that you're using a constant that doesn't exist. adText is not a valid type for a column. If you're trying to get a Memo field (Text in SQL Server), try adLongVarWChar. If you're trying to get a normal (short) text field, try adVarWChar. In relation to this, you can't use a text type, like adVarWChar, and then expect the Autoincrement property to function correctly. A field with Autoincrement set to True needs to be of a Numeric type.

There is also a third "issue" with the code. By directly converting your code from the ASP code, you're using Late Binding with Objects instead of early binding with strongly typed objects like ADOX.Table. Both options should work fine, but using strongly typed variables performs better and is considered a "better practice". I fixed the code here and there and pasted the new version below.

Hope this helps, and if you have any further questions, let me know.

Cheers,

Imar

Code:

Option Explicit
 Sub CreateAccessDatabase(sDatabaseToCreate)
    Dim catNewDB As ADOX.Catalog
    Set catNewDB = New ADOX.Catalog
    catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate & _
        ";Jet OLEDB:Engine Type=5;"
        ' Engine Type=5 = Access 2000 Database
        ' Engine Type=4 = Access 97 Database
   Set catNewDB = Nothing
End Sub

Sub CreateAccessTable(sDatabaseToCreate)
     
    Dim catDB As ADOX.Catalog
    Dim tblNew As ADOX.Table
    Set catDB = New ADOX.Catalog
   
    ' Open the catalog
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate
   
    ' Create new Table
    Set tblNew = New ADOX.Table
    tblNew.Name = "Contacts"

    ' First Create an Autonumber column, called ID.
    ' This is just for demonstration purposes.
    ' We could have done this below with all the other
    ' columns as well
    Dim col  As ADOX.Column
    Set col = New ADOX.Column

    With col
        .ParentCatalog = catDB
        .Type = adInteger ' adText does not exist
        .Name = "ID"
        .Properties("Autoincrement") = True
        .Properties("Description") = "I am the Description " & _
            "for the column"
    End With
    tblNew.Columns.Append col

     ' Now add the rest of the columns
     With tblNew
         ' Create fields and append them to the
         ' Columns collection of the new Table object.
         With .Columns
             .Append "NumberColumn", adInteger
             .Append "FirstName", adVarWChar
             .Append "LastName", adVarWChar
             .Append "Phone", adVarWChar
             .Append "Notes", adLongVarWChar
             End With
             
             Dim adColNullable ' Is not defined in adovbs.inc,
             ' so we need to define it here.
             ' The other option is adColFixed with a value of 1
             adColNullable = 2
             With .Columns("FirstName")
             .Attributes = adColNullable
         End With
     End With

     ' Add the new Table to the Tables collection of the database.
     catDB.Tables.Append tblNew
     Set col = Nothing
     Set tblNew = Nothing
     Set catDB = Nothing
End Sub

Private Sub Form_Load()
    Dim sDatabaseName As String
    sDatabaseName = "C:\MyNewDatabase.mdb"
    ' First call the Create Database method
    CreateAccessDatabase sDatabaseName
    ' Then add a table and columns to this database
    CreateAccessTable sDatabaseName
    MsgBox "Database has been created successfully!"
End Sub

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

:)
Great Thanks Imar !. My problem was that I not did a reference to MS ADOX 2.X.

Take care and have a nice day!

Edgar Caro

mmirchandani · March 30th, 2004, 01:09 AM

Thanks Imar. Just what I was looking for!

Regards,
Mahesh