locked
Reading Access DB...code snippet? RRS feed

  • 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....

     

    Sunday, September 12, 2010 8:44 PM

Answers

    • Marked as answer by Bin-ze Zhao Thursday, September 16, 2010 10:15 AM
    Sunday, September 12, 2010 9:38 PM
  • 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)
    • Marked as answer by Bin-ze Zhao Thursday, September 16, 2010 10:15 AM
    Monday, September 13, 2010 1:04 PM

All replies

    • Marked as answer by Bin-ze Zhao Thursday, September 16, 2010 10:15 AM
    Sunday, September 12, 2010 9:38 PM
  • 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)
    • Marked as answer by Bin-ze Zhao Thursday, September 16, 2010 10:15 AM
    Monday, September 13, 2010 1:04 PM
  • 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?
    Thursday, September 16, 2010 1:51 AM
  • 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

    Thursday, September 16, 2010 4:03 AM