Database project needs to create new tables, access old tables

[kindofsudden]

I'm trying to visualize how this will be set up... I need my VB.NET application to read year 2013 values (Year, Employee, and Annual Performance Score) from a CSV file and write them to an Access DB. This DB is linked to my VB.NET project using a dataset. No problem, I have done this before.

However, when the new performance scores are available in year 2014 and the user opens this application, I need it to create a new, identical Access DB (with no records) for 2014 and fill those values from the new CSV. Then I need the user to be able to select a year and have VB.NET connect to the appropriate DB so the user can look up historic values.

The key here is not the CSV read or the DB write, it is the creation of the new year's DB and the dynamic re-link of the dataset to the new DB.

Any thoughts on how to approach this? Thanks ahead of time!

[kRod]

Why do you need to create an entire new database? Why not just create a new table to hold the new year's data? If you are set on creating a new DB then check out this link Click Here

[kindofsudden]

If it would make more sense to create new tables within the same DB, I am open to that. I am pretty sure I can dynamically create new tables, but do you have any examples of how to add those new tables to the Access DB?

[kRod]

 Private Sub CreateNewAccessTable()
        Dim dataBasePath As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "\access.mdb"
        Dim tableName As String = "nameOfYourNewTable"

        'Create a new table using OLEDB Provider  
        Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & dataBasePath)
        con.Open()

        'Get data Table schema  
        Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tableName, "TABLE"})
        con.Close()
        ' If the table exists, the count = 1 
        If dbSchema.Rows.Count > 0 Then
            'if the table exists
            'inform user and exit Sub

            MsgBox("Table Exists")
        Else

            'Create the new table 
            Dim cmd As New OleDb.OleDbCommand("CREATE TABLE [" + tableName + "] ([Field1] TEXT(10), [Field2] TEXT(10))", con)
            con.Open()
            cmd.ExecuteNonQuery()
            MessageBox.Show("Table Created Successfully")
            con.Close()
        End If

    End Sub

[kindofsudden]

Thanks kRod, looks great. I will give it a shot!

[kindofsudden]

kRod, nice work. The code performs well, thanks for the input!

[Reverend Jim]

@kindofsudden - any relation to oliversudden?

[kindofsudden]

No sir!