none
Visual Studio 2012 and Microsoft Access create new table in existing database RRS feed

  • Question

  • Hi,

    I currently have a database with one table that holds a user's login data. How would I go about using the username from registration to automatically create a new table under their username that stores other information? 

    I can only seem to find information about creating new records, not actual tables itself?

    Cheers

    Monday, August 7, 2017 6:17 AM

All replies

  • Hi Azuraxe,

    Based on your description, you want to create DataTable in the existing database, please refer to the code below.

     Dim tablename As String = "Test5"
            Dim provider As String
            Dim dataFile As String
            Dim connString As String
            Dim myConnection As OleDbConnection = New OleDbConnection
            provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
            dataFile = "D:\Database\test2.accdb"
            connString = provider & dataFile
    
            Dim con As New OleDbConnection(connString)
            con.Open()
                Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tableName, "TABLE"})
            If dbSchema.Rows.Count > 0 Then
                MessageBox.Show("The Table has been existed!")
                ' do whatever you want to do if the table exists
            Else
                Dim cmd As New OleDbCommand()
                cmd.Connection = con
                cmd.CommandText = "CREATE TABLE  " + tablename + " (ID COUNTER, [Year] INTEGER, FName TEXT)"
                Try
                    cmd.ExecuteNonQuery()
                    Console.WriteLine("Table created.")
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
            End If
            con.Close()

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, August 7, 2017 9:41 AM
    Moderator
  • Hello,

    I would suggest making sure that the table does not exists, if it does and you attempt to create it an exception will be thrown.

    For this reason I have included a check to see if the table exists. There is a function included to remove the table too so the code below has a method to create at table, determine if the table exists and a remove table method.

    All are done in a class as it's best to keep this type of code out of a form.

    Usage: Create a table named Karen.

    Dim ops As New Operations
    ops.CreateTable("Karen")

    Class

    Imports System.Data.OleDb
    
    Public Class Operations
        ''' <summary>
        ''' Here the database is in the same folder as the executable
        ''' </summary>
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
    
        Public Function CreateTable(ByVal TableName As String) As Boolean
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDbCommand With {.Connection = cn, .CommandText = TableStructure(TableName)}
                        If TableExists(TableName) Then
                            Return False
                        End If
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        Return True
                    End Using
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function
        ''' <summary>
        ''' SQL Query to create table
        ''' </summary>
        ''' <param name="TableName"></param>
        ''' <returns></returns>
        Public Function TableStructure(ByVal TableName As String) As String
            Dim createTableSQL As String =
                <sql>
                CREATE TABLE <%= TableName %> 
                (
                    [User_Name] TEXT(255), 
                    [Password_Protection_Enabled] YESNO ,
                    [TattleTale_Enabled] YESNO , 
                    [User_Password] TEXT(255) , 
                    [Security_Question] TEXT(255), 
                    [Security_Answer] TEXT(255) , 
                    [Email_Address]TEXT(255) , 
                    [Mobile_Phone_Number] TEXT(255) , 
                    [Is_Locked] YESNO
                )
                </sql>.Value
            Return createTableSQL
        End Function
        ''' <summary>
        ''' Determine if the table exists already
        ''' </summary>
        ''' <param name="TableName"></param>
        ''' <returns></returns>
        Public Function TableExists(ByVal TableName As String) As Boolean
            Dim Result As Boolean = False
    
            Using cn As New OleDbConnection(Builder.ConnectionString)
    
                cn.Open()
    
                Dim dt As DataTable = New DataTable With {.TableName = "test"}
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
    
                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 Using
        End Function
        ''' <summary>
        ''' Drop an existing table
        ''' </summary>
        ''' <param name="TableName"></param>
        ''' <returns></returns>
        Public Function DropTable(ByVal TableName As String) As Boolean
    
            Dim Result As Boolean = False
    
            Try
                Dim cn As New OleDbConnection(Builder.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
                Return False
            End Try
    
            Return Result
    
        End Function
    
    End Class
    


    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

    Monday, August 7, 2017 9:47 AM
    Moderator
  • Hi Cherry,

    I have implemented your solution into my program and am experiencing issues with the database writing. Unfortunately it doesn't write anything to the database, let alone make a new table. I get no errors with anything and everything it successful, however it could be that the database is writing but not saving..

    My code is below:

        Private Sub btnRegister_Click(sender As Object, e As EventArgs) Handles btnRegister.Click
            ' Validation
            If txtCaptcha.Text = str Then
                ' Declare all variables as strings and make the unencrypted text gathered from user input equal to the variables ready for encryption.
                Dim plainPassword As String = txtPassword.Text
                Dim plainGivenName As String = txtGivenName.Text
                Dim plainSurname As String = txtSurname.Text
                Dim password As String = txtPassword.Text
    
                ' Save txtPassword.text as the wrapper password for decryption
                Dim wrapper As New Simple3Des(password)
    
                ' Create encryption wrappers for Username, Password, Given Name and Surname, leaving License unencrypted as it is a unique identifier (Primary Key). 
                Dim cipherPassword As String = wrapper.EncryptData(plainPassword)
                Dim cipherGivenName As String = wrapper.EncryptData(plainGivenName)
                Dim cipherSurname As String = wrapper.EncryptData(plainSurname)
    
                ' Declaring License as an integer as Drivers Licenses are 9 digits long
                Dim License As Integer = txtLicense.Text
    
                ' Declaring enGivenName, enSurname, enUsername and enPassword as strings. These variables are equal to encrypted strings.
                Dim enGivenName As String = cipherGivenName
                Dim enSurname As String = cipherSurname
                Dim enPassword As String = cipherPassword
    
                ' Add the unencrypted License Number, encrypted Username, Password, Given name and Surname into Database then display action successful dialogue.
                If Me.TblUserTableAdapter.InsertQueryLogin(License, enPassword, enGivenName, enSurname) Then
                    Dim tablename As String = License
                    Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dbUsers.accdb;Persist Security Info=True;Jet OLEDB:Database Password=Password")
    
                    con.Open()
                    Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tablename, "TABLE"})
                    If dbSchema.Rows.Count > 0 Then
                        MessageBox.Show("The Table has been existed!")
                        ' Create table
                    Else
                        Dim cmd As New OleDbCommand()
                        cmd.Connection = con
                        cmd.CommandText = "CREATE TABLE  " + tablename + " (ID COUNTER, [Year] INTEGER, FName TEXT)"
                        Try
                            cmd.ExecuteNonQuery()
                            Console.WriteLine("Table created.")
                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
                        End Try
                    End If
                    con.Close()
                    MsgBox("Your account has successfully been registered.")
                    Login.Show()
                    Me.Hide()
            Else
            MsgBox("Unsuccessful registration, please try again")
            End If
            Else
            MsgBox("Incorrect Captcha, Please refresh!")
            End If
        End Sub

    Thanks,
    Azuraxe

    Wednesday, August 9, 2017 2:23 AM
  • Hi Azuraxe,

    At my side, I test my code again, it can creates table in the exist database.

    You can break point in you code, and step by step, to see what is problem.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 11, 2017 10:14 AM
    Moderator