How to Create Database in Vb6

November 27th, 2003, 01:28 AM

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

And what is Compact in database.

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.

November 28th, 2003, 08:42 AM

thank u.
I will go through it.

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

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

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.

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

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.

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

March 30th, 2004, 01:09 AM

Thanks Imar. Just what I was looking for!

Regards,
Mahesh

Brough Blithical1978

How to Create Database in Vb6

Menu Halaman Statis