?
Solved

Run access make table query from vb.net

Posted on 2015-02-19
4
Medium Priority
?
169 Views
Last Modified: 2016-06-23
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
0
Comment
Question by:SweetingA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40620526
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
 

Author Comment

by:SweetingA
ID: 40620559
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 40620771
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question