About Access databases and VB.NET
Access database may be one of easiest ways in VisualBasic to create database applications, without any needs of heavy instalations (no servers, just db driver MDAC) . You may also only need to install .NET and MDAC everywhere you want to install your application, and transporting data from customer to your machine requires only transfering .mdb file on your flashdisk.
Also there is few minuses:
- current problems with parallel database connections
(I wouldn't advice you to use it without some own made request serialiser) - execution speed (inserts),
- inaccessibility to compact & repair function through vb.net
(after deleting 40M from table, database file stays 40M large until it is "repaired" from Access) - and finally worthless password protection.
Namespace, objects
You will find more classes in System.Data.OleDb namespace but I will show you only some paradigms where we will use only few of them:
This is complete set which suffice us to update tables, make structural changes to them and retrieve data from complete scale of SQL:
- OleDbConnection - class representing connection to our .mdb file, used by all others classes from OleDb namespace
- OleDbCommand - represents SQL statement or stored procedure executed against db
- OleDbDataReader - this can we use to obtain data from our SQL selects
This you should know, sometimes to be better and have it more secure, sometimes to do it easier, faster:
- OleDbTransaction - this is used to secure integrity for multiple data transfers (sometimes is better to store nothing than only part of what we wanted to store - very good programmers are said to transactionise every operation - who knows)
- OleDbParameter - this can be used to set expressions within SQL statements, or only when setting SQL conflictible string expressions (parameters)
-
OleDbException - you can use it, or let System.Exception which automatically appears when adding Try-Catch exception handling to database commands.
In some cases you may use:
- OleDbDataAdapter - it can be use as whole one paradigm of accessing table (with its properties InsertCommand, UpdateCommand and DeleteCommand), but when even if you have more types of update etc, it could be still usefull for filling datagrid directly.
- System.Data: DataTable, DataSet - datatable is usefull mainly for accessing tableschema of given database, but you can use it as it is - in-memory database.
I will be glad to link some other articles about use of DataAdapter, DataTable and DataSet in connection to MS Access or at least connectible, because I've ever sufficed with Conn, Comm and Rdr classes, and my usings of second part was much diletant and primitive :)
| sample source code |
I like freeware, philosophy and art
This thread is for discussions of VB.NET and MS Access.
'compact Private Sub MenuItem9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem9.Click If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then If IO.File.Exists(ofd.FileName) Then If sfd1.ShowDialog = Windows.Forms.DialogResult.OK Then Dim JRO As New JRO.JetEngine JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ofd.FileName, _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sfd1.FileName & ";Jet OLEDB:Engine Type=5") End If End If End If End Sub