locked
MS Access Database Question RRS feed

  • Question

  • Just a quick question for you all.

    I have a MS Access Database attached to my project.  The name of the database is PracticeDB.

    I am just experimenting now.  The database has one table with two fields.  Number and name. 

    Dim Rand As New Random

    Dim RandNo As Integer

    RandNo = Rand.Next(1, 101)

    Based on the random number, I want to return the name if the number matches RandNo.  Do I need to setup a connection string within the code to display the results?  The select statement would be simple: "SELECT Number, Name from PracticeTable WHERE Number = " & RandNo.

    Just not sure how to display the data.

    Thanks in advance for any info that can be provided.  I have looked for samples, however, most of what I found is for SQL Express.

     

     

    Thursday, September 14, 2006 11:57 PM

Answers

  • that is correct. In order to access the MS Access database you must connect to it and execute your query. Now, there are many ways of going about retrieving the records you want from the query supplied. Let me run down a couple of them:

  • OleDbDataAdapter approach - this is a pretty nice thing. Using this allows you to fill a dataset with data retrieved from the database using your query and having all records stored locally, like an "in memory" database. You can then bind this dataset to a bindable UI component, like a datagridview. This should really be used (generally speaking) when you have a fair amount of records to be shown. You can also update the database calling the Update() command, without much effort really - nifty thing!

  • OleDbDataReader - does just that. Reads data. Fast, forward only data and doesnt really care about the schema. Quick way to get data and handy if you are expecting just a few records

     

    using OleDbDataReader in your case would be ideal. You can then either bind data to a UI component or store it in variables.

    You need to import the System.Data.OleDb namespace to access the OleDb classes

    To connect to an MS Access database, you need a connection string firstly. Typical connection string:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

    Dim theOleDbConnection as new OleDbConnection("connectionStringHere")

    and thats it. Now to execute your query, you need an OleDbCommand:

     

     

    overall code:



  • Dim theOleDbConnection as new OleDbConnection("connectionStringHere")
    Dim theOleDbCommand as new OleDbCommand("SELECT Number, Name from PracticeTable WHERE Number = " & RandNo.ToString())
    theOleDbCommand.Connection = theOleDbConnection
    theOleDbConnection.Open()
    Dim theDataReader as OleDbDataReader = theOleDbCommand.ExecuteReader(CommandBehavior.CloseConnection)
    while theDataReader.Read = true
       'read data
    end while
    theOleDbConnection.Close()

     

     

    More info about OleDbDataReader:

    http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.aspx

     

    To use the DataAdapter approach:



    Dim theOleDbConnection as new OleDbConnection("connectionStringHere")
    Dim theOleDbCommand as new OleDbCommand("SELECT Number, Name from PracticeTable WHERE Number = " & RandNo.ToString())
    theOleDbCommand.Connection = theOleDbConnection
    Dim theDataSet as new DataSet()
    Dim theDataAdapter as new OleDbDataAdapter(theOleDbCommand)
    theOleDbConnection.Open()
    theDataAdapter.Fill(theDataSet)
    theOleDbConnection.Close()
     
    'assuming we have a datagridview:
    Me.theDataGridView.DataSource = theDataSet.Tables(0).DefaultView

     

     

    I hope this helps you

Friday, September 15, 2006 12:22 AM

All replies

  • that is correct. In order to access the MS Access database you must connect to it and execute your query. Now, there are many ways of going about retrieving the records you want from the query supplied. Let me run down a couple of them:

  • OleDbDataAdapter approach - this is a pretty nice thing. Using this allows you to fill a dataset with data retrieved from the database using your query and having all records stored locally, like an "in memory" database. You can then bind this dataset to a bindable UI component, like a datagridview. This should really be used (generally speaking) when you have a fair amount of records to be shown. You can also update the database calling the Update() command, without much effort really - nifty thing!

  • OleDbDataReader - does just that. Reads data. Fast, forward only data and doesnt really care about the schema. Quick way to get data and handy if you are expecting just a few records

     

    using OleDbDataReader in your case would be ideal. You can then either bind data to a UI component or store it in variables.

    You need to import the System.Data.OleDb namespace to access the OleDb classes

    To connect to an MS Access database, you need a connection string firstly. Typical connection string:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

    Dim theOleDbConnection as new OleDbConnection("connectionStringHere")

    and thats it. Now to execute your query, you need an OleDbCommand:

     

     

    overall code:



  • Dim theOleDbConnection as new OleDbConnection("connectionStringHere")
    Dim theOleDbCommand as new OleDbCommand("SELECT Number, Name from PracticeTable WHERE Number = " & RandNo.ToString())
    theOleDbCommand.Connection = theOleDbConnection
    theOleDbConnection.Open()
    Dim theDataReader as OleDbDataReader = theOleDbCommand.ExecuteReader(CommandBehavior.CloseConnection)
    while theDataReader.Read = true
       'read data
    end while
    theOleDbConnection.Close()

     

     

    More info about OleDbDataReader:

    http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.aspx

     

    To use the DataAdapter approach:



    Dim theOleDbConnection as new OleDbConnection("connectionStringHere")
    Dim theOleDbCommand as new OleDbCommand("SELECT Number, Name from PracticeTable WHERE Number = " & RandNo.ToString())
    theOleDbCommand.Connection = theOleDbConnection
    Dim theDataSet as new DataSet()
    Dim theDataAdapter as new OleDbDataAdapter(theOleDbCommand)
    theOleDbConnection.Open()
    theDataAdapter.Fill(theDataSet)
    theOleDbConnection.Close()
     
    'assuming we have a datagridview:
    Me.theDataGridView.DataSource = theDataSet.Tables(0).DefaultView

     

     

    I hope this helps you

Friday, September 15, 2006 12:22 AM
  • Hi all,

    I am a novice MS Access user and I created a simple database for a small business client. There are a few bugs that I need worked out ASAP. I am looking for someone who would be willing to take a look at the database and fix the bugs. I had someone helping me out but they were due to provide the fixed DB back in November 2006 and now I can't get in contact with them. I am desperate b/c I promised the client I would provide the DB to them in December.

    If anyone does some work on the side and is willing to help, I need you!! I am also looking to expand the functionality a bit but shouldn't be anything too fancy.

    Please contact me if you're interested.

    Thanks in Advance!!!

    "Desperate" AA807

    Friday, January 26, 2007 4:27 PM
  •  

    How does someone contact you?

    Friday, January 26, 2007 4:41 PM
  • Please contact me at newage807@hotmail.com.

     

    Thanks!

    Friday, January 26, 2007 6:44 PM