none
New table in existing ms access database RRS feed

  • Question

  • hello there,

    i am looking to include in my application (in visual studio)  the ability to have a text box which a user can enter a value into and then a button next too it.

    When the button is clicked it will make a table with the name being the contents of the textbox and then each table created will have a predefined template for column names.

    im wondering if anyone would be able to advise me on how to do this. I am using a ms access database which is already connected in server explorer and i have made a data source for it.

    regards

    Bailey
    Saturday, June 3, 2017 6:47 AM

All replies

  • Baily,

    You can simply do that with SQL transact code for Jet which you execute with exectutenonquery.

    https://docs.microsoft.com/en-us/sql/relational-databases/blob/create-alter-and-drop-filetables


    Success
    Cor

    Saturday, June 3, 2017 8:54 AM
  • Hello,

    The first thing to consider is, does the table already exists? You need a function such as this

    Public Function TableExists(ByVal ConnectionString As String, ByVal TableName As String) As Boolean
        Dim Result As Boolean = False
    
        Using cn As New OleDbConnection(ConnectionString)
            cn.Open()
            Dim dt As DataTable = New DataTable With {.TableName = "dummy"}
            dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            cn.Close()
    
            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
    
        End Using
    
        Return Result
    
    End Function

    Of course you could simply drop the table and re-create

    Public Function DropTable(ByVal ConnectionString As String, ByVal TableName As String) As Boolean
        Dim Result As Boolean = False
        If TableExists(ConnectionString, TableName) Then
            Try
                Dim cn As New OleDbConnection(ConnectionString)
                Dim cmd As New OleDbCommand With {.CommandText = "DROP TABLE " & TableName, .Connection = cn}
                cn.Open()
                cmd.ExecuteNonQuery()
                cn.Close()
    
                Result = True
    
            Catch ex As OleDbException
                '
                ' As this is a demo this is fine but not for a real app
                ' 
                Result = False
            End Try
        Else
            Result = False
        End If
    
        Return Result
    
    End Function
    

    Finally a simple method to create the table. Note [id] COUNTER creates a auto-incrementing key.

    Public Function CreateTable(TableName As String) As Boolean
        Using cn As OleDbConnection = New OleDbConnection(Builder.ConnectionString)
    
            If TableExists(Builder.ConnectionString, TableName) Then
                DropTable(Builder.ConnectionString, TableName)
            End If
    
            Using cmd As New OleDbCommand("", cn)
                cmd.CommandText = $"CREATE TABLE {TableName} ([Id] COUNTER, [FirstName] TEXT(25),[LastName] TEXT(255), [ActiveAccount] YESNO, [JoinYear] INT)"
    
                Try
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                Catch ex As Exception
                    Return False
                End Try
    
            End Using
        End Using
    End Function
    CreateTable function has a pre-defined query to create a table, suppose you had one than one query for different table schemas, you could have a function which takes a argument for the type of table to create and pass back the query to the CreateTable function and we are good to go.


    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, June 3, 2017 11:18 AM
    Moderator
  • As you can see in the below i am getting quite a lot of errors. Im coding in visual basic just to clarify.

    Would you be able to advise where im going wrong

    (http://)imgur.com/a/TQLXq

    Sunday, June 4, 2017 7:48 AM
  • This is what I didn't provide (but you could had replaced it with your connection string), replace the value for DataSource to the path and file name of your database.

    Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    The majority of developers don't use this format as my guess is they consider it too much code yet there is a reason for it, connection strings are actually easier to construct and less prone to error.


    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

    Sunday, June 4, 2017 8:39 AM
    Moderator
  • You don't really want to use the Data Designer tool for SQL DDL. Here is a simple example that uses code:

            'Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            '                                    "Data Source=C:\Test Files\db1 XP.mdb"
    		
    	Dim ConnectionString As String = "Provider=Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                "Data Source=C:\Test Files\db1 XP.accdb"
    
    
            Dim DDLCreateTable As String = "CREATE TABLE TestAllTypes (" & _
                                            "MyText       TEXT(50)," & _
                                            "MyMemo       MEMO," & _
                                            "MyByte       BYTE," & _
                                            "MyInteger    INTEGER," & _
                                            "MyLong       LONG," & _
                                            "MyAutoNumber COUNTER," & _
                                            "MySingle     SINGLE," & _
                                            "MyDouble     DOUBLE," & _
                                            "MyCurrency   CURRENCY," & _
                                            "MyReplicaID  GUID," & _
                                            "MyDateTime   DATETIME," & _
                                            "MyYesNo      YESNO," & _
                                            "MyOleObject  LONGBINARY)"
    
            Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            AccessConnection.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand(DDLCreateTable, AccessConnection)
            AccessCommand.ExecuteNonQuery()
    
            AccessConnection.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, June 5, 2017 12:40 PM
  • When the button is clicked it will make a table with the name being the contents of the textbox and then each table created will have a predefined template for column names.

    I'm curious to know if the columns predefined template is the same for all table names? because I'm not sure how do you relate the table name and the columns.

    I'm asking because the whole scenario is a bit weird. Why not creating records instead of tables each time the user clicks? How do you know which table to query later?...etc


    Fouad Roumieh

    Monday, June 5, 2017 5:30 PM
  • When the button is clicked it will make a table with the name being the contents of the textbox and then each table created will have a predefined template for column names.

    I'm curious to know if the columns predefined template is the same for all table names? because I'm not sure how do you relate the table name and the columns.

    I'm asking because the whole scenario is a bit weird. Why not creating records instead of tables each time the user clicks? How do you know which table to query later?...etc


    Fouad Roumieh

    That is the next question Fouad, 

    How can I get a database scheme at an existing user. 

    Good catch

    :-)


    Success
    Cor

    Monday, June 5, 2017 7:30 PM