MS Access to Attributes

I have an access database with 1400 plant names and a block with a PlantID attribute.

The block are inserted into the drawing first, i then wish to browse the database for a plant name, copy it (they are all in Latin, getting the spelling correct is a pig), and paste it to multiple selected blocks, not all blocks, just those i select.

I could use Global Attribute edite to change multiple blocks but can't get copy and paste to work, i am sure either Lsp or more likely VBA is required to make this work and i am not very experienced with this type of programming, does anybody know a way of doing this?

I am working on 2010, windows 7 64bit.

The Global Attribute Maro script i have is ^c^c^c_-attedit;n;y;tree;plantid;;\;;\; but if there is a better way i am very open to suggestions.

Welcome to the forums!

This should get you started:

(defun c:FOO (/ access)
 (vl-load-com)
 (if (setq access (vlax-get-or-create-object "Access.Application"))
   (progn
     (textpage)
     (vlax-dump-object access T)
     (vlax-release-object access)))
 (princ))

That's pretty cool, I've done a fair bit of Access and a Bit of ASP so most of the terms are familiar, Writing LSP or VBA is new to me, i have adapted code before but very simply. Where do i start?

I guess i need to connect to a database (?), and find a particular plant name. Is this better done directly through access or by creating a VBA form in AutoCAD that links to the database?

I will then need to select the plant, and copy the text. Also VBA Code?

