Creating Multiple MS Access Tables

[Guest]

I have given my users the ability to create a new MS Access database. I want
to do this through code (VB.Net 2005). I am able to create the DB, add 1
table, and add constraints to it using SQL syntax in one pass. However this
DB has 10 tables in it and i want to add all 10 through one Try/Catch/Finally
pass. I've tried various combinations of syntax but have come up short. All
the documentation seems to tell me how to add one at a time.
BTW - I am adding contraints in a second pass so any help with adding
multiple constraints would be greatly appreciated.
Here is what i have so far for adding the first table & it works, adding the
second table in the same pass it the problem.

sCreateString = sCreateString & ";Persist Security Info=False"

Dim NewTabConn As New System.Data.OleDb.OleDbConnection
Dim NewTabCmd As OleDbCommand = New OleDbCommand
Dim AlterTabCmd As OleDbCommand = New OleDbCommand

NewTabConn = New OleDbConnection(sCreateString)
NewTabCmd.Connection = NewTabConn <--New table Command
AlterTabCmd.Connection = NewTabConn <--Alter table
Command

NewTabCmd.CommandText = "CREATE TABLE Committees " & _
"(Member_ID Long, " & _
"Committee TEXT(25)) " & _
"union all " & _ <--Desperate attemt to
try anything
"CREATE TABLE Family " & _
"(FamilyNO IDENTITY, " & _
"FamilyName TEXT(50))"

AlterTabCmd.CommandText = "Alter Table Committees " & _
"Add CONSTRAINT [PK_Committees] PRIMARY KEY (Member_ID, Committee) "

