Best way to get my Access Database of layers into ACAD

[Anonymous]

I have a layer standard developed in an Access Database. I would like to be
able to relate this database to AutoCAD in several ways:
1. I would like to be able to "restore" existing layers in and ACAD file to
a preset state (plot/noplot, layer color, layer linetype, etc.) as defined
in Access. This is in case users change things.
2. Each layer in the database also has a field containing it's "old name"
(we're trying to switch standards). I would like to be able to change the
layer names in existing ACAD files from the old standard to the new one.
3. My layers are catagorized (using yes/no check boxes in a field
catagorized by trade) by trade application. I would love to be able to
somehow pull data out of the database to freeze layers that are not needed
by each trade. (For instance, a civil engineer would initiate a routine that
would automatically freeze layers A-wall and A-door, but thaw and turn on
layer A-wall-extr and A-door-exrt)

I believe I'm capable of writing this out manually as an autolisp by
exporting text from th database and doing some extensive find/replace work
in Word to create the lisp files. But I would prefer if somehow a routine
could draw directly out of the database.

Is this possible? How do I go about figuring out how to do this? Thanks to
all of you for your help.

By the way, the database is completely AIA layering guideline compliant and
contains all AIA defined layers (from the '97 standard) for all trades. 430
in all. It took me 3 days to get everything entered in and formatted right.
I would be willing to exchange the actual database for some useful help.

--
Robert Asher
Staff Architect
George Butler Associates, Inc.

[Anonymous]

Robert,

The best tool for this would be ActiveX/VBA. The key to your application is
using ADO to access Access.

The basic outline of the program would be:

1. Get the layer database with ADO
2. Open up the table as a "recordset"
3. Get the collection of layers from the current drawing
4. Walk the recordset, adding or modifing the layers accourding to the
database.
5. Close the recordset, release the database.

For information on the Access ActiveX interface, open up VBA from within
Access, and look at the help file (from Microsoft). Use a liberial amount of
"Cut and Paste" from the sample code.

Good luck, it shouldn't be too bad.

Cheers,

Peter Funk
API Product Manager
Building Industry Division
Autodesk, Inc.

[Anonymous]

I posted the database to date in CF if anyone cares to take a look.
I sure would appreciate any advice and/or counselling, as it seems I've bit
off a bit more than I can chew here.
Peter, thanks for the reply. I see that I'm in way over my head, but I guess
I'll start in to deciphering what you've posted.

--
Robert Asher
Staff Architect
George Butler Associates, Inc.
"Peter Funk - Autodesk, Inc" wrote in message
news:E320FE60C42CA6AB67CB473ADE2DBC6A@in.WebX.maYIadrTaRb...
> Robert,
>
> The best tool for this would be ActiveX/VBA. The key to your application
is
> using ADO to access Access.
>
> The basic outline of the program would be:
>
> 1. Get the layer database with ADO
> 2. Open up the table as a "recordset"
> 3. Get the collection of layers from the current drawing
> 4. Walk the recordset, adding or modifing the layers accourding to the
> database.
> 5. Close the recordset, release the database.
>
> For information on the Access ActiveX interface, open up VBA from within
> Access, and look at the help file (from Microsoft). Use a liberial amount
of
> "Cut and Paste" from the sample code.
>
> Good luck, it shouldn't be too bad.
>
> Cheers,
>
> Peter Funk
> API Product Manager
> Building Industry Division
> Autodesk, Inc.
>
>

[Anonymous]

Robert,

It's pretty easy, here is a little code that will just dump your database
into the drawing as layers. The key is that you need to include a reference
to the DAO 3.6 Type Library inside your VBA project. This example is very
hard coded. Notice that I'm using rs.fields(1) to get at the first column in
your database. This just will walk the database and add the layers. It
should get you started working with the Access database and the AutoCAD
layer table.

Cheers,

Peter


Public Sub layerDump()

Dim db As Database
Dim rs As recordSet
Dim fields As fields
Dim name As String
Dim cLayers As AcadLayers
Dim layer As AcadLayer
Dim layername As String

Set cLayers = ThisDrawing.Layers

name = "c:\Layer.mdb"

Set db = OpenDatabase(name)
Set rs = db.OpenRecordset("Master AIA Layer Table")

With rs
.MoveLast
.MoveFirst
Do While True
.MoveNext
If .EOF Then
Exit Do
End If
layername = rs.fields(1).Value
If Left(layername, 1) <> "*" Then
Set layer = cLayers.Add(layername)
If rs.fields(4).Value <> "" Then
layer.Color = rs.fields(4).Value
End If
End If
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

[Anonymous]

Peter, thanks again for your great help. I've got your routine working. Now
a couple more questions:

This segment doesn't work right. My plot settings in the Access database are
yes/no checkboxes. What would the code be to get the checkboxes to be read?
This will be useful to apply to many other routines where I'm pulling
department specific layers, as I'm using yes/no checkboxes to determine what
departments a specific layer is associated with.

If rs.fields(5).Value <> "" Then
layer.Plottable = False

One more question, if I may: To have the macro restore linetypes off the
database, how does that get coded? The fields in the database are currently
text, but I think ACAD would have to have those linetypes restored by DXF
number? Perhaps I code a number key related to the text content of the
linetype? (ie CONT = 1 (or whatever the DXF code is...))

Thanks

--
Robert Asher
Staff Architect
George Butler Associates, Inc.
"Peter Funk - Autodesk, Inc" wrote in message
news:7B774217CE8D4A20F3EBBE3D51094379@in.WebX.maYIadrTaRb...
> Robert,
>
> It's pretty easy, here is a little code that will just dump your database
> into the drawing as layers. The key is that you need to include a
reference
> to the DAO 3.6 Type Library inside your VBA project. This example is very
> hard coded. Notice that I'm using rs.fields(1) to get at the first column
in
> your database. This just will walk the database and add the layers. It
> should get you started working with the Access database and the AutoCAD
> layer table.
>
> Cheers,
>
> Peter
>
>
> Public Sub layerDump()
>
> Dim db As Database
> Dim rs As recordSet
> Dim fields As fields
> Dim name As String
> Dim cLayers As AcadLayers
> Dim layer As AcadLayer
> Dim layername As String
>
> Set cLayers = ThisDrawing.Layers
>
> name = "c:\Layer.mdb"
>
> Set db = OpenDatabase(name)
> Set rs = db.OpenRecordset("Master AIA Layer Table")
>
> With rs
> .MoveLast
> .MoveFirst
> Do While True
> .MoveNext
> If .EOF Then
> Exit Do
> End If
> layername = rs.fields(1).Value
> If Left(layername, 1) <> "*" Then
> Set layer = cLayers.Add(layername)
> If rs.fields(4).Value <> "" Then
> layer.Color = rs.fields(4).Value
> End If
> End If
> Loop
> End With
> rs.Close
> db.Close
> Set rs = Nothing
> Set db = Nothing
> End Sub
>
>
>

[Anonymous]

I notice that the layers are being restored according to their access
Autonumber index number, not alphabetically. If I want to control how
they're restored, do I need to somehow renumber them in Access? Also,
they're being restored according to the Capitalizations that were originally
typed. I have the field set to display All Caps and so when I was entering
data, it was kind of sloppy (some caps, some not). Is there a way to
restore the layer names as all-caps without having to renter the data in the
database?

BTW, this is REALLY cool.

Thanks,

--
Robert Asher
Staff Architect
George Butler Associates, Inc.

[Anonymous]

Robert

For the yes/no boxes, you code just needs to be the following, as the value
is either "True" or "False":

layer.Plottable = rs.fields(5).value

For the linetype, look at the AutoCAD Linetypes collection. On of the
methods of this collection is "load" that will take a linetype name and a
file to load it from. No DXF codes!!!! Just linetype names. To set the
linetype of a layer, it is just:

layer.linetype = "layer name"

You should get the linetype name and make sure that it is loaded, if not
load it before you set a layer to it. Watch out for the "CONT" linetype in
your database.

For the capitalization, use UCase(string), a VBA function that will force
upper case on the layer name. (check out "VB & VBA in a Nutshell", by Paul
Lomax, an O'Reilly book, for a great VBA resource guide)

For the order, you could have Access sort the layers when it makes the
record set, but unless this is a problem, I won't worry about it, the order
that layers are made shouldn't make any difference.

Cheers,

Peter Funk
API Product Manager
Building Industry Group
Autodesk, Inc.

FYI, if your field names didn't have spaces, then you could use the
following syntax:

layer.Plottable = rs!PlotLayer



"Robert Asher" wrote in message
news:16DFA4E65B6C7ACF4C25DD949B611CCB@in.WebX.maYIadrTaRb...
> Peter, thanks again for your great help. I've got your routine working.
Now
> a couple more questions:
>
> This segment doesn't work right. My plot settings in the Access database
are
> yes/no checkboxes. What would the code be to get the checkboxes to be
read?
> This will be useful to apply to many other routines where I'm pulling
> department specific layers, as I'm using yes/no checkboxes to determine
what
> departments a specific layer is associated with.
>
> If rs.fields(5).Value <> "" Then
> layer.Plottable = False
>
> One more question, if I may: To have the macro restore linetypes off the
> database, how does that get coded? The fields in the database are
currently
> text, but I think ACAD would have to have those linetypes restored by DXF
> number? Perhaps I code a number key related to the text content of the
> linetype? (ie CONT = 1 (or whatever the DXF code is...))
>
> Thanks
>
> --
> Robert Asher
> Staff Architect
> George Butler Associates, Inc.
> "Peter Funk - Autodesk, Inc" wrote in message
> news:7B774217CE8D4A20F3EBBE3D51094379@in.WebX.maYIadrTaRb...
> > Robert,
> >
> > It's pretty easy, here is a little code that will just dump your
database
> > into the drawing as layers. The key is that you need to include a
> reference
> > to the DAO 3.6 Type Library inside your VBA project. This example is
very
> > hard coded. Notice that I'm using rs.fields(1) to get at the first
column
> in
> > your database. This just will walk the database and add the layers. It
> > should get you started working with the Access database and the AutoCAD
> > layer table.
> >
> > Cheers,
> >
> > Peter
> >
> >
> > Public Sub layerDump()
> >
> > Dim db As Database
> > Dim rs As recordSet
> > Dim fields As fields
> > Dim name As String
> > Dim cLayers As AcadLayers
> > Dim layer As AcadLayer
> > Dim layername As String
> >
> > Set cLayers = ThisDrawing.Layers
> >
> > name = "c:\Layer.mdb"
> >
> > Set db = OpenDatabase(name)
> > Set rs = db.OpenRecordset("Master AIA Layer Table")
> >
> > With rs
> > .MoveLast
> > .MoveFirst
> > Do While True
> > .MoveNext
> > If .EOF Then
> > Exit Do
> > End If
> > layername = rs.fields(1).Value
> > If Left(layername, 1) <> "*" Then
> > Set layer = cLayers.Add(layername)
> > If rs.fields(4).Value <> "" Then
> > layer.Color = rs.fields(4).Value
> > End If
> > End If
> > Loop
> > End With
> > rs.Close
> > db.Close
> > Set rs = Nothing
> > Set db = Nothing
> > End Sub
> >
> >
> >
>
>

[Anonymous]

> For the capitalization, use UCase(string), a VBA function that will force
> upper case on the layer name. (check out "VB & VBA in a Nutshell", by Paul
> Lomax, an O'Reilly book, for a great VBA resource guide)


Peter, my copy shipped today. My first ever non-lisp book. Just wanted to
keep you informed on my VB(A) progress. I know that you've been up at
nights wondering 🙂
--
Bobby C. Jones

[Anonymous]

Good luck Bobby!

The Lomax book with help with your syntax. For learning the AutoCAD ActiveX,
there are a number of good books out there. I borrow heavily from the
AutoCAD ActiveX help file myself (cut and paste). The nice thing about VBA
is that it can lead you in your programming. Some of the tricks that I use:

1. Always run with Option Explicit at the top of your code. This will force
you to declare your variables before you use them (Dim in VBA), but will
give you IntelleSense on them. To make sure that you use this, in the
Options dialog box, change the first two Code Settings: Auto Syntax Check I
run with off (this is helpful, but the dialog box is annoying. If you don't
get it right, VBA will make the line red), and Require Variable Declaration
should be on.

2. Make sure that you have included all the Aec Type libraries in your
project.

3. Use hunt and peck programming when you start out. A trick I use is to
enter "Dim xx as " after I hit the final space bar, I'm presented with a
list of all the objects that are available. Find one that looks interesting,
and press return. Then either type "xx." and look at the list of things you
can do with it, or place your cursor within the object name, and press F1.
This will jump to the help file, and you can look at all the things that you
can do. Then select one, and go to the example code, and rip-off as much as
you can.

4. Have fun with dialogs. Did you know that additional controls can be added
to the tool palette with a simple right click. A Microsoft Tree control is a
pretty cool tool.

Cheers,

Peter Funk
API Product Manager
Building Industry Division
Autodesk, Inc.

[Anonymous]

OK, so I've taken a baby step. I'm trying to get this routine to reference a
Query, so that I can filter out layers I don't want. (I assume if I apply a
filter to the query that it'll pass through to the VBA routine...) So I've
created a query with the fields in the same order as the previously working
Master Table. However, when I run the routine, I get a Runtime Error
'xxxxxx' "Invalid Arguement Color in |AcadLayer::put_Color"
What does this mean?
Can I work with queries the same as I've been working with the Table?
That's all I'll bother you with for now.

Thanks so much,

--
Robert Asher
Staff Architect
George Butler Associates, Inc.


Public Sub layerRestoreArchitectural()

Dim db As Database
Dim rs As Recordset
Dim fields As fields
Dim name As String
Dim cLayers As AcadLayers
Dim layer As AcadLayer
Dim layername As String

Set cLayers = ThisDrawing.Layers

name = "I:\VIZ Group\ADT_3\GBA New Building Groups Files\AIA Layering
Work\Layer.mdb"

Set db = OpenDatabase(name)
Set rs = db.OpenRecordset("Architectural Layers")

With rs
.MoveLast
.MoveFirst
Do While True
.MoveNext
If .EOF Then
Exit Do
End If
layername = rs.fields(1).Value
If Left(layername, 1) <> "*" Then
Set layer = cLayers.Add(UCase(layername))
If rs.fields(4).Value <> "" Then
layer.Color = rs.fields(4).Value
End If
End If
layer.Plottable = rs.fields(5).Value
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

[Anonymous]

Thanks for all your help & hints Peter!!! I've printed all these out and
put them in my 'folder' 🙂 I've made a conscience decision that my next
small to medium size project will be done using VBA, not lisp. I'm sure
that you'll see my name here and/or over in the VBA NG quit often 🙂
Thanks again.
--
Bobby C. Jones

[Anonymous]

Robert,

I'm not sure. What I'd do is to

> If rs.fields(4).Value <> "" Then
Dim xx as int
xx = rs.fields(4).value
> layer.Color = rs.fields(4).Value
> End If

Then run the code with a break point at layer.color =. If it fails, then
look at the value of xx (hold the cursor over "xx") and see that the value
is. You must be trying to pass trash to the color field.

Peter

"Robert Asher" wrote in message
news:B45E5E8509ACDB96024AC6064A2DD0FD@in.WebX.maYIadrTaRb...
> OK, so I've taken a baby step. I'm trying to get this routine to reference
a
> Query, so that I can filter out layers I don't want. (I assume if I apply
a
> filter to the query that it'll pass through to the VBA routine...) So I've
> created a query with the fields in the same order as the previously
working
> Master Table. However, when I run the routine, I get a Runtime Error
> 'xxxxxx' "Invalid Arguement Color in |AcadLayer::put_Color"
> What does this mean?
> Can I work with queries the same as I've been working with the Table?
> That's all I'll bother you with for now.
>
> Thanks so much,
>
> --
> Robert Asher
> Staff Architect
> George Butler Associates, Inc.
>
>
> Public Sub layerRestoreArchitectural()
>
> Dim db As Database
> Dim rs As Recordset
> Dim fields As fields
> Dim name As String
> Dim cLayers As AcadLayers
> Dim layer As AcadLayer
> Dim layername As String
>
> Set cLayers = ThisDrawing.Layers
>
> name = "I:\VIZ Group\ADT_3\GBA New Building Groups Files\AIA Layering
> Work\Layer.mdb"
>
> Set db = OpenDatabase(name)
> Set rs = db.OpenRecordset("Architectural Layers")
>
> With rs
> .MoveLast
> .MoveFirst
> Do While True
> .MoveNext
> If .EOF Then
> Exit Do
> End If
> layername = rs.fields(1).Value
> If Left(layername, 1) <> "*" Then
> Set layer = cLayers.Add(UCase(layername))
> If rs.fields(4).Value <> "" Then
> layer.Color = rs.fields(4).Value
> End If
> End If
> layer.Plottable = rs.fields(5).Value
> Loop
> End With
> rs.Close
> db.Close
> Set rs = Nothing
> Set db = Nothing
> End Sub
>
>
>

[Anonymous]

The sample code included with ADT3 is good, but certainly not comprehensive.
Is there a code to load ALL of the linetypes in the ACAD.LIN file? After
running my routine, I could purge out the unused ones later.