Create a DB from VB code????

[John T. Howard]

Can you issue commands from VB.Net to create and delete databases?

I would like to create, delete, and copy MSAccess tables and/or
databases from code. Can this be done? Can you show, or point me to
some source code? I'm a newbie.

TIA

John

[Tarren]

you can create the database through code witt Transact-SQL commands. Look
it up in google. I think for access you will either to have a blank
database file already, or you would have to have appropriate privileges to
the file system in order to create a new database.

"John T. Howard" <jo*************@comcast.net> wrote in message
news:kn********************************@4ax.com...

Can you issue commands from VB.Net to create and delete databases?

I would like to create, delete, and copy MSAccess tables and/or
databases from code. Can this be done? Can you show, or point me to
some source code? I'm a newbie.

TIA

John

[Eric Lemmon]

Hi John,

As Tarren mentioned, you can use T-SQL statements to create your database. However, T-SQL is specific to SQL Server.

You can create Access databases purely at run-time, but not with native ..NET code. For some reason unbeknown to me, it is not available in the ..NET Framework, but it is available with legacy VB 6 DAO code. Also, assuming you are coding a Windows application, you should not need any heightened file system privileges.

Below is a very simple example (be sure to add a reference to the DAO reference library). I didn't test it, but most of it came from a recent project, so it should work with very little modifications. (Also, watch out for line breaks.)

Take care,

Eric

____________________________________

Dim db As DAO.Database
Dim rs As Recordset
Dim dbc As Connection
Dim tbl As DAO.TableDef
Dim idx As DAO.Index
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim qry As DAO.QueryDef

' Create DB
db = DBEngine.CreateDatabase(App.Path & "\myDatabase.mdb", dbLangGeneral, dbEncrypt)

' Open DB
Set db = DBEngine.OpenDatabase(App.Path & "\myDatabase.mdb")

' Create Customers Table
tbl = db.CreateTableDef("Customers")

' Create an ID field in the Customers table.
fld = tbl.CreateField("CustomerID", dbLong)

' Set schema attributes for the CustomerID column.
fld.Attributes = dbAutoIncrField ' Make CustomerID an AutoNumber field.
tbl.Fields(0).AllowZeroLength = False
tbl.Fields(0).Required = True

' -------------------------------------------------
' INSERT CODE TO ADD MORE COLUMNS
' -------------------------------------------------

' Append the field to the table
tbl.Fields.Append Fld

' Add Companies table to database
db.TableDefs.Append tbl

' Create and append primary key
idx = tbl.CreateIndex("Primary Key")
idx.Primary = True
idx.Fields.Append idx.CreateField("CustomerID")
tbl.Indexes.Append idx

' Create the Customers query
Set qry = db.CreateQueryDef("qryCustomers", _
"SELECT * FROM Customers ORDER BY CustomerID;")

' -------------------------------------------------
' INSERT CODE TO ADD MORE TABLES, ETC.
' -------------------------------------------------

' Create 1:M between [Customers] and [Orders]:
' **********************************************
' (Of course, this assumes Orders table with CustomerID
' column as the foreign key to the Customers table.)

rel = db.CreateRelation()
rel.Name = "CustomersToOrders"
rel.Table = "Customers"
rel.ForeignTable = "Orders"
rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade + dbRelationLeft

' Create linking field:
Fld = rel.CreateField("CustomerID")

' Set ForeignName property of the field to the name of
' the corresponding field in the primary table:
Fld.ForeignName = "CustomerID"
rel.Fields.Append Fld

' Append the relation to the database:
dbs.Relations.Append rel

db.Close

[Eric Lemmon]

Correction...when I said "but it is available with legacy VB 6 DAO code," I really meant that the Access creation functionality is made available through legacy COM. The code itself can be .NET if you wish.

Also, the code I attached was mostly .NET...there are a few holdovers (such as App.Path) from VB 6 that you'll need to fix before compiling.

Hope this helps!

Eric

[Ken Tucker [MVP]]

Hi,

Use ADOX to create an access database.
http://support.microsoft.com/default...b;en-us;317867

Ken
-----------------------
"John T. Howard" <jo*************@comcast.net> wrote in message
news:kn********************************@4ax.com...

Can you issue commands from VB.Net to create and delete databases?

I would like to create, delete, and copy MSAccess tables and/or
databases from code. Can this be done? Can you show, or point me to
some source code? I'm a newbie.

TIA

John