-
Access
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
-
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.
Phil Weber
http://www.philweber.com
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
-
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:
Code:
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
Similar Threads
-
By Sunil Menon in forum Web
Replies: 1
Last Post: 07-26-2002, 09:00 PM
-
By Ray Clough in forum Database
Replies: 7
Last Post: 09-06-2001, 02:27 PM
-
By Nathan in forum Enterprise
Replies: 0
Last Post: 09-06-2001, 06:18 AM
-
By David Jones in forum Database
Replies: 5
Last Post: 09-02-2001, 03:17 AM
-
By Todd B - Agendum Software in forum vb.announcements
Replies: 0
Last Post: 12-20-2000, 12:22 PM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|