Solved

VB.net Create a query inside an Access database

Posted on 2013-05-26
2
521 Views
Last Modified: 2013-06-01
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
Comment
Question by:murbro
2 Comments
 
LVL 9

Accepted Solution

by:
skipper68 earned 500 total points
ID: 39197950
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
 

Author Closing Comment

by:murbro
ID: 39212497
Thanks very much
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

825 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