• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

VB.net Create a query inside an Access database

Hi. I am using the following VB.net code to create a view inside a SQL database.
What similar code would I use to create a query inside an Access database?


    Sub Create_SQL_View(ByVal oViewName As String, ByVal oSQL As String)
        Dim myConnection As SqlConnection = New SqlConnection(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text)
        Dim myCommand As SqlCommand

        myConnection.Open()

        Dim S As String = "CREATE VIEW " & oViewName & " AS " & " " & oSQL

        myCommand = New SqlCommand(S, myConnection)
        myCommand.ExecuteNonQuery()

        myConnection.Close()
    End Sub
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
skipper68Application Development ManagerCommented:
How about:
Dim query As OleDbCommand = New OleDbCommand(SELECT * FROM MYTABLE)  
 

or you can create a Query and store it into Access database file with VB.NET code  like this:

Imports System.Data.OleDb  
 
Public Class Form1  
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
 
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  
 
        Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC procProductsList AS SELECT * FROM Table1", con)  
        con.Open()  
        cmd.ExecuteNonQuery()  
        con.Close()  
 
    End Sub  
 
End Class  

You can call/execute the Query stored in Access database file with VB.NET code like this:

 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click  
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  
        Dim cmd As New OleDbCommand  
        cmd.CommandType = CommandType.StoredProcedure  
        cmd.CommandText = "procProductsList"  
        cmd.Connection = con  
        con.Open()  
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)  
        Dim ds As DataSet = New DataSet()  
        da.Fill(ds, "Table1")  
        DataGridView1.DataSource = ds.Tables("Table1")  
        con.Close()  
    End Sub
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now