Run access make table query from vb.net

Does anyone know how to run an access make table query from vb.net

I know how to run SQL queries but not a make table query

Thanks in advance
SweetingAAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
What is the SQL for qry_LastRecord?

You may need to spell it out in your .net code.  A simple example:


Dim strMakeTable As String = "SELECT * INTO [NewTable] FROM [OldTable]"
Dim cmd2 As New System.Data.OleDb.OleDbCommand(strMakeTable, myConnection)

cmd2.ExecuteNonQuery()
cmd2.Dispose()

Open in new window

0
 
Robert ShermanOwnerCommented:
I would think they would be run the same way or at least in a very similar manner.   Can you provide a brief example of code that you are using to run a SQL query, along with code that does not work when passing it a make table query?

Are you using OleDb?
0
 
SweetingAAuthor Commented:
Dropping the table works
Running the make table query does not work


Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
       
        Dim provider As String
        Dim dataFile As String
        Dim SchemaTable As DataTable
        Dim connString As String
        Dim myConnection As OleDbConnection = New OleDbConnection

        'provider = "Provider = Microsoft.Jet.OLEDB.4.0;"
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        dataFile = "C:\Pm\Examples\AppExamples\Data\Database\Plantroom.mdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString
        myConnection.Open()
        SchemaTable = myConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, "Table2"})

        Try

            If SchemaTable.Rows.Count = 0 Then
                Dim str2 As String = "qry_LastRecord"
                Dim cmd2 As New System.Data.OleDb.OleDbCommand(str2, myConnection)
                cmd2.CommandType = CommandType.StoredProcedure
                cmd2.ExecuteNonQuery()
                cmd2.Dispose()
            Else
                Dim str As String = "DROP TABLE Table2"
                Dim cmd As New System.Data.OleDb.OleDbCommand(str, myConnection)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                '   Dim str2 As String = "qry_LastRecord"
                '    Dim cmd2 As New System.Data.OleDb.OleDbCommand(str2, myConnection)
                '    cmd2.CommandType = CommandType.StoredProcedure
                '    cmd2.ExecuteNonQuery()
                '    cmd2.Dispose()
            End If
            myConnection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.