Filling multi tables into dataset?

Question

I have problem with my code which fills multi tables into my dataset. It loads all contents contained in tables of my database to only one table in dataset. My code is shown below. How to load those tables from database into a dataset , that has the same number of tables and contents.

Private Sub Filldataset()
Private cnn As OleDbConnection
Private dt As New DataTable
Private da As New OleDbDataAdapter
Private cmd As New OleDbCommand
Private ds As New DataSet
    Dim tblrestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}
    Dim userTables As DataTable = Nothing
    userTables = cnn.GetSchema("Tables", tblrestrictions)
    Dim i As Integer
    For i = 1 To userTables.Rows.Count - 1 Step 1
        cnn = New OleDbConnection(Str)
        cnn.Open()
        cmd = cnn.CreateCommand
        cmd.CommandText = "select * from" & " " & userTables.Rows(i)(2).ToString
        dt.Clear()
        da.SelectCommand = cmd
        da.Fill(dt)
        da.Fill(ds)
    Next
    cnn.Close()
    MessageBox.Show(ds.Tables.Count)
End Sub

Answer 1

Connections can be created elsewhere but should not be opened or closed until directly before an directly after you use them. You will have to adjust this code for an Oledb application.

Private Sub GetData()
        cn.Open()
        Dim dt As DataTable = cn.GetSchema("Tables")
        cn.Close()
        Dim ds As New DataSet
        Dim row As DataRow
        For Each row In dt.Rows
            Dim strTableName As String = row(2).ToString
            Dim strSQL As String = "Select * From " & strTableName
            Dim cmd As New SqlCommand(strSQL, cn)
            Dim da As New SqlDataAdapter
            da.SelectCommand = cmd
            da.Fill(ds, strTableName)
        Next
        Debug.Print(ds.Tables.Count.ToString)
End Sub

I scoped several variables locally that you will want to scope to the class like the dataset