Creating a Table in a New Database with AUTOINCREMENT column : Table Create « Access « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Access
  3. Table Create

Creating a Table in a New Database with AUTOINCREMENT column

 
Sub CreateTableInNewDb()
    Dim cat As ADOX.Catalog
    Dim conn As ADODB.Connection
    Dim strDb As String
    Dim strTable As String
    Dim strConnect As String
    
    On Error GoTo ErrorHandler
    
    Set cat = New ADOX.Catalog
    strDb = CurrentProject.Path & "\mydb.mdb"
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDb
    
    cat.Create strConnect
    
    Set conn = cat.ActiveConnection
    
    conn.Execute "CREATE TABLE myTable(SchoolId AUTOINCREMENT(100, 5)," & _
        "SchoolName CHAR,City Char (25), District Char (35),YearEstablished Date);"
ExitHere:
    Set cat = Nothing
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    If Err.Number = -2147217897 Then
        Kill strDb
        Resume 0
    Else
        Debug.Print Err.Number & ": " & Err.Description
        GoTo ExitHere
    End If
End Sub








Related examples in the same category

1.Create a table with validation rule
2.Creating a Table (ADOX data types vs. Microsoft Access data types)
3.Creating a Table in the Current Database with SQL statement
4.Creating a Table with a Single-Field Index with SQL command
5.Adding a Single-Field Index to an Existing Table with SQL command
6.Creating an Index Based on Two Fields with SQL command
7.Default Column value
8.Creating Check Constraints:add business rules for a table