MS-ACCESS from VB.NET 2010

[OceanaPolynom]

Hello

I would like to see a code example of of how to connect, access, update, query an existing MS-ACCESS file from VB.NET

I don't need to show any of the data on a form or a grid control.  I only need to manipulate the data from inside VB.NET.  The results are to be drawn in AutoCad or written out as text files.  I couldn't find an answer by searching the site.

Thanks

John

[Anonymous]

Hi there,

Pleaze look at this website. I'll think it will help you with your problem.

http://www.startvbdotnet.com/ado/msaccess.aspx

Here is  a sample from that page using a console application to retrive records from an acces database.

Imports System.Data.OleDb
Imports System.Console
Module Module1

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader

Sub Main()
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\emp.mdb;_
Persist Security Info=False")
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
WriteLine(dr(0))
WriteLine(dr(1))
WriteLine(dr(2))
'writing to console
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub

End Module 

 Goodluck

Irvin

[norman.yuan]

Addition to Irvin's reply.

If you use 64-bit AutoCAD, then the code will not work inside AutoCAD at the line

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\emp.mdb;_
Persist Security Info=False")

because Jet Engine (Jet driver) is only of 32-bit. If you have to access the database file from inside 64-bit AutoCAD, you have to download 64-bit MS Access DB Engine and install it. Be aware, though, if you use MS Office 2007/2010 32 bit with MS Acces included, you cannot install 64-bit MS Access DB Engine in the same computer.

