Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Access

  1. #1
    Join Date
    Oct 2005
    Posts
    133

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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!

  3. #3
    Join Date
    Aug 2005
    Location
    Hermosillo, Sonora, Mexico
    Posts
    103
    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

  1. Displaying the access key assignments
    By Sunil Menon in forum Web
    Replies: 1
    Last Post: 07-26-2002, 10:00 PM
  2. access vs SQL Server
    By Ray Clough in forum Database
    Replies: 7
    Last Post: 09-06-2001, 03:27 PM
  3. Secure registry for remote access
    By Nathan in forum Enterprise
    Replies: 0
    Last Post: 09-06-2001, 07:18 AM
  4. ..or maybe Access will work after all...
    By David Jones in forum Database
    Replies: 5
    Last Post: 09-02-2001, 04:17 AM
  5. Replies: 0
    Last Post: 12-20-2000, 01: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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center