Solved

Run access make table query from vb.net

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now