Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 27th, 2003, 12:28 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to create database (.mdb) file in vb6 code

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

And what is Compact in database.

Reply With Quote
  #2 (permalink)  
Old November 27th, 2003, 02:04 AM
Imar's Avatar
Wrox Author
Points: 65,751, Level: 100
Points: 65,751, Level: 100 Points: 65,751, Level: 100 Points: 65,751, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 15,982
Thanks: 64
Thanked 1,351 Times in 1,331 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old November 28th, 2003, 07:42 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank u.
I will go through it.

Reply With Quote
  #4 (permalink)  
Old November 28th, 2003, 08:35 AM
joefawcett's Avatar
Wrox Author
Points: 9,712, Level: 42
Points: 9,712, Level: 42 Points: 9,712, Level: 42 Points: 9,712, Level: 42
Activity: 7%
Activity: 7% Activity: 7% Activity: 7%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,066
Thanks: 1
Thanked 36 Times in 35 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old January 12th, 2004, 10:45 AM
Registered User
 
Join Date: Jan 2004
Location: Aguadilla, PR, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old January 12th, 2004, 10:48 AM
Imar's Avatar
Wrox Author
Points: 65,751, Level: 100
Points: 65,751, Level: 100 Points: 65,751, Level: 100 Points: 65,751, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 15,982
Thanks: 64
Thanked 1,351 Times in 1,331 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old January 12th, 2004, 10:18 PM
Registered User
 
Join Date: Jan 2004
Location: Aguadilla, PR, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old January 13th, 2004, 06:02 AM
Imar's Avatar
Wrox Author
Points: 65,751, Level: 100
Points: 65,751, Level: 100 Points: 65,751, Level: 100 Points: 65,751, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 15,982
Thanks: 64
Thanked 1,351 Times in 1,331 Posts
Default

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.
Reply With Quote
  #9 (permalink)  
Old January 14th, 2004, 10:16 AM
Registered User
 
Join Date: Jan 2004
Location: Aguadilla, PR, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old March 30th, 2004, 12:09 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Imar. Just what I was looking for!

Regards,
Mahesh
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open mde or mdb access file from vb6 gorgo Pro VB 6 1 February 20th, 2006 12:39 AM
Passwrd protected mdb file from Crystl Rpt in vb6 Kaustav Pro VB Databases 1 October 24th, 2005 08:08 PM
to print a file thourgh vb6 code vinod_jsr VB How-To 1 April 21st, 2005 02:59 PM
Clearing all records in an .mdb table from VB6 rwheeler Pro VB Databases 5 December 9th, 2004 02:23 PM
How can I create a query to database file by code. marksu Pro VB Databases 0 April 17th, 2004 05:55 AM



All times are GMT -4. The time now is 01:18 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.