Access

[Spumbu1977]

Hi All,

i am trying to get my program to create a new table each time i request it

but for the life of me i can not figure out or even to begin to imagine how to get vb6 to do this

i usually connect to a database using

Dim conn as adodb.connection
Dim rs as adodb.recordeset
set .. etc

but how do i get the recordset to create a new table

any help is always appreciated

many thanks in advance

Rob

[Phil Weber]

You don't use a recordset to create a table. You may simply use the Execute method of the connection object to execute a CREATE TABLE statement. See http://msdn.microsoft.com/library/en.../acfundsql.asp for more information.

[jggtz]

In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default.
On the Project menu, select References to add the following type libraries:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security
Add two Command buttons (Command1 and Command2) and the following code to the Form1:

Option Explicit:


Private Sub Command1_Click()
'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, _
                            objTable As ADOX.Table

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table

  'Open the connection
  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"

  'Open the Catalog
  Set Cat.ActiveConnection = Cn

  'Create the table
  objTable.Name = "Test_Table"

  'Create and Append a new field to the "Test_Table"
  'Columns Collection
  objTable.Columns.Append "PrimaryKey_Field", adInteger

  'Create and Append a new key. Note that we are merely passing
  'the "PimaryKey_Field" column as the source of the primary key.
  'Thi snew Key will be Appended to the Keys Collection of
  '"Test_Table"
  objTable.Keys.Append "PrimaryKey", adKeyPrimary, _
                       "PrimaryKey_Field"

  'Append the newly created table to the Tables Collection
  Cat.Tables.Append objTable

' clean up objects
  Set objKey = Nothing
  Set objTable = Nothing
  Set Cat = Nothing
  Cn.Close
  Set Cn = Nothing
End Sub

Private Sub Command2_Click()
'
' This code adds a multi-field Primary Key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog
Dim objTable As ADOX.Table, objKey As ADOX.Key

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table
  Set objKey   = New ADOX.Key

  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
  Set Cat.ActiveConnection = Cn
  objTable.Name = "Test_Table2"

  objTable.Columns.Append "PrimaryKey_Field1", adInteger
  objTable.Columns.Append "PrimaryKey_Field2", adInteger

  objKey.Name = "PrimaryKey"
  objKey.Type = adKeyPrimary
  objKey.Columns.Append "PrimaryKey_Field1"
  objKey.Columns.Append "PrimaryKey_Field2"

  objTable.Keys.Append objKey

  Cat.Tables.Append objTable

' clean up objects
  Set objKey   = Nothing
  Set objTable = Nothing
  Set Cat      = Nothing
  Cn.Close
  Set Cn       = Nothing
End Sub


Private Sub Command1_Click()
'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, _
                            objTable As ADOX.Table

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table

  'Open the connection
  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"

  'Open the Catalog
  Set Cat.ActiveConnection = Cn

  'Create the table
  objTable.Name = "Test_Table"

  'Create and Append a new field to the "Test_Table"
  'Columns Collection
  objTable.Columns.Append "PrimaryKey_Field", adInteger

  'Create and Append a new key. Note that we are merely passing
  'the "PimaryKey_Field" column as the source of the primary key.
  'Thi snew Key will be Appended to the Keys Collection of
  '"Test_Table"
  objTable.Keys.Append "PrimaryKey", adKeyPrimary, _
                       "PrimaryKey_Field"

  'Append the newly created table to the Tables Collection
  Cat.Tables.Append objTable

' clean up objects
  Set objKey = Nothing
  Set objTable = Nothing
  Set Cat = Nothing
  Cn.Close
  Set Cn = Nothing
End Sub

Private Sub Command2_Click()
'
' This code adds a multi-field Primary Key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog
Dim objTable As ADOX.Table, objKey As ADOX.Key

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table
  Set objKey   = New ADOX.Key

  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
  Set Cat.ActiveConnection = Cn
  objTable.Name = "Test_Table2"

  objTable.Columns.Append "PrimaryKey_Field1", adInteger
  objTable.Columns.Append "PrimaryKey_Field2", adInteger

  objKey.Name = "PrimaryKey"
  objKey.Type = adKeyPrimary
  objKey.Columns.Append "PrimaryKey_Field1"
  objKey.Columns.Append "PrimaryKey_Field2"

  objTable.Keys.Append objKey

  Cat.Tables.Append objTable

' clean up objects
  Set objKey   = Nothing
  Set objTable = Nothing
  Set Cat      = Nothing
  Cn.Close
  Set Cn       = Nothing
End Sub