locked
How do I connect to and read a table in Access to work with the data before displaying it? RRS feed

  • Question

  • User-1715607868 posted

    I need to read a table in Access and go row by row and use the data to do things but not display it directly.  I've always worked with more sophisticated databases before but never with Access.  Thus my questions are;

    1) how to connect to Access in a manner that will allow selecting a single table and reading it row by row?

    2) How to reference each field in the table row?

    I've tried the data connection wizard but it keeps wanting to tie to a data display object such as a gridview which is simply the exact opposite of what I want.  I've tried a few examples from different sites or forums but none of them are simply reading the data row by row.  Below is the closest I've gotten;

        Dim dt As DataTable
        Dim dr As DataRow
        Dim OleDBcn As OleDb.OleDbConnection
        Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\DotNetWindowsProjects\TBIN data\tbin2009.mdb;"
        Dim adpt As OleDb.OleDbDataAdapter

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            OleDBcn = New OleDb.OleDbConnection(ConnectString)
            OleDBcn.Open() 
            adpt = New OleDb.OleDbDataAdapter("Select * From tblHeatPump", OleDBcn)
            adpt.Fill(dt)  'It dies here.
            For Each Me.dr In dt.Rows
    ' Do stuff
            Next

        End Sub

    EDIT: I had a typo in the connection string which is now fixed. It now dies on the fill command instead of the open command.

    Edit 2: I had left out the dim dt line which does exist in my code as well as the dim dr line

    Tuesday, September 25, 2012 3:51 PM

Answers

  • User632635755 posted

    I see it being dimensioned, but not instantiated.  You should be creating an instance of the DataTable by using New DataTable or using a DataSet and calling the Tables.Add method from it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 25, 2012 5:05 PM

All replies

  • User632635755 posted

    IIRC, you must have the Access database closed before trying to read/write from/to it, otherwise it will throw an exception when reading/writing.  If this is not your problem, let us know :)

    Tuesday, September 25, 2012 4:47 PM
  • User632635755 posted

    Also, I wouldn't hold that connection around as a field of the class.  Open the database when you need to, close it when you are done.  Just good business.

    Tuesday, September 25, 2012 4:48 PM
  • User2117486576 posted

     adpt.Fill(dt)  'It dies here.

    Don't you need to dimension or declare or define the dt?

    Tuesday, September 25, 2012 4:54 PM
  • User-1715607868 posted

    Actually it was dimensioned further up the code.  I added that in the code on the first post via a second edit..

    Tuesday, September 25, 2012 5:00 PM
  • User632635755 posted

    I see it being dimensioned, but not instantiated.  You should be creating an instance of the DataTable by using New DataTable or using a DataSet and calling the Tables.Add method from it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 25, 2012 5:05 PM
  • User-1715607868 posted

    It just hangs on the da.Fill(dt).  I get no error message.  There are about 585 records, each with 92 columns.  Three columns are text and the rest are numbers.  I've waited until the browser times out but it never hits the breakpoint on the next line.

    Tuesday, September 25, 2012 5:07 PM
  • User-1715607868 posted

    I've never had to do that before with Oracle or SQL Server.  Do you mean something like this:

    dt = New DataTable

    da.Fill(dt)

    I'll give it a shot and see what happens.

    Tuesday, September 25, 2012 5:10 PM
  • User-1715607868 posted

    I see it being dimensioned, but not instantiated.  You should be creating an instance of the DataTable by using New DataTable or using a DataSet and calling the Tables.Add method from it.

    That was it! Thank you very much!  I was starting to feel stupid.

    Tuesday, September 25, 2012 5:12 PM