Creating an Access Database with VB .NET

VB .NET is a very cool language, although I had a hard time switching over from plain old Visual Basic. I persevered, and once I got used to the change in syntax, I was staggered by the ease with which all kinds of things could be done.

Earlier this week for example, A user came to me wanting to load a 30 megabyte text file in Access, split into three different tables. This is something they need to do every quarter. The problems were many and commonplace: multiple formats in the file; duplicate lines that needed to be deleted; superfluous columns; trailing minus signs on numbers (e.g. 123.45- ). Who ever came up with that format by the way should be beaten to death with a baseball bat.

Well once I'd written a program to parse the file and fix the various issues, I needed a way to create and access database, create the tables and insert the records into the table as I parsed them.

Unfortunately, there isn't well documented or supported way to do this, since neither ADO.NET nor ActiveX Data Object (ADO) offer a way to do this simple thing.

It can be done though, by using the Microsoft Jet OLE DB Provider and Microsoft ADO Ext. 2.7 (or 2.8) for DDL and Security (ADOX) with the COM Interop layer.

In Visual Studio, add a references to Microsoft ADO Ext. 2.x for DDL and Security in your project, then you can easily create a database using a simple function. The code example below came from FreeVBCode.com, and was adapated only a little. Change the database dbtable.mdb to whatever you want:


Function createAccessDatabase(ByVal DatabaseFullPath As String) As Boolean

Dim bAns As Boolean
Dim cat As New ADOX.Catalog()

Try
'Make sure the folder
'provided in the path exists. If file name w/o path
'is specified, the database will be created in your
'application folder.

Dim sCreateString As String
sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbtable.mdb"
cat.Create(sCreateString)
bAns = True
Catch Excep As System.Runtime.InteropServices.COMException
bAns = False
'do whatever else you need to do here, log,
'msgbox etc.

Finally
cat = Nothing

End Try

Return bAns

End Function

After that, you'll want to create the tables inside the database, and this can be done with the following code:

Sub createTable(ByVal tableName As String)

' shown below
Connect()

Dim oleDbCreateCommand As System.Data.OleDb.OleDbCommand
oleDbCreateCommand = New System.Data.OleDb.OleDbCommand()

' This will be where you set your table specs.
' Change as needed.
oleDbCreateCommand.CommandText = _
"create table " & tableName & " (fld1 varchar(7),fld2 varchar(5), fld3 varchar (5),fld4 int,fld5 double);"

oleDbCreateCommand.Connection = OleDbConnection

accessDataAdapter = New System.Data.OleDb.OleDbDataAdapter()
accessDataAdapter.UpdateCommand = oleDbCreateCommand
accessDataAdapter.UpdateCommand.ExecuteNonQuery()

' shown below
Disconnect()

End Sub

Sub Connect()
OleDbConnection = New System.Data.OleDb.OleDbConnection()
OleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbtable.mdb;"

Try
OleDbConnection.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub


Sub Disconnect()

Try
OleDbConnection.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

Now you have the Database and table(s) created, the rest is just opening the text file, parsing it, cleaning it and doing an insert for each record.If you aren't sure how to do that, let me know and I'll post the text read and access insert pieces of the code.TTFN :)