topic Re: in VBA Forum

Access database and AutoCAD 2000

Anonymous — Mon, 01 May 2000 19:09:33 GMT

Re: Access database and AutoCAD 2000

Anonymous — Mon, 01 May 2000 21:03:30 GMT

Whats the easiset way for Access '97 and AutoCAD 2000 to interact as far as Extracting information out of AutoCAD drawings and having it show up in Access.
I know how to control ACAD in VB/VBA using attributes and so forth. But is there an easier way like ODBC or something.

Re:

Anonymous — Mon, 01 May 2000 21:39:10 GMT

Douglas,

You can use the Data Access Object (DAO) in Windows by simply referencing it
into your VBA project. ([Tools/References] then select "Microsoft DAO 3.x
Oject Library".)

This will allow you to use either an ODBC or Jet Database Engine connection
to insert/update/select data from the MDB file directly. You may have to
learn a little about the DAO, but that probably would be time well spent if
you plan to work with databases in the future--and a ton of information is
available on it.

Here is some code that uses the Jet Database Engine. I pulled it from an old
example I wrote, and it probably will not work in it's current state.
(paths, filenames etc.) but it shows how to open you MDB file using the DAO.

>>>>>>>>>>>>>>>>>>>>>
Option Explicit

Public gws As Workspace
Public gdb As Database
Public gstrDBName As String

Public Function OpenPartsDatabase(strDatabasePath As String) As Boolean
OpenPartsDatabase = False

Dim strPswd As String

On Error GoTo UnknownError

If IsFileFound(strDatabasePath) Then

' Sets password
strPswd = ""

' sets and opens the global workspace
Set gws = CreateWorkspace("PartsWorkspace", "Admin", "", dbUseJet)

' sets and opens the global database
Set gdb = gws.OpenDatabase(strDatabasePath, False, False, ";pwd=" &
strPswd)

Else

GoTo UnknownError

End If

OpenPartsDatabase = True

UnknownError:

End Function

Public Function IsFileFound(strFullFileName As String) As Boolean
IsFileFound = False

On Error GoTo TheFileWasntFound

If strFullFileName = "" Then

Exit Function

End If

If Dir(strFullFileName, vbNormal) <> "" Then

IsFileFound = True

Exit Function

End If

TheFileWasntFound:

End Function

Function drawMyCube()

ThisDrawing.Regen False

If OpenPartsDatabase("C:\DrawBox.mdb") Then

frmBox.GetPartsList

frmBox.Show

Else

MsgBox "The database coudn't be found.", vbOKOnly, "Error opening
database"

End If

End Function

Sub Test()

drawMyCube

End Sub
<<<<<<<<<<<<<<<<<<<<<

This code retrieves data from the MDB file.

>>>>>>>>>>>>>>>>>>>>>
Function GetPartsList() As Boolean
GetPartsList = False

Dim ors As Recordset
Dim strSQL As String

On Error GoTo UnknownError

strSQL = "SELECT DISTINCT PartNumber, Description "
strSQL = strSQL & "FROM Parts "
strSQL = strSQL & "ORDER BY PartNumber "

Set ors = gdb.OpenRecordset(strSQL, dbOpenSnapshot)

If ors.RecordCount Then

While Not ors.EOF

lstParts.AddItem (ors!PartNumber)
lstParts.List(lstParts.ListCount - 1, 0) = ors!PartNumber
lstParts.List(lstParts.ListCount - 1, 1) = ors!Description

ors.MoveNext

Wend

GetPartsList = True

lstParts.ListIndex = 0

End If

ors.Close

UnknownError:

End Function
<<<<<<<<<<<<<<<<<<<<<

This code updates (puts) data in the MDB file. (You would probably use an
"INSERT" query here.)

>>>>>>>>>>>>>>>>>>>>>
Function UpdateData() As Boolean
UpdateData = False

Dim strSQL As String

On Error GoTo UnknownError

strSQL = "UPDATE Parts "
strSQL = strSQL & "SET Length = " & txtLength & ", "
strSQL = strSQL & "Width = " & txtWidth & ", "
strSQL = strSQL & "Height = " & txtHeight & " "
strSQL = strSQL & "WHERE PartNumber = """ &
lstParts.List(lstParts.ListIndex, 0) & """"

gdb.Execute strSQL

UpdateData = True

UnknownError:

End Function
<<<<<<<<<<<<<<<<<<<<<

Notice that I use ANSI SQL for all my read/write opperations into the MDB
file. This will allow you to easily move this code to use a ODBC record
source if you ever want to. (A good habit.)

All you have to do is change this:
Set gws = CreateWorkspace("PartsWorkspace", "Admin", "", dbUseJet)

To this:
Set gws = CreateWorkspace("PartsWorkspace", "Admin", "", dbUseODBC)

Then you might have to change the connection string (as the following line
would not be valid for a SQL Server or Oracle connection:
Set gdb = gws.OpenDatabase(strDatabasePath, False, False, ";pwd=" &
strPswd)

I hope this helps!

Until later,

Jeremy McMahan
Premier Product Support
WW Support & Services, Autodesk
Discussion Q&A: http://www.autodesk.com/discussion

"DouglasPoston" wrote in message
news:ef04111.0@WebX.SaUCah8kaAW...
> Whats the easiset way for Access '97 and AutoCAD 2000 to interact as far
as Extracting information out of AutoCAD drawings and having it show up in
Access.
> I know how to control ACAD in VB/VBA using attributes and so forth. But is
there an easier way like ODBC or something.

Re:

Anonymous — Mon, 01 May 2000 21:40:23 GMT

ActiveX Data Objects is probably the way to go in your
situation. See "AutoCAD Database Connectivity", Scott
McFarlane, ISBN 0-7668-1640-0.

jrf
Member of the Autodesk Discussion Forum Moderator Program

In article , DouglasPoston wrote:
> Whats the easiset way for Access '97 and AutoCAD 2000 to
interact as far as Extracting information out of AutoCAD
drawings and having it show up in Access.
> I know how to control ACAD in VB/VBA using attributes and so
forth. But is there an easier way like ODBC or something.
>

Re:

Anonymous — Tue, 02 May 2000 13:51:44 GMT

Hi Jemery,

We have a similar query that does not work neither in 97 nor Access 2000.
Would you have time to try this:

strSQL = "SELECT DISTINCT PartNumber, Description "
strSQL = strSQL & "FROM Parts "
strSQL = strSQL & "WHERE PartNumber IN "
strSQL = strSQL & "(SELECT PartNumber FROM Parts WHERE PartNumber
BETWEEN 50 AND 100)"

I known the sub-query in this sample make no sense,
but if you have column PartNum in table NewParts it will.
We could not get the result even between 9 and 10 for 5 minutes.
(The Jet engine is a funny name.)

Thanks

Re:

Anonymous — Tue, 02 May 2000 14:19:51 GMT

Quan,

First of all, it will be hard to test without your MDB file... but just
looking at the SQL I have a couple of comments.

I presume that the line that reads:

"strSQL = strSQL & "(SELECT PartNumber FROM Parts WHERE PartNumber BETWEEN
50 AND 100)"

Is being executed as:

strSQL = strSQL & "(SELECT PartNum FROM NewParts WHERE PartNum BETWEEN 50
AND 100)"

Also, do you have the PartNumber field indexed in both tables? This will
improve performance dramatically.

Your SQL seems sound to me, but I always avoid using "IN" queries whenever
possible. They are almost always slower then direct comparisons.

(How big is the MDB? Is the MDB on a network share or local? How many
records are in the tables? Have you compacted the MDB lately? etc.)

--

Until later,

Jeremy McMahan
Premier Product Support
WW Support & Services, Autodesk
Discussion Q&A: http://www.autodesk.com/discussion

"Quan Kieu" wrote in message
news:ef04111.3@WebX.SaUCah8kaAW...
> Hi Jemery,
>
> We have a similar query that does not work neither in 97 nor Access 2000.
> Would you have time to try this:
>
> strSQL = "SELECT DISTINCT PartNumber, Description "
> strSQL = strSQL & "FROM Parts "
> strSQL = strSQL & "WHERE PartNumber IN "
> strSQL = strSQL & "(SELECT PartNumber FROM Parts WHERE PartNumber
> BETWEEN 50 AND 100)"
>
> I known the sub-query in this sample make no sense,
> but if you have column PartNum in table NewParts it will.
> We could not get the result even between 9 and 10 for 5 minutes.
> (The Jet engine is a funny name.)
>
> Thanks
>

Re:

Anonymous — Tue, 02 May 2000 15:49:47 GMT

You wrote:
You can use the Data Access Object (DAO) in Windows by simply referencing it into your VBA project. ([Tools/References] then select "Microsoft DAO 3.x Oject Library".)
There is no "References" under Tools In AutoCAD 2000. I know its in Access or Visual Basic.
My question is what can be done on the AutoCAD side to link to a .MDB.
Or do I have to use the this code
------------------------
Dim objAcadApp As AcadApplication
Dim ThisDrawing As AcadDocument

Set objAcadApp = CreateObject("AutoCAD.Application.15")
objAcadApp.Visible = True
Set ThisDrawing = objAcadApp.ActiveDocument
WhichDir = Direct1(dwgName)
ThisDrawing.Application.Documents.Open WhichDir & dwgName
Set ThisDrawing = objAcadApp.ActiveDocument

Re:

Anonymous — Tue, 02 May 2000 15:57:02 GMT

Yes Jeremy,

I did not think you have PartNum/NewPart in your database.
So the sample query is odd, but it should work all the same.
If you have any similar Column/Table, just try.

Our database is around 22 Meg and we have columns indexed.
There are about 120000 rows.
We also have it packed.

I have found that similar query:
SELECT DISTINCT ... WHERE x IN (SELECT y ...)
is a big fault with the Jet in Access 97 even on a small database.
Now we have Access 2000 with the same failure.

It never takes more than a few seconds if I use an ODBC driver
on the same query/database.
(Ex: ODBC = 4 secs / JET = 20 min+ and we could not wait)

You could try the query directly in MS Access.
The MSQRY32.EXE that comes with many MS products, takes only a couple
seconds.
(Aren't they all from MS ? I guess one team is busy doing the PaperClip
animation.)

Thanks for trying.

Re:

Anonymous — Tue, 02 May 2000 17:18:19 GMT

Thanks I just bought it Now on Amazon.com
It is $35.96
use the isbn and leave out the dashes
ISBN 0766816400

Re:

Anonymous — Tue, 02 May 2000 21:59:06 GMT

Sorry about that...

The [Tools] menu in question is in the VBA editor (VBAIDE) in AutoCAD 2000,
not under AutoCAD 2000 itself.

As for interactively... You could use the DBCONNECT feature of AutoCAD 2000.
(See the on-line help for further details.)

--

Until later,

Jeremy McMahan
Premier Product Support
WW Support & Services, Autodesk
Discussion Q&A: http://www.autodesk.com/discussion

"DouglasPoston" wrote in message
news:ef04111.5@WebX.SaUCah8kaAW...
> You wrote:
> You can use the Data Access Object (DAO) in Windows by simply referencing
it into your VBA project. ([Tools/References] then select "Microsoft DAO 3.x
Oject Library".)
> There is no "References" under Tools In AutoCAD 2000. I know its in Access
or Visual Basic.
> My question is what can be done on the AutoCAD side to link to a .MDB.
> Or do I have to use the this code
> ------------------------
> Dim objAcadApp As AcadApplication
> Dim ThisDrawing As AcadDocument
>
> Set objAcadApp = CreateObject("AutoCAD.Application.15")
> objAcadApp.Visible = True
> Set ThisDrawing = objAcadApp.ActiveDocument
> WhichDir = Direct1(dwgName)
> ThisDrawing.Application.Documents.Open WhichDir & dwgName
> Set ThisDrawing = objAcadApp.ActiveDocument
>

Re:

Anonymous — Tue, 02 May 2000 22:05:17 GMT

Quan,

Yeah... It is usually best to test the performance in you specific
environment with the different options.

To be honest, I have not tested nested query performance using .MDBs and/or
the Jet engine. Most of my experience has been with ODBC and SQL
Server/Oracle. Even there a nested query can significantly degrade
performance. (I think it's because the engine can't use indexes on the
results of the nested query... but that's just a theory.)

You may also want to look in your SQL manual for information about the
"HAVING" operand. Proper usage of this can often improve query performance.

:-)

--

Until later,

Jeremy McMahan
Premier Product Support
WW Support & Services, Autodesk
Discussion Q&A: http://www.autodesk.com/discussion

"Quan Kieu" wrote in message
news:ef04111.6@WebX.SaUCah8kaAW...
> Yes Jeremy,
>
> I did not think you have PartNum/NewPart in your database.
> So the sample query is odd, but it should work all the same.
> If you have any similar Column/Table, just try.
>
> Our database is around 22 Meg and we have columns indexed.
> There are about 120000 rows.
> We also have it packed.
>
> I have found that similar query:
> SELECT DISTINCT ... WHERE x IN (SELECT y ...)
> is a big fault with the Jet in Access 97 even on a small database.
> Now we have Access 2000 with the same failure.
>
> It never takes more than a few seconds if I use an ODBC driver
> on the same query/database.
> (Ex: ODBC = 4 secs / JET = 20 min+ and we could not wait)
>
> You could try the query directly in MS Access.
> The MSQRY32.EXE that comes with many MS products, takes only a couple
> seconds.
> (Aren't they all from MS ? I guess one team is busy doing the PaperClip
> animation.)
>
> Thanks for trying.
>