Solved

Run access make table query from vb.net

Posted on 2015-02-19
4
150 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

724 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