Also, if the Access DB is built on MS Access 2007 (*.accdb/*.accde), you cannot use Jet Engine either. You need to download MS Access DB Engine and change the connectionString in the above code accordingly.

[OceanaPolynom]

Hello

First of all thanks a lot.   This look like exactly what I need.  I am trying to get it to work but I have many things to learn.

Maybe I'll have some questions later.

 John

[OceanaPolynom]

Hello

I am working in 32 bit, XP service pack 3, VS2010, MS-ACCESS file is 2003 ver.  Autocad is not an issue yet.

After looking at the site you mentioned I created a WindowsApplication, added a form, button and 3 textboxes.

I opened the project properties window and on the Compile tab changed the Target framework to .NET Framework 3.5

On the References tab I noticed that there is no reference to System.Data.OleDb.  I attempted to add a reference but couldn't find it in the Add Reference window.

Trying to build the code results in "end of statement expected on line 3." error.  Line 3 is: Dim cn As OleDbConnection

This is the code:

Imports System.Data.OleDb
Public Class Form1 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\emp.mdb;")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
' loading data into TextBoxes by column index
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub
End Class

What am I missing?

Thank you all

John

[Anonymous]

Please look here.

http://msdn.microsoft.com/en-us/library/wkze6zky.aspx

Kind regards,

Irvin

[OceanaPolynom]

Hello

After reading the page you linked I think that my problem is about:

"A component that uses a recent version of the .NET Framework is incompatible with a project that targets an earlier version of the .NET Framework.

For information about how to change the target .NET Framework version for a project, see How to: Target a Specific .NET Framework Version or Profile.

A component that uses .NET Framework 4 is incompatible with a project that targets the .NET Framework 4 Client Profile. "

I have tried changing the .NET Framework to all the recent versions but in no case am I able to reference

System.Data.OleDb

which seems to be the problem, so I am still unable to successfully build the project.

Thank you

John

[Anonymous]

Are you using the express edition of VS2010. I might be a limitation of that version. I all out of suggestions. Sorry

[Anonymous]

I googled and came up with this wikilink:

http://en.wikipedia.org/wiki/Microsoft_Visual_Studio_Express

No IDE support for databases other than SQL Server Express and Microsoft Access

Using the oleDB you could connect to other databases.

So i think when you use the express edition this is where your problem comes from.

Kind regards,

Irvin

[Anonymous]

This is not a direct solution, but it shows that an Access DB data-bound Form can be made inside VB.NET or C# Express 2010 without writing any code at all.  It's the semi-automagic wizard method!


Start IDE

Select New Project

Select Windows Forms Application

Top Menu Bar > Data > Show Data Sources (Shift-Alt-D)

Data Sources treeview {empty}, (left-hand side) > Add New Data Source

"Choose Data Source Type" Dialog > Database

"Chose a Dataset Model" Dialog > Dataset

"Choose your Data Connection" > [New Connection...]

Change Data Source to "Microsoft Access Database File"; (Data provider changes to ".NET Framework Data Provider for OLE DB")

"Add Connection Dialog" Dialog > Database File Name [Browse...] (navigate to .mdb or .accdb file; let's say My Documents\Dababase1.accdb)

Enter login & password if applicable, then [Test Connection], then [OK] if good...

Preview connection string for interests' sake, then [Next >]

You now have the option of copying the datasource (actual database file) to the VB project directory every time the application is run.  Read the info scrreen carefully before you make your decision.  I selected [No].

The connection string will now be saved to a special project setting, i.e. "Database1ConnectionString".  Click [Next...]

Select any Tables or Queries at this point that will be used, and click [Finished]


You can now add data-bound fields directly to your Form.  Simply Drag-and-Drop the objects in the Data Sources treeview onto the new Form.

- Dragging a Query or Table will automatically add a shiny new data navigator tool bar and a data-grid
- Dragging one field at a time will add an individual label / text-box pair bound to that field, in addition to a shiny new data navigator tool bar.

Note that prior to dragging, you can change the control type, i.e. text-box, combo-box, label, etc.

It's almost as easy as designing a form in MS Access! 

Vince T.

[OceanaPolynom]

Hello

When I began this thread I was starting to write a program that draws contour lines.  My input is a co-ordinate text file. The points are on a square grid, but I don't know how the grid is defined, that is I don't know which 4 points define each square.  I have to work with a large number of points, up to several million.  I have written some similar things in vba and used a MS Access database to store the points outside of autocad, with some success.   I knew that speed would be an important factor in this case, so I thought that this would be a good time to try vb.net.  After being unable to deal with the database in vb.net, I decided to write the program in vba.  The vba program works very well (see attached file) but as I thought, it takes too long to do the job.  The attached jpgout file from autocad is based on 500000 points, about 495000 squares, and took about 30 minutes to finish.   I use an empty Access file where I have predefined the tables, fields,indexes etc.  All the numbers are converted to long integers before being stored in the database.  Here are examples of the code that I use in vba to connect to and use the database file:

FileCopy "e:\John\AU-JHL1\AU-JHL2.mdb", "z:\temp3.mdb"
Set dbsObj = DBEngine.Workspaces(0).OpenDatabase("z:\temp3.mdb")
Set rstObj = dbsObj.OpenRecordset("tblSquares", dbOpenTable)
Set rstObj1 = dbsObj.OpenRecordset("tblcoorsJHL2", dbOpenTable)
Set rstObj2 = dbsObj.OpenRecordset("tblcoorsJHL2", dbOpenTable)
 .

.

.

ssql = "SELECT tblcoorsJHL2.indexjhl2 FROM tblcoorsJHL2 WHERE ((tblcoorsJHL2.yxdata=" & Trim(Str(yrso1&)) & ") And (tblcoorsJHL2.xxdata=" & Trim(Str(xrso1&)) & "));"
Set rstObj2 = dbsObj.OpenRecordset(ssql)
.

.

.

ssql = "SELECT tblcoorsJHL2.yxdata, tblcoorsJHL2.xxdata,tblcoorsJHL2.zxdata From tblcoorsJHL2 WHERE tblcoorsJHL2.IndexJHL2=" & Trim(Str(c1)) & ";"
Set rstObj2 = dbsObj.OpenRecordset(ssql)

So what I really need to know is how to do the equivalent in vb.net.  I hope that I am correct in understanding that vb.net will be significantly faster than vba.  Placing the db file on a ram disk had no effect on the speed.  Half of the run time is spent defining the squares by point id and the other half interpolating the squares and drawing plines in autocad.  Any tips on any of these subjects will be greatly appreciated.

Thank you all,

John

[dgorsman]

I'm not sure switching from VBA to VB .NET will get you the increase in speed you are looking for, especially with working out of an external database rather than using List<T> or other in-memory data storage.  With several million points to consider, the only significant time savings will be to evaluate your algorithms and scrape together *any* kind of optimization that reduces the number of loops.  For example, when triangulating a set of points you can cache the circumcircle center and radius for each existing triangle so it doesn't have to be re-calculated each time, as well as sorting the triangle lists by the X and Y circcumcircle center.  Similarly you might be able to pre-sort your "squares" into lists for each contour elevation.

Unless this is a learning exercise, at this scale you should be considering commercially available software.