Link to home
Start Free TrialLog in
Avatar of HLRosenberger
HLRosenbergerFlag for United States of America

asked on

MS access and calling saved Queries

From VB.NET code, I'm calling a query saved in an Access database.   Is there a way to call multiple saved queries, each of which return a set of records, and get back each set of records in  a DataTable within a in DataSet?  Here's my current code:

   Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.JET.oledb.4.0;data source=C:\MyAccess\service.mdb")

        Dim cmd As New OleDbCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "GetWorkOrder"
        cmd.Parameters.Add("@LocalID", OleDbType.Integer).Value = 35

        Try
            cmd.Connection = con
            con.Open()

            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

            Dim dt As New DataTable

            Dim ds As DataSet = New DataSet()
            da.Fill(ds, "WorkOrder")

            dt = ds.Tables("WorkOrder")

            con.Close()
            con.Dispose()

        Catch ex As Exception
        End Try
Avatar of YZlat
YZlat
Flag of United States of America image

Could try something like that:

Dim cmd1 As New OleDbCommand
Dim cmd2 As New OleDbCommand
Dim cmd3 As New OleDbCommand
Dim dt1 As New DataTable
Dim dt2 As New DataTable
Dim dt3 As New DataTable
            

con.ConnectionString = "Provider=Microsoft.JET.oledb.4.0;data source=C:\MyAccess\service.mdb"
con.Open()


    cmd1.Connection = con
    cmd1.CommandType = CommandType.StoredProcedure
    cmd1.CommandText = "GetWorkOrder"
    Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(cmd1)
    cmd1.Parameters.Add("@LocalID", OleDbType.Integer).Value = 35
  Dim ds1 As DataSet = New DataSet()
    da1.Fill(ds1, "WorkOrder")
dt1 = ds1.tables("WorkOrder")


   cmd2.Connection = con
    cmd2.CommandType = CommandType.StoredProcedure
    cmd2.CommandText = "SecondQueryName"
    Dim da2 As OleDbDataAdapter = New OleDbDataAdapter(cmd2)
    cmd2.Parameters.Add .....
  Dim ds2 As DataSet = New DataSet()
    da2.Fill(ds2, "Table2")
dt2 = ds2.tables("Table2")


     cmd3.Connection = con
    cmd3.CommandType = CommandType.StoredProcedure
    cmd2.CommandText = "ThirdQueryName"
    Dim da3 As OleDbDataAdapter = New OleDbDataAdapter(cmd3)
    cmd3.Parameters.Add .....
  Dim ds3 As DataSet = New DataSet()
    da3.Fill(ds3, "Table3")
dt3 = ds3.tables("Table3")

Open in new window

Avatar of HLRosenberger

ASKER

But that hits the database three time, correct?  it requires three open connections, correct?  I was looking to do this to improve speed.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks!