dcsimg
Results 1 to 5 of 5

Thread: Access data reading and writing

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2009
    Posts
    12

    Post Access data reading and writing

    I know this has to be a simple request, but I cannot find and documentation on how to open, read, save data, close an Access table without Visually seeing the data on a form. In VB6 I used to connect to the source, define the access table by a recordset, move the data from beginning to end via the .movefirst and .movenext functions, process the data by using the table field names as !fieldname and then close the record set and close the connection. That what I want to do.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,096

    Re: Access data reading and writing

    In .Net there are various alternatives to a recordset (which equate to the various options you provide as parameters to recordset.Open), such as a DataReader for only reading data, and several more.

    For examples of the various methods you can use to work with a database in .Net, see the article Retrieving and Saving Data in Databases which is one of the things linked from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2009
    Posts
    12

    Re: Access data reading and writing

    I have tried all of the suggestion. I went from SQL functions to OLEDB functions. Obviously, I don't have a clue as to what these particular functions do. I have got the program to open the Access table and I'm ready to read the table.

    Here is my code:

    Public Sub ExtraDB()
    Using MyConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;;Persist Security Info=False;Data Source=D:\VS2019\HORSES.mdb")
    Using MyCommand As New OleDb.OleDbCommand("SELECT Jockey, Trainer, Track, Type, Dist, Class, Pts, P1, P2, P3, FROM Extras", MyConnection)
    MyConnection.Open()
    Using MyReader As OleDb.OleDbDataReader = DataTableReader("Extras")
    While MyReader.Read
    MessageBox.Show("Track" & " " & "Jockey" & "Trainer" & "P1" & "P2" & "P3")
    End While
    End Using
    MyConnection.Close()
    End Using
    End Using
    End Sub


    It errors off at the While MyReader.read with:

    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    MyReader was Nothing.

    What is missing? Where can we lookup what all these oledb functions do?

    I know I'm from the old school of programming and I can do it in VB6, but when everything is written for you I get lost.

    Thanks,

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,096

    Re: Access data reading and writing

    Using MyReader As OleDb.OleDbDataReader = DataTableReader("Extras")
    You haven't shown what DataTableReader is, but I suspect you don't want it for this.

    That line should probably be:
    Code:
     Using MyReader As OleDb.OleDbDataReader = MyCommand.ExecuteReader()

    Where can we lookup what all these oledb functions do?
    If you have the help for Visual Studio installed, just click on the text you are interested in (such as OleDbConnection) and press F1.

    If you haven't got it installed you can do a web search for any of the object names, one of the top result should be the documentation at docs.microsoft.com, which tends to be very good (often including examples).

    So searching for OleDbConnection finds this: https://docs.microsoft.com/en-us/dot...tframework-4.8
    ...and if you want to find out about a method like ExecuteReader, also include the object it belongs to. DbCommand ExecuteReader finds this: https://docs.microsoft.com/en-us/dot...tframework-4.8


    In simple terms however, the objects you are using are:
    (OleDb)Connection - the link to the database (including location), similar to the old-school Connection object.
    (OleDb)Command - an SQL statement, usually associated with a particular Connection. This is similar to the Command object you may have used in VB6.
    (OleDB)DataReader - access to the data itself, similar to a read-only recordset.

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2009
    Posts
    12

    Re: Access data reading and writing

    Thank you, thank you. I finally was able to read the entire file and process as I desired. I found out a few other things that are a little annoying, but easily resolved once you know about them. They are all the fields you need to read must be in the SELECT list and you can't specify a field that is a key word field. When you do these things, the error you get are not obvious, but something you can work with. Again thanks. I'll be back.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width