Retrieving Data from an Access Database
There are a number of easy ways to programmatically retrieve data from an Access database in .NET. The DataReader object is one of the easiest, especially when you need to loop through the data being returned to carry out some calculation or function.
The first step is to create and open a connection to the database using the technique shown in a previous post.
'declare a DataReader
Dim dr As OleDbDataReader 'For an Access database
'populate the datareader with the results of the command
dr = cmd.ExecuteReader()
'create a loop to read values from the datareader
While dr.Read()
'gain access to the fields using something like below
Dim strValue As String = dr(fieldname).ToString()
End While
'close the read when done
dr.Close()
Adding Parameters to Access Queries
Option 1:
Create a Parameter object and add this to an existing Command object.
'Create a new parameter
Dim myParam As New OleDbParameter("ParamName", paramValue)
'add the parameter to the command
cmd.Parameters.Add(myParam)
Option 2:
Use the AddWithValue option to add a Parameter directly to the Command object.
'create, assign value and add a new parameter to the command
cmd.Parameters.AddWithValue("ParamName", paramValue)
Connecting to an Access database
Class Declarations
Imports System.Data.OleDb
Function Code
'declare the Sql data controls
Dim conn As New OleDbConnection()
Dim cmd As New OleDbCommand
'declare and initialise the connection string
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|Users.mdb"
'assign the string and open the SQL connection control
conn.ConnectionString = strConnection
conn.Open()
'set the command type depending on the query being used
'For dynamically created SQL
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM tablename WHERE FilterBy LIKE @ParamName"
'set the connection to use with the command
cmd.Connection = conn
'utilise the command in one of the methods below...
Reading from Text Files
Reading from an existing file is a very simple extension of the code example we used in Working with Text Files, except this time we will utilise the ReadLine function of the TextStream object from FileSystemObject to return a string containing the first line from our file.
Reading the First Line of Text
'open and read the contents of an existing file
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream
'open an existing text file
Set fsoStream = fso.OpenTextFile(strFileName, ForReading)
'read a line to the file and display in a message box
With fsoStream
End With
If everything worked you will get the following message. If not, make sure that your text file exists and that it has at least one line of text.
Of course in most cases we want to read more than just the first line of the file. For this we add a simple Do While…Loop and reference the property AtEndOfStream which tells us that the end of the file has been reached. Normally we would read each line and run some kind of actions on it, such as adding it to the database or checking the value against some previously stored values – feel free to add these at the TODO line.
Reading Every Line of Text
'open and read the contents of an existing file
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream
'open an existing text file
Set fsoStream = fso.OpenTextFile(strFileName, ForReading)
'read a line to the file and display in a message box
With fsoStream
Do While Not .AtEndOfStream
'TODO: add some code here to process each line
MsgBox .ReadLine
Loop
End With
Working with Text Files Tutorials
Reading and writing from text files may seem antiquated to the modern developer, after all didn’t we invent database management systems to avoid using flat files in the first place? There are however many times when it is useful and even absolutely necessary to read and write to text files especially when transferring data between legacy systems or proprietary systems that don’t offer direct access to their underlying databases.
Thankfully the FileSystemObject contains a number of excellent methods for opending, reading and writing to text files.
Writing to Text Files
You will find quite a number of examples on the web where the FileSystemObject is not used to write to a text file, which of course works, but to be thorough in our discussion about the FileSystemObject class we’ll look at the slightly more complex using a TextStream.
When you were in the Object Browser you may have noticed that the both the CreateTextFile and OpenTextFile functions of the FileSystemObject class returned a TextStream. We can use this TextStream to add additional lines of data to a new or existing text file.
Creating and Writing to a Text File
'create a new text file and write a line of text
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream
'create a text file - overwriting any previous file
Set fsoStream = fso.CreateTextFile(strFileName, True)
'write a line to the file
With fsoStream
.WriteLine "Hello World!"
End With
When you were in the Object Browser you may have noticed that the both the CreateTextFile and OpenTextFile functions of the FileSystemObject class returned a TextStream. We can use this TextStream to add additional lines of data to a new or existing text file.
Opening and Writing to a Text File
'open an existing text file and write a line of text
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream
'create a text file - overwriting any previous file
Set fsoStream = fso.OpenTextFile(strFileName, ForWriting, True)
'write a line to the file
With fsoStream
.WriteLine "Hello World!"
End With
'close the text file
fsoStream.Close
If you run the example above you will notice that no matter how many times you run it, you only end up with one Hello World! in the text file. This is because the ForWriting option, despite the sensible sounding name actually clears the test file when you open it. Not a particularly useful function if you were hoping to keep your previous information. In this case you will need to use the ForAppending option.
Getting Information about your Files
Having come this far, with a complete list of Drives and Folders, the next logical step is to see if we can find out anything useful about what Files our Folders contain. Thankfully once again the FileSystemObject contains a very useful method called Files that returns a collection of files within the current folder.
Building on the example we used when dealing with Folders using the FileSystemObject, we can now gather a raft of information about the files located in a particular folder using the Files collection of the FileSystemObject.
'declare the starting or root folder – you could get this from the Drives
Dim strRoot As String
strRoot = "C:\"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim parent As Scripting.Folder
Dim children As Scripting.Folders
Dim child As Scripting.Folder
Dim fl As File
'get the root folder from fso
Set parent = fso.GetFolder(strRoot)
'get the subfolders contained within the root folder
Set children = parent.SubFolders
'iterate through the subfolder under the root and display some data
For Each child In children
'iterate through the filesFor Each fl
In child.Files
Debug.Print fl.Name & " " & fl.Type
Next
Next
Of course, sometimes you already know the location of the file you want information about, so the FileSystemObject includes another function called GetFile that takes as its parameter the full path and filename of the file.
'declare the starting or root folder – you could get this from the Drives
Dim strFilePath As String strFilePath = "C:\Temp\MyWorld.txt"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fl As File
'get the file from fso
Set fl = fso.GetFile(strFilePath)
'display some information about the file
Debug.Print fl.Name & " " & fl.Type & " " & fl.Size
'TODO: add some code to move, copy or delete the file
'you can even open it as a TextStream to read the contents
Getting Information about your Folders
Just knowing the drive letters on your computer isn’t very exciting, but using this information to display a list of folders starts getting a little more useful. The GetFolder method of the FileSystemObject returns a Folder object, which contains a whole lot of useful information about the folder, including its name, size and a collection of subfolders.
You may on occasion need to display or work with a list of sub-folders located within a parent folder. Thankfully, as with finding out what Drives your computer has access to, working with fodlers is easy using the Folders property of the FileSystemObject.
'declare the starting or root folder – you could get this from the Drives
Dim strRoot As String
strRoot = "C:\"
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim parent As Scripting.Folder
Dim children As Scripting.Folders
Dim child As Scripting.Folder
'get the root folder from fso
Set parent = fso.GetFolder(strRoot)
'get the subfolders contained within the root folder
Set children = parent.SubFolders
'iterate through the subfolder under the root and display some data
For Each child In children
Debug.Print child.name
Next
Getting Information about your Drives
As you have no doubt discovered as you carefully analysed the Scripting library in the Object Browser, there are far more items listed than just the TextStream class and the OpenTextFile method. Using the FileSystemObject you can retrieve a wealth of information about the current computer, including information about available drives, folders and files.
You may on occasion need to display a list of drives the current computer user has access to, so that you can store information about the location of various files used within your Access database. This is very easy using the Drives property of the FileSystemObject.
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim drvs As Scripting.Drives
Dim drv As Scripting.Drive
'get the drives from fso
Set drvs = fso.Drives
'iterate through the drives and display some data
For Each drv In drvs
Debug.Print drv.DriveLetter & " " & drv.ShareName
Next
You will notice that there is a myriad of properties that you can discover about each Drive, including AvailableSpace and FileSystem, such as FAT and NTSC. Try a few out in the Debug line for the example above and then impress you friends!
Introducing the FileSystemObject
The FileSystemObject class contains the methods and properties required when interacting with your computers file system and is also the primary tool we will use to read and write text files. The FileSystemObject is a class within the Microsoft Scripting Runtime library, contained in the scrrun.dll.
Adding a Reference to the FileSystemObject
To help you get starting using the FileSystemObject it is a good idea to add a reference to the Microsoft Scripting Runtime library in your Access project (using Tools\References on the menu bar). That way you can browse the various methods and properties available in the Object Browser as well as taking advantage of IntelliSense, which helps avoid typos. We will use this method in our examples, however if you are deploying your finished database it is always best to avoid superfluous references, so we recommend you use late binding, which we will demonstrate later in the article.
Late Binding the FileSystemObject Class
As mentioned previously, if you are deploying your database to others, it is best to limit the number of references your database uses. That is unless you have a particularly sadistic attraction to pain and suffering. To use late binding, follow the example below and remove any declarations using the System library, creating instead variable of type Object.
'replace an variables creating an instance of the Scripting library
'Dim fso As New Scripting.FileSystemObject
'Dim fsoStream As Scripting.TextStream
Dim fso As Object
Dim fsoStream As Object
'initialise the FileSystemObject by using the CreateObject method
Set fso = CreateObject("Scripting.FileSystemObject")
You will also need to replace the IOMode constants used in the OpenTextFile function with their integer values.
ForWriting = 2
ForAppending = 8
ForReading = 1
After you have done this, remove the reference to the Microsoft Scripting Library and make sure your project compiles. Now you are free to explore the many facets of this powerful object.