Solved

Run access make table query from vb.net

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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