Need help with connecting to an access (or excel) table

[Anonymous]

I have pieced together a (bloated) but very useful VBA program in AutoCad over the past 10+ years. It no longer works in the AUtocad 2009 or 2010 (So i keep a 2004 install just for it!).  In recent Autocads it seems to take a long time to finish tasks for some reason, and, there seems to always be modal focus problems (I tried the focuscontrol object but it made no difference).  So rather than trying to convert the entire, messy VBA app to VB.net, I think I would have an easier time starting over in VB.net.

The basic part of it is a listbox that gets a list of unique records from a Microsft Access table.  Then I click on a record in the listbox and can click a button on the form that inserts a block reference using the name for the block refence from the listbox current calue.  Pretty basic, right?

Well I am stumped on how to get started with VB.net to do this.  Can anyone point me to a simple, simple sample of a VB.net form that can have a listbox/combobox that connects to an MS Access table (or worst case Excel table)?  I have searched and see that 64 bit setups can be trouble. I am using Autocad 2010 (64), Windows 7 (64) and Office 2010 and VS 2008 express.  I downloaded some of the .NET samples from AUtodesk but none talk about database accessing.

Could anyone help me get started with this?  So far my "north american based" Autocad subscription help is of no help to me at all.

[Mike.Wohletz]

The Following will work to connect, but this is going to require that the app be built to run only in 32bit mode as the Jet data connector will not run in 64 bit mode.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Dim Connection As OleDb.OleDbConnection
    Dim DBCommand As OleDb.OleDbCommand
    Dim DBReader As OleDb.OleDbDataReader
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Connect()
    End Sub
    Public Sub Connect()
        Try
            Connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AllBlocks.mdb")
            Connection.Open()
            ' do something with it
            DBCommand = New OleDb.OleDbCommand("select * from AllBlocks_Master", Connection)
            DBReader = DBCommand.ExecuteReader

            While DBReader.Read
                ' we are going to select all items from the AllBlocks_Master table 
                'in the AllBlocks database and add the items @ index 0 to the list box
                ListBox1.Items.Add(DBReader(0))
            End While
            Connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
End Class

 remember that the target machine must be x86 inside of visual studio for this to work. That is not handy when you are creating an app to work as managed code inside of AutoCAD, but you can build you connection as a separate dll and then reference it into your add-in and it will work fine.

[Anonymous]

Might be a good time to look into XML for simple data files.

Excel and Access take a lot of overhead and users would have to have those programs installed.

.NET has excellent tools which can easily read and write tables or datasets.

[Anonymous]

Thanks both of you. Points well taken.  Is there any chance either of you can provide a little more guidance?  For now, I have the .NET tutorial lab projects and am dissecting them.  For example, the hello world lab that contains a userform.  And then I am trying to add some kind of an excel/access listbox or something.

Generally, .NET seems very powerful but a little more advanced than VBA.  So a more complete, simple example could get me started. Thanks again for the help.  If you could somehow zip together a Express project or something for a head start, that would be great.

I, also dont know to much about XML but am willing to check that out too.  My database requirements and fairly simple. Basic tables and records and sorting, etc.

-S

[norman.yuan]

There is good chance that Mike's code may not work for you: you are using 64-bit AutoCAD, meaning 64-bit Win 7. Therefore, the Jet OLEDB driver cannot be used. You need to install 64-bit MS Accesss database engine (free download from MS), in order to connect to *.mdb/*.accdb from 64-bit AutoCAD with .NET code.

Since you have MS Office 2010 installed, which may make things even more complicated, if Office2010 is 32-bit (very likely!), including Access2010 or not. because if you have 32-bit office installed, you then cannot install 64-bit MS Access database engine. You either remove 32-bit Office 2010, or install 64-bit Office 2010.

It is less likely you would go back to 32-bit AutoCAD, so, your first issue is to find a right technology to get data into AutoCAD. The vaible solution would be to move data into different types of source, be it XML file or other type of data file, or some sort of database, such as SQL Server/Express... If the data is in Excel sheet, you can direcctly read it via Open XML technology (OpenXML SDK), without the need to install Excel

[Mike.Wohletz]

As I stated before the only way that this will work is to build a separate dll with the functions needed to return the proper query and build with the target machine of X86. At that point you would build your AutoCAD add-in as you normally would and add reference to the database code dll and make all the calls to the database through that reference,  otherwise it will for sure fail as MS JET is not supported in a 64 bit environment.

I would skip the other options and go straight to the SQL Express method; you will have better results with expandability.

[dgorsman]

w3schools.com - best reference I have found for XML, XSD, XSLT, and a host of other things.

[Anonymous]

Class Description
One plus one usually equals two. But when we take AutoCAD® (1) and add it to Microsoft Office (1), we get something very different than two. We get four  or eight or 19 or 126. We get exponential increases in productivity and accuracy. Got Excel®? Got Word? Got Access®? Got PowerPoint®? Then you, my friend have got game. We'll show you how to take AutoCAD, Microsoft .NET, and these Office products to make a beautifully powerful solution.

http://au.autodesk.com/?nd=class&session_id=7585