Creating a new table from an existing template

[combs]

Hi,

I've got kind of a weird situation... I have an Access backend that I'm using to store all project data. When the user opens the data connection, he can choose from several active projects.... Upon selection of a project, the program checks to see if a "master" or "global" table exists in the database for that project. IF it exists, then - great - use the information from that table to fill datasets and you're off.... If it doesn't exist, then I must create it from a template table (tblTemplate) and then fill datasets from that table. The idea is that not all projects will want to co-mingle their data (i.e. item numbers and funding sources).

I'm having problems creating the table from the code... I can create it with a query in Access with no problems, but when I try to do it through VB.Net (Visual Studio 2008 Professional Edition), I keep getting the error message that "query input must contain at least one table or query". I'd appreciate any help that you may be able to offer...

The offending lines are RED and bolded.

Private Sub btnOpenProject_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenProject.Click
        [COLOR=green]'ENABLE OTHER TABS NOW THAT A PROJECT HAS BEEN SELECTED[/color]
        Dim i As Integer
        For i = 1 To TabControl1.Controls.Count - 1
            TabControl1.Controls.Item(i).Enabled = True
        Next
        [COLOR=green]'MOVE FOCUS TO THE OWNER INFORMATION TAB[/color]
        TabControl1.SelectedTab = TabControl1.Controls.Item(1)
        [COLOR=green]'MAKE THE CLOSE PROJECT BUTTON VISIBLE NOW THAT A PROJECT HAS BEEN OPENED...[/color]
        btnCloseProject.Visible = True
        Me.Text = m_Project.ToString
        [COLOR=green]'BIND THE FORM FIELDS AND DATA[/color]
        SetUpCurrencyManager()

        [COLOR=green]'OPEN & FILL OTHER DATASETS RELATED TO THIS PROJECT (USING THE TEMPLATE MASTER TABLE)
        'CHECK TO SEE IF A MASTER TABLE EXISTS FOR THIS PROJECT...
        'IF TABLE EXISTS, OPEN IT AND FILL DATASETS
        'IF TABLE DOES NOT EXIST, CLONE ONE FROM THE TEMPLATE AND THEN OPEN IT AND FILL DATASETS[/color]

        Dim boolTableFound As Boolean
        m_ProjectTableName = "tbl" & Replace(m_Project, " ", "")
        boolTableFound = RetrieveTableInformation(m_ProjectTableName)
        If boolTableFound = True Then
            [COLOR=green]'LOAD DATASETS FOR WORK ITEMS, FUNDS, ADMIN_PERCENTAGE & PROJECT DETAILS[/color]

        Else
            [COLOR=green]'CREATE THE TABLE FROM THE EXISTING TEMPLATE TABLE (THEN FILL DATSETS AS ABOVE)
            '****** CREATE TABLE FROM TEMPLATE ******[/color]
            Dim objCmd As New OleDb.OleDbCommand
            If conn.State = ConnectionState.Closed Then
                strDBLocation = My.Settings.strDatabaseLocation
                conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & strDBLocation

                objCmd.Connection = conn
                objCmd.CommandType = CommandType.Text

                If m_da Is Nothing Then m_da = New OleDb.OleDbDataAdapter
                m_da.SelectCommand = objCmd
                conn.Open()
            End If

            Try
                [COLOR=green]'Create Command objects[/color]
                [COLOR=red][b]Dim dbCommand As New OleDb.OleDbCommand("SELECT * INTO " & m_ProjectTableName & " FROM tblTemplate", conn)[/b][/color]
                MsgBox("SQL: " & dbCommand.CommandText.ToString)
                [COLOR=green]' Execute Query[/color]
                [COLOR=red][b]dbCommand.ExecuteNonQuery()[/b][/color]
            Catch ex As OleDb.OleDbException
                [COLOR=green]' Display error[/color]
                MsgBox("Error: " & ex.ToString())
            Finally
                [COLOR=green]' Close Connection[/color]
                conn.Close()
            End Try


        End If


    End Sub


    Public Function RetrieveTableInformation(ByVal strTableName As String) As Boolean
        Dim objCmd As New OleDb.OleDbCommand
        If conn.State = ConnectionState.Closed Then
            strDBLocation = My.Settings.strDatabaseLocation
            conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & strDBLocation

            objCmd.Connection = conn
            objCmd.CommandType = CommandType.Text

            If m_da Is Nothing Then m_da = New OleDb.OleDbDataAdapter
            m_da.SelectCommand = objCmd
            conn.Open()
        End If

        Dim tables As DataTable = objCmd.Connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)

        For Each row As DataRow In tables.Rows
            Console.WriteLine(row("TABLE_NAME"))
            If row("TABLE_NAME").ToString = strTableName Then
                RetrieveTableInformation = True
                Exit Function
            Else
                [COLOR=green]'MsgBox(row("TABLE_NAME").ToString & " <> " & strTableName)[/color]
            End If
        Next
        RetrieveTableInformation = False
    End Function

 

[Andrzejek]

Don't you have to have a table in order to [tt]SELECT * INTO MyNewTable [/tt]?

You have a function called RetrieveTableInformation which returns either the table exists or not. If the table does NOT exist, you need to CREATE TABLE before you can populate it.


Have fun.

---- Andy

 

[combs]

Andrzejek,

Thanks for your reply.

I'm not sure if you have to specify a table.... I thought if it didn't exist, that Access would create it automatically.

I know that using a query [inside Access], a table name for the new table is not needed - it creates it.... Not sure if that translates to programaticlly creating a table. I was hoping it was a syntax error or something that someone would be able to spot for me... Anyone? Anyone? Bueller?

Thanks again.

 

[Andrzejek]

Bueller took the day off, it's Friday....

You can make sure the table needs to be created in order for your logic to work by having empty m_ProjectTableName table already in your DB and run:

boolTableFound = RetrieveTableInformation(m_ProjectTableName)
[green]'Fake it[/green]
[blue]boolTableFound = False[/blue]
If boolTableFound = True Then
...

And then you can test your:

[red]Dim dbCommand As New OleDb.OleDbCommand("SELECT * INTO " & m_ProjectTableName & " FROM tblTemplate", conn)[/red]

Give it a shot.

Have fun.

---- Andy