Newb: Getting into an Access 2002 db with

Hi, I'm very new to application programming, but have a good number of years doing web based stuff with PHP/SQL.

What are the first steps I need to do to get into an Access 2002 database with Visual Basic 2008 Express?


  • seancampbellseancampbell Pennsylvania, USA

    First add this above the "Public Class Form1" line of your application:
    Imports System.Data
    Imports System.Data.OleDB

    The Imports statement opens a "namespace" for you, so you can write commands to it without explicitly calling it. Without this, you would have to properly call variable types like this: System.Data.OleDB.OleDBConnection

    Next, read this post that I made that describes how to connect to and manipulate data in an Access database (this is a common enough question that I wrote a tutorial for it a few months ago):

    I was never able to attach that screenshot...
    -The top code box describes the objects I put on the form and what changes I made to them.
    -The second describes how I set up the MS Access DB
    -The third contains the code on my form, inside that you should find ample code and commenting to help you understand this. Feel free to pose any questions you'd like

    Sean Campbell
  • First of all, THANK YOU! The code you wrote is a huge help and great learning tool.

    I'm having a hard time figuring out the listview stuff. It doesn't look like lvData was declared in the code, so I put [code]Dim lvData As ListView[/code] in line 10 to get rid of the errors saying it wasn't declared yet, but it didn't really solve the issue (I think).

    I'm able to open the database, but can't do any functions to records within it.

    The error points to a NullReferenceException when lvData.Items is referenced.

    I can give more detailed information if you want, but I'm assuming that the error's happening because I put the lvData dim in the wrong spot...?

  • seancampbellseancampbell Pennsylvania, USA
    You are right, you have declared lvData in the wrong spot.

    Remove that line entirely. Instead, add a listview to your Form, and name it lvData. There are a few settings you will need to set on it, those settings are detailed in the top code box of the MS Access DB Program post.

    The reason you recieve a Null reference error is because you are trying to access elements of the object lvData before it has been initialized. You would need a second line:

    Dim lvData as ListView
    lvData = new ListView() 'This is due to how Class Objects work, i am not good at explaining this
  • seancampbellseancampbell Pennsylvania, USA
    After looking at that post again... The entire first code box is describing objects that are to be drawn onto a form. I no longer have this project on this computer, but I could draw the form up and post a screenshot if you need me to.

    Be sure that you set all of the properties for the listview, the most important one is the property "View" being set to Details.
  • That worked really well. Once I got the listview object in the form it all kind of fell into place. Thank you very much for the help.

    I have 2 questions for you:

    Is there any way to make it so a database loads when the form does, so the user doesn't have to hit the open button?

    Is there any way to load the data within the database at the same time it's loaded, so the user doesn't have to hit the refresh button?
  • Nevermind, I figured it out :)
    I put this in Form 1:

    [code]Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connString As String = "provider= microsoft.jet.oledb.4.0; " & _
    "data source=c:/dbtest/db1.mdb;"

    Dim conn As New OleDbConnection(connString)


    Catch ex As OleDbException
    ' Display error
    MsgBox("Error: " & ex.ToString())
    ' Close Connection

    End Try[/code]

    And then I just copied and pasted the information from the RefreshLV sub you wrote and it loads fine!
  • seancampbellseancampbell Pennsylvania, USA
    Good work!!
    I was in the middle of composing a reply when I saw your's.

    Here is a note on having a filepath programmed straight in: (here I use constant variables, but you could program the filepath in straight. I prefer to make changes in only one place, so if I call FilePath in a bunch of places, only need to change it in the one delcaration)

    How to do it is dependent on your program, but you can do things like this:

    Const FilePath As String = "C:WhereItIsFileName.Mdb"

    And put a Static path in (this would only be good in he instance that you know that the filepath would never ever... EVER... change. I would definitely not suggest this unless you were making a very short term solution, or a one time program)

    If you program a static path, you don't have to prompt the user and ask where a file is, you can just supply FilePath (or whatever you name that variable).

    If you want the path of the file to be relative to the folder the application is running from, you can do this:

    Const FilePath as String = "FileName.mdb"
    Const FilePath as String = "DataFileName.mdb"

    If you want the file to be located anywhere on the computer, and it has a chance to move somewhere, you would want to save the file path to a flatfile and load that path back in when you load, it gets more complicated from there. Then you can check for the existence of the file (System.IO.File.Exists(FilePath)) and prompt for a new filePath when it couldn't find it.

    I suggest setting it up to run relative to the application's path, unless you are writing a program that is only going to be used once or for a VERY brief time. (If it's for work, spend the extra time making it work better to save yourself a lot of time upgrading it in the future)

    When you are debugging an application, it's startup path will be close to the path in parenthese. When you debug, the program actually compiles an exe file in this folder (which you could in theory just copy out of there to deploy the program on someone's PC)
    (for VS 2008, it will be something like this: C:Visual Studio 2008 ProjectsProject NameProject NameBinDebug)
  • [b]You're awesome. [/b]

    Is it possible for a user to search within a specific field in a ListView box? For example, FName?
  • seancampbellseancampbell Pennsylvania, USA
    Typically I store the data outside of the ListView, and search on that, but you can search through a listview.

    I am not sure what exactly you want to do. I most comonly search ListViews for Identities (or a unique value, integer or string, that indetifies the record and makes it unique). Normally I store the unique identifiers in the Tag Field of a row in a list view
    'This is how I add it
    Dim xItem as New ListViewItem("FirstName")
    xItem.Tag = UniqueKey 'This could be the primary key from the Database


    'This is how I search on the tag
    For r as integer = 0 to ListView1.Items.Count - 1
    If ListView1.Items(r).Tag = SearchTerm Then
    MsgBox("Found it on row: " & r)
    Exit For 'This escapes the loop
    ' If I only need to find one, I don't want to continue searching
    ' In other words, Unique means 1 is only found*
    End If
    Next r

    'this searches all columns of each item
    'and sends a message box when it finds them
    Dim SearchTerm as String

    For r as integer = 0 to ListView1.Items.Count - 1
    For c as integer = 0 to Listview1.Items(r).SubItems.Count - 1
    If ListView1.Items(r).SubItems(c).Text = SearchTerm
    MsgBox("Found one on row: " & r & " column: " & c)
    End If
    Next c
    Next r

    'Lets say I want to search for multiple records, and return that in an array.
    'Since a 2 dimensioned array is a pain, I will use the Point object
    'which holds an X (for Column) and Y (for Row)
    Public Function GetSearchResultsInArray(ByVal SearchTerm as String) as Point()
    Dim P() as Point()
    'This is tricky, we don't know how many results there will be
    'So we are going to have to Redimension the bounds of this array
    'during run time. We will use ReDim for that, and ReDim Preserve
    'To know when to do which, we need to check if any values exist in P() yet
    'When No Bounds have been Dimensioned on an Array, it will cause an Exception (slips my mind which, but probably a NullReferenceError)
    'So we will check and see if it 'Is Nothing'.

    For r as integer = 0 to ListView1.Items.Count - 1
    For c as integer = 0 to ListView1.Items(r).SubItems.Count - 1
    If ListView1.Items(r).SubItems(c).Text = SearchTerm Then
    'Check the state of P()
    If isNothing(P) = True Then
    ReDim P(0) 'ReDim makes the object lose all of it's content
    ReDim Preserve P(P.Length) 'The Preserve keyword makes P keep it's contents
    End If
    ' P.Length - 1 is the uppermost bound of the array, the one we just made
    P(P.Length - 1) = New Point(C, R)
    End If
    Next c
    Next r

    Return P
    'This has a case at returning a Null P() object, make sure you check
    'Example of how in next function
    End Function

    Public Sub TestIt()

    Dim PointArray() as Point
    PointArray = GetSearchResultsInArray("TestTerm")
    If IsNothing(PointArray) = True Then
    MsgBox("No Results")
    For i as integer = 0 to PointArray.Length - 1
    MsgBox("Column: " & PointArray(i).X & ", row: " & PointArray(i).Y)
    Next i
    End If

    End Sub

    hope that helps
  • hi.

    i'm very new in programming,...
    I've tried to follow your source code and able to insert a new record into the list view... but when i close and restart the program then load the database again, the record is not shown.. and when i open the cust.mdb file, there isn't any record inside....

    what should i do to keep the record in the database file?

    sorry if my english isn't that good...

    thank you...
  • seancampbellseancampbell Pennsylvania, USA
    This post doesnt really cover using a program to access data, but instead some specific problems this poster was having... I wrote a nice post with some great code examples on using an MS Access DB with code, here it is:

    Sorry for the delay in response
  • thanks for your reply.

    i finally managed to modify the source code with my friend's help..
    now the database is working properly...

    thanks again..
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!