none
visual basic and access 2015

    Question

  • Create a new table in an existing database with code. this is what I have, the database opens and closes, but no table is added

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

            Dim dbProvider As String
            Dim dbSource As String
            Dim con As New OleDb.OleDbConnection
            Dim FullDatabasePath As String


            dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

            FullDatabasePath = "C:\Users\Russe\Desktop\stocks\StockDatadase\stockdata1.accdb"

            dbSource = "Data Source = " & FullDatabasePath
            con.ConnectionString = dbProvider & dbSource

            con.Open()
            MsgBox("Database is now open")

            '***************************************************************************************
            Dim ds As New DataSet
            Dim da As OleDb.OleDbDataAdapter
            Dim sql As String

            '*************************************************************************************
            sql = "CREATE TABLE " & ". People (" &
      "Id int NOT NULL PRIMARY KEY, " &
      "LastName  VARCHAR(30), " &
      "FirstName VARCHAR(20), " &
      "Address   VARCHAR(50) " &
      ") "
            ' Execute

            da = New OleDb.OleDbDataAdapter(sql, con)
            Dim cb As New OleDb.OleDbCommandBuilder(da)

            da.Update(ds, sql)
         '***************************************************************************************
            con.Close()
            MsgBox("Database is now Closed")

        End Sub
    End Class

        
    Saturday, April 22, 2017 6:25 PM

Answers

  • Here is a full example that first checks to see if the table exists and if so, in this case drops the table then goes on to create it. If the table is there you might not want to simply drop it but no matter, if it exists and you attempt to re-create it a error would be raised.

    Public Class AccessOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Function CreateTable() As Boolean
            Dim TableToCreate As String = "Catalogue"
    
            If TableExists(Builder.ConnectionString, TableToCreate) Then
                If DropTable(Builder.ConnectionString, TableToCreate) Then
                    Return False
                End If
            End If
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            CREATE TABLE Catalogue 
                            (
                                [Id] COUNTER, 
                                [StockCode] TEXT(10), 
                                [AccountNumber] TEXT(6)
                            )
                        </SQL>.Value
    
                    cn.Open()
                    Try
                        cmd.ExecuteNonQuery()
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
        End Function
        Public Function TableExists(ByVal ConnectionString As String, ByVal TableName As String) As Boolean
            Dim Result As Boolean = False
    
            Dim dt As DataTable = New DataTable With {.TableName = "test"}
    
            Using cn As New OleDbConnection(ConnectionString)
                cn.Open()
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
    
            End Using
    
    
            Dim query = (From F In dt.Rows.Cast(Of DataRow)() Where F.Field(Of String)("TABLE_NAME").ToString = TableName).FirstOrDefault
    
            If query IsNot Nothing Then
                Result = True
            End If
    
            Return Result
    
        End Function
        Public Function DropTable(ByVal ConnectionString As String, ByVal TableName As String) As Boolean
    
            Dim Result As Boolean = False
    
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Dim cmd As New OleDbCommand With {.CommandText = "DROP TABLE " & TableName, .Connection = cn}
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
    
                Result = True
    
            Catch ex As OleDbException
                ' do nothing
            End Try
    
            Return Result
    
        End Function
    
    
    End Class
    

    Form code

    Dim ops As New AccessOperations
    If ops.CreateTable Then
        MessageBox.Show("Created")
    Else
        MessageBox.Show("Failed")
    End If


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 22, 2017 7:11 PM
    Moderator