Loading Access table into DataSet .NET

Question

Good day,

I'm using the OleDbDataAdapter in VB.NET to load query results into a data set.

However, when I step through the code it only loads the columns into the data set and not the rows in the query.

I did do a brief test by exporting that query result into a separate Access DB and then tried loading it and it worked correctly.

I am unsure whether or not the command for Fill supports loading of query results?

This is the code I am using to load up the Access table:

    Dim dsAccess As New DataSet
    Dim da As OleDb.OleDbDataAdapter

    Try
        If conAccess.State = ConnectionState.Open Then
            sql = "SELECT * FROM " & strTblName
            da = New OleDb.OleDbDataAdapter(sql, conAccess)


            da.Fill(dsAccess, strTblName)
        Else
            Return False
        End If
    Catch ex As System.Exception
        Return False
    Finally
        da = Nothing
    End Try

Note, I've edited this question to show the actual table structure of my "query".

That's the query I'm trying to perform the Select on.

The design view of the query:

Hopefully this helps in diagnosing this issue!

Answer 1

It's not a good idea to have a try-catch without checking to see what the error message is e.g. MessageBox.Show(ex.Messasge). Have you tried this or taking the code completely out of the try-catch to see if an exception is thrown?

PLease try the following code with proper modifications to the connection string and table name. Hopefully the data is returned or an exception is thrown.

One more thing, if field names have spaces in them e.g. First Name you might need to do something like SELECT [First Name] ... and lastly if any fields are reserve words like Date you should wrap them in [].

Public Sub SimpleDemo()
    Dim dt As New DataTable
    Dim strTblName As String = "SomeTable"
    Using cn As New OleDb.OleDbConnection With
        {
            .ConnectionString = "Your connection string goes here"
        }
        Using cmd As New OleDb.OleDbCommand With
            {
                .Connection = cn,
                .CommandText = "SELECT * FROM " & strTblName
            }
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using

    MessageBox.Show(dt.Rows.Count.ToString)
End Sub

Answer 2

Okay so after much research I finally found a way to load up the table. I'm posting this here so that someone else who runs into this has something to work with!

Anyways what I found out was that when you use Microsoft Access, the OleDbAdapter doesn't like the fact that the Like statement uses the * wildcard character. I found out that when using the OleDbAdapter you have to use the % operator. A simple Replace does the job.

So basically what I did was, I found the actual query behind the View I had. I got the View Definition of it (which is the pesky query that encompasses the Like and from there I just did a simple Replace).

Here's the code (posted this to help everyone else save some time):

                Dim dtMeta As DataTable = conAccess.GetSchema("Tables")

                If drTableType.Length > 0 Then
                    If drTableType(0).Item("TABLE_TYPE") = "VIEW" Then
                        dtMeta = Nothing
                        dtMeta = conAccess.GetSchema("Views")
                        drTable = dtMeta.Select("TABLE_NAME='" & strTblName & "'")
                        If drTable.Length > 0 Then
                            strOriginalQuery = drTable(0).Item("VIEW_DEFINITION").ToString()
                            strModifiedQuery = strOriginalQuery.Replace("*", "%")

                            Dim myCommand As OleDbCommand = New OleDbCommand()
                            myCommand.CommandText = strModifiedQuery
                            da = New OleDbDataAdapter(myCommand.CommandText, conAccess)
                            da.Fill(dsAccess, strTblName)
                        End If
                    Else
                        da.Fill(dsAccess, strTblName)
                    End If
                End If

The GetSchema method was employed to get the name of the tables out, and then further down I employed the method again to retrieve the Views collection, and from here I was able to get the ViewDefinition which had the actual query behind.