NewTabConn.Close()
Try
NewTabConn.Open()
NewTabCmd.ExecuteNonQuery()
AlterTabCmd.ExecuteNonQuery()
NewTabConn.Close()
Catch ex As Exception
MessageBox.Show("could not create table" & ex.ToString, "Create
table", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Thanks
Gary

 

[Paul Clement]

¤ I have given my users the ability to create a new MS Access database. I want
¤ to do this through code (VB.Net 2005). I am able to create the DB, add 1
¤ table, and add constraints to it using SQL syntax in one pass. However this
¤ DB has 10 tables in it and i want to add all 10 through one Try/Catch/Finally
¤ pass. I've tried various combinations of syntax but have come up short. All
¤ the documentation seems to tell me how to add one at a time.
¤ BTW - I am adding contraints in a second pass so any help with adding
¤ multiple constraints would be greatly appreciated.
¤ Here is what i have so far for adding the first table & it works, adding the
¤ second table in the same pass it the problem.
¤
¤ sCreateString = sCreateString & ";Persist Security Info=False"
¤
¤ Dim NewTabConn As New System.Data.OleDb.OleDbConnection
¤ Dim NewTabCmd As OleDbCommand = New OleDbCommand
¤ Dim AlterTabCmd As OleDbCommand = New OleDbCommand
¤
¤ NewTabConn = New OleDbConnection(sCreateString)
¤ NewTabCmd.Connection = NewTabConn <--New table Command
¤ AlterTabCmd.Connection = NewTabConn <--Alter table
¤ Command
¤
¤ NewTabCmd.CommandText = "CREATE TABLE Committees " & _
¤ "(Member_ID Long, " & _
¤ "Committee TEXT(25)) " & _
¤ "union all " & _ <--Desperate attemt to
¤ try anything
¤ "CREATE TABLE Family " & _
¤ "(FamilyNO IDENTITY, " & _
¤ "FamilyName TEXT(50))"
¤
¤ AlterTabCmd.CommandText = "Alter Table Committees " & _
¤ "Add CONSTRAINT [PK_Committees] PRIMARY KEY (Member_ID, Committee) "
¤
¤ NewTabConn.Close()
¤ Try
¤ NewTabConn.Open()
¤ NewTabCmd.ExecuteNonQuery()
¤ AlterTabCmd.ExecuteNonQuery()
¤ NewTabConn.Close()
¤ Catch ex As Exception
¤ MessageBox.Show("could not create table" & ex.ToString, "Create
¤ table", MessageBoxButtons.OK, MessageBoxIcon.Error)
¤
¤ End Try


It might help us understand the problem better if you could identify it. ;-)

Are you getting an error? If so, what is it? On what line does the error occur?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Paul
I have been working on the command text of my table creation command:
NewTabCmd.CommandText = "CREATE TABLE Committees " & _
"(Member_ID Long, " & _
"Committee TEXT(25)) " & _
"union all " & _ <--Desperate attemt to
try anything
"CREATE TABLE Family " & _
"(FamilyNO IDENTITY, " & _
"FamilyName TEXT(50))"
When this runs I get a general Syntax error, nothing more specific. So i
have been monkeying with the syntax for quite a while to no avail. My example
here is just one rendidtion of my attempting to correct this. So i am looking
for a little help in refining my command text.
Hope this helps, thanks
Gary

¤ I have given my users the ability to create a new MS Access database. I want
¤ to do this through code (VB.Net 2005). I am able to create the DB, add 1
¤ table, and add constraints to it using SQL syntax in one pass. However this
¤ DB has 10 tables in it and i want to add all 10 through one Try/Catch/Finally
¤ pass. I've tried various combinations of syntax but have come up short. All
¤ the documentation seems to tell me how to add one at a time.
¤ BTW - I am adding contraints in a second pass so any help with adding
¤ multiple constraints would be greatly appreciated.
¤ Here is what i have so far for adding the first table & it works, adding the
¤ second table in the same pass it the problem.
¤
¤ sCreateString = sCreateString & ";Persist Security Info=False"
¤
¤ Dim NewTabConn As New System.Data.OleDb.OleDbConnection
¤ Dim NewTabCmd As OleDbCommand = New OleDbCommand
¤ Dim AlterTabCmd As OleDbCommand = New OleDbCommand
¤
¤ NewTabConn = New OleDbConnection(sCreateString)
¤ NewTabCmd.Connection = NewTabConn <--New table Command
¤ AlterTabCmd.Connection = NewTabConn <--Alter table
¤ Command
¤
¤ NewTabCmd.CommandText = "CREATE TABLE Committees " & _
¤ "(Member_ID Long, " & _
¤ "Committee TEXT(25)) " & _
¤ "union all " & _ <--Desperate attemt to
¤ try anything
¤ "CREATE TABLE Family " & _
¤ "(FamilyNO IDENTITY, " & _
¤ "FamilyName TEXT(50))"
¤
¤ AlterTabCmd.CommandText = "Alter Table Committees " & _
¤ "Add CONSTRAINT [PK_Committees] PRIMARY KEY (Member_ID, Committee) "
¤
¤ NewTabConn.Close()
¤ Try
¤ NewTabConn.Open()
¤ NewTabCmd.ExecuteNonQuery()
¤ AlterTabCmd.ExecuteNonQuery()
¤ NewTabConn.Close()
¤ Catch ex As Exception
¤ MessageBox.Show("could not create table" & ex.ToString, "Create
¤ table", MessageBoxButtons.OK, MessageBoxIcon.Error)
¤
¤ End Try


It might help us understand the problem better if you could identify it. ;-)

Are you getting an error? If so, what is it? On what line does the error occur?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Paul Clement]

¤ Paul
¤ I have been working on the command text of my table creation command:
¤ NewTabCmd.CommandText = "CREATE TABLE Committees " & _
¤ "(Member_ID Long, " & _
¤ "Committee TEXT(25)) " & _
¤ "union all " & _ <--Desperate attemt to
¤ try anything
¤ "CREATE TABLE Family " & _
¤ "(FamilyNO IDENTITY, " & _
¤ "FamilyName TEXT(50))"
¤ When this runs I get a general Syntax error, nothing more specific. So i
¤ have been monkeying with the syntax for quite a while to no avail. My example
¤ here is just one rendidtion of my attempting to correct this. So i am looking
¤ for a little help in refining my command text.
¤ Hope this helps, thanks
¤ Gary

AFAIK you probably will need to execute the DDL statements one at a time. I don't believe it's
possible to execute multiple statements in one call.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Paul,
Thanks for your response. Over the past few days i created a way to do it. I
created a string array, which is my command text to create each table, for
the number of tables to be created. In a for/next loop I incremented the
index for each command text in a single try/catch statement. It might be a
bit cludgey, but it works as expected, so i'm satisfied.
Gary