Create And Manage Databases

[cleverboy81]

Hi,

I need to create a DB directely from VB6... is it possible?

Can I create also different tables and queries?

Thanx!

[Hack]

Private NewDef As TableDef
Private NewField As Field
Private SQL As String
 
'LocalDB is a database object
 
      SQL = "CREATE TABLE ReportTable "
      LocalDb.Execute SQL
 
      With LocalDB
         Set NewDef = .TableDefs!ReportTable
         Set NewField = NewDef.CreateField("SerialNumber", dbText, 15)
           NewField.AllowZeroLength = True
           NewDef.Fields.Append NewField
         Set NewField = NewDef.CreateField("ModelCode", dbText, 15)
           NewField.AllowZeroLength = True
           NewDef.Fields.Append NewField
         Set MyField = NewDef.CreateField("AcceptBuildStatus", dbText, 8)
           NewField.AllowZeroLength = True
           NewDef.Fields.Append NewField
         Set MyField = NewDef.CreateField("Station", dbText, 8)
           NewField.AllowZeroLength = True
           NewDef.Fields.Append NewField
         Set MyField = NewDef.CreateField("BuildTask", dbText, 8)
           NewField.AllowZeroLength = True
           NewDef.Fields.Append NewField
       End With

[honeybee]

If you are using Access and DAO, you can use the following methods to create various objects:

DBEngine.CreateDatabase() to create a database
TableDefs collection to manage tables. The Add method of this collection lets you add a table to the collection, and in turn to the database, which eliminates the need for the "Create Table" SQL statement.
Fields collection of a TableDef object lets you manage the fields in a table. The Add method of this collection lets you add a field to the collection, in turn to the respective table.

In DAO, a database is represented by the Database object. You can create an empty database using the CreateDatabase method mentioned above. Use of DBEngine is optional. So you could just write CreateDatabase() instead of DBEngine.CreateDatabase().

Each table in an Access database (including system tables) is represented by a TableDef object. For each database there is a TableDefs collection which lists out all the tables in the database.

Each TableDef object has a Fields collection which holds various Field objects. Each Field object represents one field of the table.

Each TableDef object can also hold an Indexes collection, which contains the various Index objects. Each Index object represents a specific index, either on one field or more than one fields, in the table.

Then there is a QueryDef object that represents a query stored in the Access database.

.

[cleverboy81]

Thanks to everybody, but I need something clearer... I'm not so expert in databases managing.

What do you mean with DBEngine?

Please add some code to explain what I have to do...

Before adding a row like CreateDatabase(), what have I to write down? What kind of refs do I need to select?

Be patient!

[RSINGH]

Do you have a DBMS to work against?

[honeybee]

Originally posted by cleverboy81
Thanks to everybody, but I need something clearer... I'm not so expert in databases managing.

What do you mean with DBEngine?

Please add some code to explain what I have to do...

Before adding a row like CreateDatabase(), what have I to write down? What kind of refs do I need to select?

Be patient!

With VB, you can use a number of techniques to connect to a variety of databases. The techniques can be DAO (Data Access Objects), RDO (Remote Data Objects) or ADO (ActiveX Data Objects) among the most popular, and the databases could be simple text files, Excel spreadsheets, MS Access databases (the most common), SQL Server or Oracle or even dBase.

The two posts above, mine and Hack's, talk about VB and MS Access using DAO.

Since DAO, RDO, ADO all are COM structures, they have different components arrenged in a hierarchy. So a Database component/object sits on top of all TableDef objects, a Field object is under a TableDef object and so on. The "mother of all objects" or the root of the DAO object hierarchy is the DBEngine object.

In order to use the DAO technology, you have to add a reference to the appropriate DAO library availble on your machine. With VB5/6, you will typically have DAO 3.51 object library (which can connect to an Access 97 database), and if you have installed SP3 or higher, you will have DAO 3.6 (which can connect to Access 97 as well as Access 2000 databases).

Once you add a reference to the DAO library, you can start using the various objects in the DAO COM hierarchy. The usual practice of using the objects is to specify a reference variable, and then create a new instance of an object and assign that instance to the reference variable:

Dim myDB As DAO.Field 'Declaring reference variable
Set myDB = New DAO.Field 'Creating a new instance
          'of the object and assigning it to the reference variable

However, the DBEngine object does not need such instantiation. You can directly use it in code, as if an object of it was already created. Therefore you don't have to create a new instance of it using statements like : Set myEngine = New DBEngine. Simply use it in the code.

.