• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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