Access different tables

[BlackByte]

Hi i have a database called Inventory.mdb, that has two tables, tblInventory and tblAdmin. I use the following code to access tblInventory :

Dim ds As New Data.Dataset
Dim da As New Oledb.OledbDataAdapter
Dim con As New Oledb.OledbConnection
Dim sql As String
'Under form_load
con.ConnectionString = "MICROSOFT.Jet.Oledb.4.0; Data Source = C:\Inventory.mdb"
sql = "SELECT * FROM tblInventory"
da = New Oledb.OledbDataAdapter(sql,con)
da.fill(ds, "tblInventory")

what i would like to know is how do i access tblAdmin, using the same connection and dataAdapter, or should i create seperate ones

 

[jmcilhinney]

You can use the same connection but you should definitely create a separate data adapter.

 

[Hack]

And name them so that in your code a quick glance will tell you which one is doing what

Dim daInventory As New Oledb.OledbDataAdapter
Dim daAdmin As New Oledb.OledbDataAdapter

 

[Tom]

You can query multiple resultsets with a single call to the database but you must use table mappings to assign which resultsets fill which of the tables in your dataset. The first returned ResultSet is named "Table", second "Table1", third "Table2" etc...

sql = "SELECT * FROM tblInventory; Select * From tblAdmin"

da = New Oledb.OledbDataAdapter(sql,con)
da.TableMappings.Add("Table", "tblInventory")
da.TableMappings.Add("Table1", "tblAdmin")
da.fill(ds)

 

[jmcilhinney]

You can query multiple resultsets with a single call to the database but you must use table mappings to assign which resultsets fill which of the tables in your dataset. The first returned ResultSet is named "Table", second "Table1", third "Table2" etc...

sql = "SELECT * FROM tblInventory; Select * From tblAdmin"

da = New Oledb.OledbDataAdapter(sql,con)
da.TableMappings.Add("Table", "tblInventory")
da.TableMappings.Add("Table1", "tblAdmin")
da.fill(ds)

That functionality depends on the database and the provider and it's not supported by the Jet OLEDB provider unfortunately.