MS Access
Connecting to an MS Access database requires using the OLEDB provider in the .NET Framework. The critical parts of the code are specifying the connection string and the SQL statement. The SQL statement can be any valid SQL statement that MS Access accepts. The connection string uses the standard tags, but notice that the password tag is more complex than you might expect and you can also have to specify where the system database (System.MDW) is located at. If the Access database hasn’t been secured, then leave the Id, password, and system database tags out of the connection string.
Private Sub FillDataset(ByVal myDataset As DataSet)
Dim myConnectionString As String
Dim myDataAdapter As OleDbDataAdapter
Dim SQL As String = "SELECT * FROM Customer"
Dim myDataSet As DataSet = New DataSet()
myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Xtreme.mdb;User ID=Admin;" & _
"Jet OLEDB:Database Password=pw;Jet OLEDB:System database=" & _
"C:\Program Files\Office2000\Office\system.mdw"
MyDataAdapter = New OleDbDataAdapter(SQL, MyConnectionString)
myDataAdapter.Fill(myDataSet, "Customer")
End Sub
You can use MS Access to help you build SQL statements. Use the visual query designer to build and test a query. This is a simple matter of adding the tables to the designer and linking the fields together. When finished, click on the View button in the top left hand corner and select the SQL option. This shows you the corresponding SQL statement to generate the query. Copy and paste this to your application. If you are familiar enough with SQL that you don’t need to use the visual query designer, you can still use Access to test the code you wrote. Open a new query and don’t add any tables to it. Click on the view button in the top left hand corner and select the SQL option. Copy the SQL statement from your program into the Access query window. Click on the view window again to see the query results. It will immediately tell you if there is a syntax error. If there is no syntax error, you can look at the results of the query to see if it generated the expected results.
