Reading Access DB...code snippet?

Question

I have a VB 10 app all written up, doing all kinds of fun things with it, and now I need to read from an Access database.

I went ahead and added the database to the project as a data source of type "Microsoft Access Database File (OLE DB)".  That's all the further I've gone.

I've never accessed an Access database using VB so I could use some tips.  A tutorial, code snippets, etc.  Seems like everything I'm finding here is really more for using SQL flavors so if anyone could offer some direction, I'd appreciate it.

Long made short -- I'm just going to have the app read a table into an array then manipulate the data from there.  In some cases I'll be adjusting variables after loading the data, in others I'll use the data for one nefarious purpose or t'other.  Nothing fancy.  At this point, there will not even be any writing; it will just be reading in data.  To be honest, it would be really nice if I could even do a simple SELECT on it (not sure if that's possible, but...).

Thanks....

 

Answer 1

look through these:

http://social.msdn.microsoft.com/Search/en-US/?query=OleDb%20Access&rq=meta:Search.MSForums.ForumID(0f60fa48-1ceb-41ee-a10a-0dfcee7e19bd)+site:microsoft.com&rn=Visual+Basic+General+Forum

the 16th one down looks like a good one

Answer 2

Below is relatively simple example that uses a DataReader:

    Dim AccessConn As System.Data.OleDb.OleDbConnection
    Dim AccessCommand As System.Data.OleDb.OleDbCommand
    Dim AccessReader As System.Data.OleDb.OleDbDataReader
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Test Files\db1 XP.mdb;"

    AccessConn = New System.Data.OleDb.OleDbConnection(ConnectionString)
    AccessConn.Open()
    Dim SQLString As String = "SELECT * FROM [TableName]" & _
                  " WHERE Col1 = ? AND Col2 = ?"

    AccessCommand = New System.Data.OleDb.OleDbCommand(SQLString, AccessConn)
    AccessCommand.CommandType = CommandType.Text
    AccessCommand.Parameters.AddWithValue("Param1", Col1Value)
    AccessCommand.Parameters.AddWithValue("Param2", Col2Value))
    Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
    With da
      .SelectCommand = AccessCommand
    End With
    AccessReader = AccessCommand.ExecuteReader

    While AccessReader.Read()
      Console.WriteLine(AccessReader.Item(0).ToString)
      Console.WriteLine(AccessReader.Item(1).ToString)
      Console.WriteLine(AccessReader.Item(2).ToString)
    End While

    AccessReader.Close()
    AccessConn.Close()

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

Yipes...I thought you said simile! :-) Ok- question. I've seen a few code snippets now that have the ...ToString in them. I am not familiar with this, but it appears to read in data and convert itt to a string type...correct? Where does it store the data?

Answer 4

One of the most useful features of .NET is consistent type conversions for basic types. ToString is an example of this. Every basic type in the System namespace has this member function, and it is overloaded to take parameters as a format string. The Object class implements it, and derived classes override the basic function. Documentation for particular implementations can be found by accessing help for the class, and examining the Member functions.

http://msdn.microsoft.com/en-us/library/system.object.tostring.aspx

The data is returned as a string. The buffer is internal to .NET