Back to AutoCAD, Select the Blocks/Attribute Tags i wish to Update, then Paste the text? This last bit i believe could be done using _-ATTEDIT (^c^c^c_-attedit;n;y;tree;plantid;;\;;\ but i'm not sure how this would be written in LSP or VBA.

This is a learning experience for me, but i guess i am not a complete newby, just unfamiliar with the LSP and VBA Commands.

The code I posted uses the ActiveX COM API (the same as VBA).

Everything you need can be written within the Visual LISP Integrated Development Environment (VLIDE). As Visual LISP uses the same object-oriented API, you simply need to step through the specified (and implied) tasks to achieve your goal. Visual LISP can even populate the attribute values you need, simply break down your code into logical segments, and tackle them one-at-a-time.

Get the Access Application object >> Get the document/database object >> get the values needed >> get the block/attributes needed >> populate the attribute values >> release the Access database >> release the Access Application, etc.

Hope this helps!

Cheers RenderMan, This does help a lot. A couple more questions, i appreciate your time.

Get the Access Application Object and Values = i guess this would require a form within CAD, or does it use the Access interface?

Then i guess i would need some code to manually select the blocks/Attributes that need to be populated (the block being 'Tree' the attribute being 'PlantID'), what would this code look like?

If ActiveX COM API is the same as VBA, i guess it allows the creation of forms, do you use the same Visual Basic Editor to create the code?

H GregH

I wrote below (with mixed input from other forum users) a while back for a project it should help you get the ball rolling.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;			SQL SEVICES				    ;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(vl-load-com)
;Global DB conneciton
(setq $DB_CONNECTION)
;DB File path
(setq $DB_LOCATION "c:SAMPLE FILE PATH/SAMPLE.mdb")
;DB Record set
(setq $DB_RECORD_SET)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Open DB Connection				;
;	Open connection to MS Acces DB		;
;						;
;						;
;  RETRUN nil if failed to open either object	;
;  RETURN list: connection, recordset		;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun openDbConnection ( / )
 ;Create connection and record set
 (setq $DB_CONNECTION   (vlax-create-object "ADODB.Connection")
      $DB_RECORD_SET (vlax-create-object "ADODB.Recordset"))
 (vlax-invoke-method $DB_CONNECTION
                    "Open"
                    (strcat "Driver={Microsoft Access Driver (*.mdb)};DBQ=" $DB_LOCATION)
                    "Admin"
                    ""
                    0) 
 ;If successfully connected return list of objects
 (if(and $DB_CONNECTION $DB_RECORD_SET)
   t
   nil
 )
)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Close DB Connection				;
;	Close connection to MS Acces DB		;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun closeDbConnection ( / )
  (vl-catch-all-error-p(vl-catch-all-apply (vlax-invoke-method $DB_CONNECTION 'close)))
)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Get Sql Results				;
;	Return nested list of results from a sql;
;	query.					;
; sql = sql query to execute			;
; returnFields = list of fields 		;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun getSqlResults( sql returnFields  / results column fields)
 (vlax-invoke-method $DB_RECORD_SET "Open" sql $DB_CONNECTION 1 3 1)
(setq fields (vlax-get-property $DB_RECORD_SET "Fields"))
(while (= (vlax-get-property $DB_RECORD_SET "BOF")
          (vlax-get-property $DB_RECORD_SET "EOF")
          :vlax-false
       )
 (setq
  results (cons
            (mapcar
              (function (lambda (x)
                          (vlax-variant-value
                            (vlax-get-property (vlax-get-property Fields "Item" x) "Value")
                          ) 
                        ) 
              ) 
              returnFields
            ) 
          results
         ) 
 ) 
 (vlax-invoke-method $DB_RECORD_SET 'MoveNext)
)
(vlax-invoke-method $DB_RECORD_SET 'close)
results
)



(defun InsertEntries(sql / )
 (setq sqErr sql)
 (foreach c sql
   (vlax-invoke-method $DB_RECORD_SET
                    'open
                     c 
                    $DB_CONNECTION
                    1
                    3
                    1)
 )
)

;===============================================;
;Create Sql Statement				;
; Create insert statement for sql db		;
;						;
; Params: 2 element list = string 		;
;	  3 element list = number		;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun createSqlStatement (table params /  i statement)
 (setq statement (strcat "INSERT INTO " table " "))
 (setq fields "")
 (setq values "")
 (setq i -1)
 (while (< (setq i  (1+ i))(length params))
   (if(< i (1- (length params)))
     (progn
       (setq delimiter ", ")
(setq Fields (strcat fields " "(car (nth i params)) ", "))
     )
     (progn
       (setq delimiter "")
(setq Fields (strcat fields " "(car (nth i params)) ""))
     )
   )
   (if(= (length (nth i params)) 3)
     (setq encap "'")
     (setq encap "'")
   )
	  
   (setq values (strcat values
		      encap
		      (if (and(= (cadr (nth i params)) "")(= (length (nth i params)) 3))"0" (cadr (nth i params)))
		      encap
		      delimiter))

 )
 (setq statement (strcat statement "(" fields ") VALUES (" values ");"))
)

To any user that helped in the creation of these methods I'm sorry I haven't attributed credit where due. If you let me know I'll edit this post accordingly/

SOliver.

SOliver,

I'm sitting here going round in circles.

This creates a database connection? and gathers the data? How does it display the data?

You could use something like this to select and edit block references once you've retrieved the results

 ;Select by window, filter to contain block references
 (setq sset(ssget "W" '((0 . "INSERT"))))
 (setq i -1)
 ;With each block reference in sset
 (while (<(setq i(1+ i))(sslength sset))
   ;Convert to vla-object
   (setq ent(vlax-ename->vla-object (ssname sset i)))
   ;Check that the block has attributes
   (if(= (vla-get-hasattributes ent) :vlax-true)
     (progn
       ;Method to determine if attribute values should be updated
;,,,,,,,LOGICAL TEST HERE
 ;Method to update attribute values
     )
   )
 )

The bit within the progn statement is largely down to how you wish to identify blocks which should be updated.

Of possible use methods. You may find several of the list manipulation methods in the last post of this thread useful.

If you could define the logical process of identify blocks that require updating I can go a bit further into the update if required.

Hope this helps,

SOliver.

SOliver,

I have just posted this on the VBA Forum which should make things clearer, you might notice from my questioning i am a novice at this sort of programming but i am very willing to learn.

I have a script to run global Attribute edit

Command: -ATTEDIT

Edit attributes one at a time? [Yes/No] : N

Edit only attributes visible on screen? [Yes/No] : Y

Enter block name specification : Tree

Enter attribute tag specification : PlantID

Enter attribute value specification :

1 attributes selected.

Enter string to change: .

Enter new string:

this runs off a icon ^c^c^c_-attedit;n;y;tree;plantid;;\;;\;

I am trying to run it from a VBA form, activated by the icon.

Firstly I would like it to replace the whole string to change regardless of what is entered there, if that is possible.

The form would also have a text box to enter the new string if you know it (as a code), and a browse button if you don't.

Clicking the browse button would open a second form that is connected to a database (Plant.mdb) which has plant names and codes that you can select. on selection it fills out the text box for you.

Thankyou for your help, my problem is that i am unfamilliar with the terminology in the code. Do you know of a good training source?

I'd recommend http://www.jefferypsanders.com orhttp://afralisp.com as good all round. tutorail for lisp. One of the other forum member Lee Mac also has a crackin' tutorial site ideal for beginners http://lee-mac.com/

I think I get what you are looking for. Unfortunately I haven't spent any time working with DCL (Dialog Control Language) which would be required if this were to be done with lisp. If I get a chance to look into it I'll see what I can do.

Hi I'm, trying to use the code Blackbox were using to help GegH mixed with some fleming-group.com code. Is it really necessary to use the ADO connection commands if I'm already connected using the dbconnect command?

Edit:

I have an ADOLISP library files. Most of it is just adapting VB functions to Visual LISP and importing ADO library from the registry.

I how is being applied the loops on these answers with the code suggestions (I have experience on VBA for excel and trying to learn LISP) but I can't see how it's being attributed to the variable defined as string to the "CAD BLOCK ATTRIBUTE".

I'll try to work on a code based on an asnwer about how necessary is the ADO connection since DBCONNECT is working and the DB tables are already visible to my CAD interface.

Regards,

Eron