MS Access to VB. NET conversion journal/checklist
OK Folks,
I can't find much help for this task so I thought I'd create a step-by-step journal to help simpletons like myself that prefer to be led by the nose ring, people who consult instructions as a last resort. This will be an evolving work as I convert my specific MS Access coding style to VB .NET. I have no idea whether people have stuck tenaciously to a 3GL mindset even in an OO environment like myself but I'm sure there must be some fellow subborn dinasaurs out there. This journal/instruction check list will no doubt contain learning errors as I go along, hopefully I'll be able to edit as I correct these mistakes and improve on my methodology for others.
Everybody feel free to contribute.
Target Audience
Besides above, programmers proficient in MS Access but poor in VB .NET and OO.
Aims
Get the MS Access application compiling first, working second, neat and efficient last of all.
Some Sources
http://www.devcity.net/Articles/268/1/article.aspx
http://www.granite.ab.ca/access/sqlserverupsizing.htm
Discussions and links in the Access forum
So, here goes:-
First the easy parts (go for those numerous small wins first!).
Forget an upgrade wizard, i can't find one. Bugger!
Start a new project
Holy heck! I can't even cut and paste generic controls to forms! Oh well . . .
Import the MS Access modules
Copy the module its VB .NET equivalent
Menu->Project->Add module->
Name it the same as your existing Access module.
Paste the code between the Module and End Module lines
Now the following process that will likely be repeated for all your imported Form/Report code
Do this for all your modules and form code
Find and replace the words
"Global" with "Public"
"Variant" with "Object"
"RecordSet" with "DataSet" . . . for now. . . I think I'll be coming back to this one
"Isnull(" with "IsDBNull("
"IsEmpty(" wtih "IsNothing("
Replace your Type definitions with Sturcture definitions similarly to this example
from
Type MyCustomer
Name as string*20
...
End Type
to
Public Structure MyCustomerType
Dim Name as string
...
End structure
Notice that VB doesn't support fixed length strings, so lop off the *20 as shown
Dim CustomerName as string*20 changes to
Public CustomerName as string
Now if you code like myself then you will have many lines similar to
Dim gblDB as database, rs as recordset
Set gblDB=CurrentDB() (also was equivalent to gblDB=DBEngine(0)(0) in Access 97)
Set rs = gblDB.OpenRecordset("Customers")
rs.MoveFirst
In order to map the above code to VB .NET you need to add System.Data.OleDB to your references (this is much like Access) which contains the code for the low level programming routines that we need.
The first part of the above code I have now translated to
Dim gblDB as OleBDConnection, rs as DataSet
gblDB = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Users\Common\CustomerData.mdb")
gblDB.open()
Now this sets up the connection to my external data (I'll figure out internal data later . . . if it is available) much like Access. Now I know that I'll need to hook up to many tables (aka "recordsets" or "views") within this database so I have constructed the following function to make things easier for me. Stick this code in a module:-
Dim gblDB As New OleDbConnectionDim rs As New System.Data.DataSet()
Public Function LoadRecordData(ByRef MSAccessRecordsetTypeObject As DataSet, ByVal TableName As String) As BooleanDim myComm As OleDbCommand = New OleDbCommand("SELECT * FROM " & TableName, gblDB)
Dim gblDataAdapter As New OleDbDataAdapter
gblDataAdapter.SelectCommand = myComm
gblDataAdapter.Fill(MSAccessRecordsetTypeObject, TableName)
LoadRecordData = True
End Function
Now to create my Access recordset type object all I have to do is use the function
LoadRecordData(rs, "Customers")Yes, you can see I haven't got around to considering errors yet.
Now the big difference between Access and VB .NET (or VB ADO.NET . . . edit?) is that .NET uses dissconnected "virtual"( or "shadow") arrays instead of live record data. Thus
- instead of accessing fields and records I have to access array rows and columns
- Additionally the shadow array has to update its source table. ie VB loads the data into an array, the program or user operates on that array and then the program or user initiates a save or update process to transfer the changes in the array back to the source data.
Shades of Begin and End Transactions! The number of rows in a sample MS Access type recordset is found by a command+reference as follows
Debug.Print("Rows = " & rs.Tables.Item("Customers").Rows.Count.ToString)So now for my next task is to determine how I will mass change my code so that code like the following
rs.AddNew
rs![Customer Name]="Mr. Try Hard"
rs.update
turns into
rs.Tables.Item(
"Customers").Rows(UserRequiredRowNo).Item("Customer Name")="Mr. Try Hard" PLUS the associated ADO .NET saving operation!
Additionally I now have to control record selection via an Integer or Long (eg my variable UserRequiredRowNo here).
Crumbs, this is going to be a big project!
12 Oct 06 - current situation is . . . after almost going blind changing all my code and at last getting it to compile I came across a problem updating a text box from another thread (the text changes but not visibly, ie the screen control doesn't update) and haven't been able to solve the problem to my satisfaction. Changing a textbox and having it reflected on the screen is a fairly rudamentary part of my code and I am pretty dissapointed with VB.NET here.
I have also wanted to investigate the upsizing wizard which seems to convert only to SQL Server (something I am not well enough equainted with) but which also apparently upgrades the access forms to windows forms. This is something that may quite possibly benefit me greatly.
Anyhoooo I'm going to cease this thread for a while. Apologies.
