asked to create an access database

[d_m_hopper]

I have been asked to create an access database, that will allow for our estimators to see how many occurances of a 'block' exist in a drawing.

They would like to start small and see how many fixtures 'clothing rack blocks' exist.

The reason for the database is to two fold, help the estimators do there job faster, and keep records of fixture count (our stores go through renovations and remodels every two years)

I can create a database in access, the blocks have attributes, my problem is linking the two...I have never done this. I know I can use DBconnect, that is about it.

Any tips, pdfs, books, etc would be appreciated

thanks

Dennis

[d_m_hopper]

Basically I am thinking....

I need access to read the attached block and tell the person doing the query how many of these exist.

it has 2 attributes

tag fixture = 48 (items it can hold)

tag dept = juniors (section of store it is located)

for simplicity it is named fixture_48_juniors

[aaronic_abacus]

Here is a Thread for interfacing with a database.

http://forums.augi.com/showthread.php?t=52990&highlight=database

[aaronic_abacus]

Also a comma delimited text file could be opened in excell, I'm not sure if it would work in a database program.

[Opie]

You might try dbconnect. Eric Wing has provided an ATP course on it. Have a look for "ATP122 Back by popular demand! Linking AutoCAD to Access using DB connect (http://www.augi.com/education/archive.asp?page=293)".

[d_m_hopper]

You might try dbconnect. Eric Wing has provided an ATP course on it. Have a look for "ATP122 Back by popular demand! Linking AutoCAD to Access using DB connect (http://www.augi.com/education/archive.asp?page=293)".

thanks a bunch, I would be lost without some of these ATP courses :)

[d_m_hopper]

quick question?

we use 2004 currently and are upgrading to 2008 before end of year, has data extraction been upgraded? or is it up to the user? I don't know if I am asking the right question?

[d_m_hopper]

Here is a Thread for interfacing with a database.

http://forums.augi.com/showthread.php?t=52990&highlight=database

Also a comma delimited text file could be opened in excell, I'm not sure if it would work in a database program.

thanks a lot for the help, I really think I have to make this an access DB. But that does not mean I won't use your suggestions for stuff related to me only.

[Opie]

thanks a bunch, I would be lost without some of these ATP courses :)
You might also check into this thread, "Connecting to an external database".

[d_m_hopper]

thanks, I have searched the forums using multiple keywords and did not come across this thread.

seems every suggestion points to the fleming group

[jwanstaett]

Check out this Thread
It use Excel but Excel can be link to a Access database

http://forums.augi.com/showthread.php?t=7456&page=2

or this Thread it work on Access Database

http://forums.augi.com/showthread.php?p=26831#post26831

[d_m_hopper]

Check out this Thread
It use Excel but Excel can be link to a Access database

http://forums.augi.com/showthread.php?t=7456&page=2

or this Thread it work on Access Database

http://forums.augi.com/showthread.php?p=26831#post26831

I will try this tonight, will let you know....thanks

[d_m_hopper]

I can't get it to run in 07

Compile error:

Can't find project or library

[Opie]

I can't get it to run in 07

Compile error:

Can't find project or library
What are you referring to?

[d_m_hopper]

What are you referring to?


Sorry I am referring to this post from his suggested thread...I am trying to use his program (http://forums.augi.com/showpost.php?p=43872&postcount=11)

[fixo]

Here is a simple example how you could be
to create database on fly and populate the table
into this database
Tested on A2007

~'J'~


;; local defun
(defun sql-parse-list (RowData)
(vl-string-translate
"."
","
(vl-princ-to-string
(append
(mapcar (function (lambda (x)
(vl-string-subst x x (strcat "'" x "'" ", "))
)
)
(reverse (cdr (reverse RowData)))
)
(list (vl-string-subst
(last RowData)
(last RowData)
(strcat "'" (last RowData) "'")
)
)
)
)
)
)


;;======== write blocks into newly created database ==========;;

(defun C:NDB (/ acsApp ADOCn ADOrst cmdStr DBname blkInfo
En i insPt blkObj Sset tmpData x)
(vl-load-com)
(if (< (atof (getvar "acadver")) 15.05)
(progn
(alert "Programm would not work\n for this Acad version")
(princ)
)
)
(setq Sset (ssget (list (cons 0 "INSERT"))) i -1)
(repeat (sslength Sset)
(setq En (ssname Sset (setq i (1+ i)))
blkObj (vlax-ename->vla-object En)
insPt (vlax-get blkObj "InsertionPoint")
tmpData (append (list (vlax-get blkObj "Handle")) insPt)
blkInfo (cons tmpData blkInfo)
tmpData nil
)
)
(setq blkInfo (reverse blkInfo))
(setq acsApp (vlax-create-object "access.application"))
(vlax-invoke-method acsApp
"NewCurrentDatabase"
(strcat (getvar "dwgprefix") "NewDBTest"))
(vlax-invoke-method acsApp
"CloseCurrentDatabase")

(setq
DBname
(strcat (getvar "dwgprefix") "NewDBTest.mdb")

ADOcn
(vlax-create-object "ADODB.Connection")
ADOrst
(vlax-create-object "ADODB.Recordset")
)
(vlax-invoke-method
ADOcn
"Open"
(strcat "Driver={Microsoft Access Driver (*.mdb)}; DBQ="
DBname
)
T
T
T
)
(setq cmdStr
(strcat "CREATE TABLE BLOCKTABLE "
" (OBJECT_HANDLE VARCHAR(6) NOT NULL, "
" X_COORD DOUBLE, "
" Y_COORD DOUBLE, "
" Z_COORD DOUBLE "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)
(foreach lst blkInfo
(setq cmdStr
(strcat "INSERT INTO BLOCKTABLE "
" (OBJECT_HANDLE, "
" X_COORD, "
" Y_COORD, "
" Z_COORD) "
" VALUES "
(sql-parse-list (mapcar 'vl-princ-to-string lst))
)
)
(vlax-invoke-method ADOrst "Open" cmdStr ADOcn -1 3 1)
)

(vlax-invoke-method ADOcn "Close")

(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOrst ADOcn acsApp)
)
(princ "\n *** Done ***")
(gc)(gc)
(princ)
)
(princ "\ Start command with NDB...")
(princ)

[d_m_hopper]

Here is a simple example how you could be
to create database on fly and populate the table
into this database
Tested on A2007

~'J'~


;; local defun
(defun sql-parse-list (RowData)
(vl-string-translate
"."
","
(vl-princ-to-string
(append
(mapcar (function (lambda (x)
(vl-string-subst x x (strcat "'" x "'" ", "))
)
)
(reverse (cdr (reverse RowData)))
)
(list (vl-string-subst
(last RowData)
(last RowData)
(strcat "'" (last RowData) "'")
)
)
)
)
)
)


;;======== write blocks into newly created database ==========;;

(defun C:NDB (/ acsApp ADOCn ADOrst cmdStr DBname blkInfo
En i insPt blkObj Sset tmpData x)
(vl-load-com)
(if (< (atof (getvar "acadver")) 15.05)
(progn
(alert "Programm would not work\n for this Acad version")
(princ)
)
)
(setq Sset (ssget (list (cons 0 "INSERT"))) i -1)
(repeat (sslength Sset)
(setq En (ssname Sset (setq i (1+ i)))
blkObj (vlax-ename->vla-object En)
insPt (vlax-get blkObj "InsertionPoint")
tmpData (append (list (vlax-get blkObj "Handle")) insPt)
blkInfo (cons tmpData blkInfo)
tmpData nil
)
)
(setq blkInfo (reverse blkInfo))
(setq acsApp (vlax-create-object "access.application"))
(vlax-invoke-method acsApp
"NewCurrentDatabase"
(strcat (getvar "dwgprefix") "NewDBTest"))
(vlax-invoke-method acsApp
"CloseCurrentDatabase")

(setq
DBname
(strcat (getvar "dwgprefix") "NewDBTest.mdb")

ADOcn
(vlax-create-object "ADODB.Connection")
ADOrst
(vlax-create-object "ADODB.Recordset")
)
(vlax-invoke-method
ADOcn
"Open"
(strcat "Driver={Microsoft Access Driver (*.mdb)}; DBQ="
DBname
)
T
T
T
)
(setq cmdStr
(strcat "CREATE TABLE BLOCKTABLE "
" (OBJECT_HANDLE VARCHAR(6) NOT NULL, "
" X_COORD DOUBLE, "
" Y_COORD DOUBLE, "
" Z_COORD DOUBLE "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)
(foreach lst blkInfo
(setq cmdStr
(strcat "INSERT INTO BLOCKTABLE "
" (OBJECT_HANDLE, "
" X_COORD, "
" Y_COORD, "
" Z_COORD) "
" VALUES "
(sql-parse-list (mapcar 'vl-princ-to-string lst))
)
)
(vlax-invoke-method ADOrst "Open" cmdStr ADOcn -1 3 1)
)

(vlax-invoke-method ADOcn "Close")

(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOrst ADOcn acsApp)
)
(princ "\n *** Done ***")
(gc)(gc)
(princ)
)
(princ "\ Start command with NDB...")
(princ)


Would I edit this section if I wanted to find specific block attributes?

(setq cmdStr
(strcat "CREATE TABLE BLOCKTABLE "
" (OBJECT_HANDLE VARCHAR(6) NOT NULL, "
" X_COORD DOUBLE, "
" Y_COORD DOUBLE, "
" Z_COORD DOUBLE "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)
(foreach lst blkInfo
(setq cmdStr
(strcat "INSERT INTO BLOCKTABLE "
" (OBJECT_HANDLE, "
" X_COORD, "
" Y_COORD, "
" Z_COORD) "
" VALUES "

for example i have a block called JUNIORS, it has two tags

Fixture - value 24
Dept - Juniors

and how could I make that created table calculate how many of each selected block occur in the drawing...or would it be best to set up a query for that?

thanks this code is great by the way, where did you find the info to learn this?

[fixo]

I can't do it today, maybe tomorrow if
I will be have a time
Hope it's not urgent work for you
Cheers :)

~'J'~

[d_m_hopper]

I can't do it today, maybe tomorrow if
I will be have a time
Hope it's not urgent work for you
Cheers :)

~'J'~

no its not urgent, in fact I wish you had time to comment it and teach it to me....basic command lisp I get...its stuff like what you wrote that I get lost in.

I have a book by Bill Kramer and the Autocad V13 manual that I work from plus this site, any suggestions on where to go for info?

books, sites, etc...

[fixo]

Hi
Try this instead
This will create two tables in the databaase
first one is common data storage and the second
one is blocks count table you asked for
Another way to create the query, I agreed with you,
but this is not so easy...


;; local defun
(defun dxf (key lst)
(cdr (assoc key lst))
)
;; local defun
(defun group-by-first (lst / ret tmp)
(while (car lst)
(setq tmp (list (vl-remove-if-not (function (lambda (a)
(eq a (car lst )))) lst)))
(setq ret (cons (car tmp) ret))
(setq lst (vl-remove-if (function (lambda (a)
(eq a (car lst )))) lst))
(setq tmp nil))
(setq ret (mapcar (function (lambda (x)
(list (car x) (length x)))) (reverse ret)))
)
;; local defun
(defun sql-parse-list (RowData)
(vl-string-translate
"."
","
(vl-princ-to-string
(append
(mapcar (function (lambda (x)
(vl-string-subst x x (strcat "'" x "'" ", "))
)
)
(reverse (cdr (reverse RowData)))
)
(list (vl-string-subst
(last RowData)
(last RowData)
(strcat "'" (last RowData) "'")
)
)
)
)
)
)


;;======== write blocks into newly created database ==========;;

(defun C:NDB (/ *error* acsApp ADOCn ADOrst cmdStr DBname blk_data
en i ss ad an blk_lst count_list el hnd nm
resp ss tag txt x)
(vl-load-com)
(defun *error* (msg)
(cond
((not msg))
((wcmatch (strcase msg) "*QUIT*,*CANCEL*"))
(T (princ (strcat "\nError: " msg)))
)
(if ADOcn
(vl-catch-all-apply
(function
(lambda ()
(vlax-invoke-method ADOcn "Close")))))
(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOrst ADOcn acsApp)
)
(gc)(gc)
(princ)
)
(if (< (atof (getvar "acadver")) 15.05)
(progn
(alert "Programm would not work\n for this Acad version")
(princ)
)
)
(setq blk_lst nil blk_data nil)
(if
(setq ss (ssget "_X" (list (cons 0 "INSERT")(cons 2 "MENS,WOMENS,JUNIORS")(cons 66 1))))
(progn
(while
(setq en (ssname ss 0))
(setq el (entget en)
nm (dxf 2 el)
hnd (dxf 5 el)
blk_lst (list hnd nm)
an (entnext en))
(while (= "ATTRIB" (dxf 0 (entget an)))
(and (setq ad (entget an))
(setq tag (dxf 2 ad)
txt (dxf 1 ad))
(setq blk_lst (append blk_lst (list tag txt)))
)
(setq an (entnext an))
)
(ssdel en ss)
(setq blk_data (cons blk_lst blk_data)
blk_lst nil)
)
)
)

(setq blk_data
(vl-sort blk_data
(function (lambda(a b)
(< (cadr a)(cadr b))))))
(setq acsApp (vlax-create-object "access.application"))
(vlax-invoke-method acsApp
"NewCurrentDatabase"
(strcat (getvar "dwgprefix") "NewDBTest"))
(vlax-invoke-method acsApp
"CloseCurrentDatabase")

(setq
DBname
(strcat (getvar "dwgprefix") "NewDBTest.mdb")

ADOcn
(vlax-create-object "ADODB.Connection")
ADOrst
(vlax-create-object "ADODB.Recordset")
)
(vlax-invoke-method
ADOcn
"Open"
(strcat "Driver={Microsoft Access Driver (*.mdb)}; DBQ="
DBname
)
T
T
T
)
(setq cmdStr
(strcat "CREATE TABLE BLOCKTABLE "
" (OBJECT_HANDLE VARCHAR(6) NOT NULL, "
" BLOCKNAME VARCHAR(50) NOT NULL, "
" FIXTURE VARCHAR(50) NOT NULL, "
" FIXTURE_VALUE VARCHAR(50) , "
" DEPARTMENT VARCHAR(50) NOT NULL, "
" DEPARTMENT_VALUE VARCHAR(50) "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)
(foreach lst blk_data
(setq cmdStr
(strcat "INSERT INTO BLOCKTABLE "
" (OBJECT_HANDLE, "
" BLOCKNAME, "
" FIXTURE, "
" FIXTURE_VALUE, "
" DEPARTMENT, "
" DEPARTMENT_VALUE) "
" VALUES "
(sql-parse-list (mapcar 'vl-princ-to-string lst))
)
)
(vlax-invoke-method ADOrst "Open" cmdStr ADOcn -1 3 1)
)
(initget "Yes No")
(setq resp (getkword "\n *** Do you want to create blocks count table? (Yes/No) <Y>: "))
(if (not resp)(setq resp "Yes"))
(if (equal "Yes" resp)
(progn
(setq cmdStr
(strcat "CREATE TABLE BLOCKSCOUNT "
" (BLOCKNAME VARCHAR(50) NOT NULL, "
" QUANTITY VARCHAR(7) NOT NULL "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)

(setq count_list (mapcar (function (lambda (x)(cadr x))) blk_data)
count_list (group-by-first count_list)
count_list (mapcar (function (lambda (x)(list (car x)(itoa (cadr x))))) count_list)
)
(foreach lst count_list
(setq cmdStr
(strcat "INSERT INTO BLOCKSCOUNT "
" (BLOCKNAME, "
" QUANTITY) "
" VALUES "
(sql-parse-list (mapcar 'vl-princ-to-string lst))
)
)
(vlax-invoke-method ADOrst "Open" cmdStr ADOcn -1 3 1)
)
)

)
(vlax-invoke-method ADOcn "Close")

(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOrst ADOcn acsApp)
)
(princ "\n *** Done ***")
(gc)(gc)
(*error* nil)
(princ)
)
(princ "\ Start command with NDB...")
(princ)


I can't to recommend you some books because
I have just one book only on my native language
about Access programming on VBA
I'm just collected some examples/codes from
many VBA forums and then rewrote it on VLisp
You can see object model of Access in VBAIDE
Try to open database file and see help in VBA editor
There are many examples there
Another good place is John Flemming's library ADOLisp
try to search on Google
Sorry for the poor explanations

~'J'~

[jwanstaett]

Sorry I am referring to this post from his suggested thread...I am trying to use his program (http://forums.augi.com/showpost.php?p=43872&postcount=11)
Do you have Excel on your computer.

[d_m_hopper]

Do you have Excel on your computer.

yes I do, it seems like the libraries are not working together

my company is really behind on its OS, we run Excel 2000 on our drafters computers

[d_m_hopper]

Hi
Try this instead
This will create two tables in the databaase
first one is common data storage and the second
one is blocks count table you asked for
Another way to create the query, I agreed with you,
but this is not so easy...


;; local defun
(defun dxf (key lst)
(cdr (assoc key lst))
)
;; local defun
(defun group-by-first (lst / ret tmp)
(while (car lst)
(setq tmp (list (vl-remove-if-not (function (lambda (a)
(eq a (car lst )))) lst)))
(setq ret (cons (car tmp) ret))
(setq lst (vl-remove-if (function (lambda (a)
(eq a (car lst )))) lst))
(setq tmp nil))
(setq ret (mapcar (function (lambda (x)
(list (car x) (length x)))) (reverse ret)))
)
;; local defun
(defun sql-parse-list (RowData)
(vl-string-translate
"."
","
(vl-princ-to-string
(append
(mapcar (function (lambda (x)
(vl-string-subst x x (strcat "'" x "'" ", "))
)
)
(reverse (cdr (reverse RowData)))
)
(list (vl-string-subst
(last RowData)
(last RowData)
(strcat "'" (last RowData) "'")
)
)
)
)
)
)


;;======== write blocks into newly created database ==========;;

(defun C:NDB (/ *error* acsApp ADOCn ADOrst cmdStr DBname blk_data
en i ss ad an blk_lst count_list el hnd nm
resp ss tag txt x)
(vl-load-com)
(defun *error* (msg)
(cond
((not msg))
((wcmatch (strcase msg) "*QUIT*,*CANCEL*"))
(T (princ (strcat "\nError: " msg)))
)
(if ADOcn
(vl-catch-all-apply
(function
(lambda ()
(vlax-invoke-method ADOcn "Close")))))
(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOrst ADOcn acsApp)
)
(gc)(gc)
(princ)
)
(if (< (atof (getvar "acadver")) 15.05)
(progn
(alert "Programm would not work\n for this Acad version")
(princ)
)
)
(setq blk_lst nil blk_data nil)
(if
(setq ss (ssget "_X" (list (cons 0 "INSERT")(cons 2 "MENS,WOMENS,JUNIORS")(cons 66 1))))
(progn
(while
(setq en (ssname ss 0))
(setq el (entget en)
nm (dxf 2 el)
hnd (dxf 5 el)
blk_lst (list hnd nm)
an (entnext en))
(while (= "ATTRIB" (dxf 0 (entget an)))
(and (setq ad (entget an))
(setq tag (dxf 2 ad)
txt (dxf 1 ad))
(setq blk_lst (append blk_lst (list tag txt)))
)
(setq an (entnext an))
)
(ssdel en ss)
(setq blk_data (cons blk_lst blk_data)
blk_lst nil)
)
)
)

(setq blk_data
(vl-sort blk_data
(function (lambda(a b)
(< (cadr a)(cadr b))))))
(setq acsApp (vlax-create-object "access.application"))
(vlax-invoke-method acsApp
"NewCurrentDatabase"
(strcat (getvar "dwgprefix") "NewDBTest"))
(vlax-invoke-method acsApp
"CloseCurrentDatabase")

(setq
DBname
(strcat (getvar "dwgprefix") "NewDBTest.mdb")

ADOcn
(vlax-create-object "ADODB.Connection")
ADOrst
(vlax-create-object "ADODB.Recordset")
)
(vlax-invoke-method
ADOcn
"Open"
(strcat "Driver={Microsoft Access Driver (*.mdb)}; DBQ="
DBname
)
T
T
T
)
(setq cmdStr
(strcat "CREATE TABLE BLOCKTABLE "
" (OBJECT_HANDLE VARCHAR(6) NOT NULL, "
" BLOCKNAME VARCHAR(50) NOT NULL, "
" FIXTURE VARCHAR(50) NOT NULL, "
" FIXTURE_VALUE VARCHAR(50) , "
" DEPARTMENT VARCHAR(50) NOT NULL, "
" DEPARTMENT_VALUE VARCHAR(50) "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)
(foreach lst blk_data
(setq cmdStr
(strcat "INSERT INTO BLOCKTABLE "
" (OBJECT_HANDLE, "
" BLOCKNAME, "
" FIXTURE, "
" FIXTURE_VALUE, "
" DEPARTMENT, "
" DEPARTMENT_VALUE) "
" VALUES "
(sql-parse-list (mapcar 'vl-princ-to-string lst))
)
)
(vlax-invoke-method ADOrst "Open" cmdStr ADOcn -1 3 1)
)
(initget "Yes No")
(setq resp (getkword "\n *** Do you want to create blocks count table? (Yes/No) <Y>: "))
(if (not resp)(setq resp "Yes"))
(if (equal "Yes" resp)
(progn
(setq cmdStr
(strcat "CREATE TABLE BLOCKSCOUNT "
" (BLOCKNAME VARCHAR(50) NOT NULL, "
" QUANTITY VARCHAR(7) NOT NULL "
") "
)
)
(vlax-invoke-method ADOCn "Execute" cmdStr nil 1)

(setq count_list (mapcar (function (lambda (x)(cadr x))) blk_data)
count_list (group-by-first count_list)
count_list (mapcar (function (lambda (x)(list (car x)(itoa (cadr x))))) count_list)
)
(foreach lst count_list
(setq cmdStr
(strcat "INSERT INTO BLOCKSCOUNT "
" (BLOCKNAME, "
" QUANTITY) "
" VALUES "
(sql-parse-list (mapcar 'vl-princ-to-string lst))
)
)
(vlax-invoke-method ADOrst "Open" cmdStr ADOcn -1 3 1)
)
)

)
(vlax-invoke-method ADOcn "Close")

(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOrst ADOcn acsApp)
)
(princ "\n *** Done ***")
(gc)(gc)
(*error* nil)
(princ)
)
(princ "\ Start command with NDB...")
(princ)


I can't to recommend you some books because
I have just one book only on my native language
about Access programming on VBA
I'm just collected some examples/codes from
many VBA forums and then rewrote it on VLisp
You can see object model of Access in VBAIDE
Try to open database file and see help in VBA editor
There are many examples there
Another good place is John Flemming's library ADOLisp
try to search on Google
Sorry for the poor explanations

~'J'~

This is awesome.
I do search on GOOGLE and I have looked at John Flemings website. I have not tried the help menu in VBA yet.
Last night I found a book by Scott McFarlane on database programming on Barnes & Noble.com which I am considering purchasing.

Hopefully by looking at your code and others resouces, I will slowly understand what it is I need to do to create these from scratch.

*edit* I was just handed a book by one of our former programmers AutoCAD 2004 VBA, so something else to learn now!

thanks so much

[fixo]

You're welcome,
Glad to help
Happy coding :)

~'J'~

[d_m_hopper]

You're welcome,
Glad to help
Happy coding :)

~'J'~

Where is Pietari, Venäjä? Curious.

thanks again

[Opie]

Where is Pietari, Venäjä? Curious.

thanks again

Somewhere near St. Petersburg, Russia (http://maps.google.com/maps?q=Pietari,+Ven%C3%A4j%C3%A4&hl=en&safe=off&client=firefox-a&rls=org.mozilla:en-US:official&hs=Otk&lr=lang_en&um=1&ie=UTF-8&sa=N&tab=wl).

[fixo]

Somewhere near St. Petersburg, Russia (http://maps.google.com/maps?q=Pietari,+Ven%C3%A4j%C3%A4&hl=en&safe=off&client=firefox-a&rls=org.mozilla:en-US:official&hs=Otk&lr=lang_en&um=1&ie=UTF-8&sa=N&tab=wl).

Ah, you knew it :)
Most of my hometown citizens are
very likes finns

~'J'~

[Opie]

Ah, you knew it :)
Most of my hometown citizens are
very likes finns

~'J'~
I googled it. ;) I had to figure out what he was talking about first.

[fixo]

Continuing the previous theme it is possible to create query
on calculation of quantity of blocks from the table
tested in 2008 / MS Office 2003 only
See how it will work for you


(defun C:APPVIEW(/ ADOcmd ADOXcat ADOXcn ADOXview ADOXvws DBname strCmd X)
(vl-load-com)
(setq
DBname
(strcat (getvar "dwgprefix") "NewDBTest.mdb")

ADOXcat
(vlax-create-object "ADOX.Catalog")
)
(vlax-put-property ADOXcat "ActiveConnection"
(strcat "Provider='Microsoft.Jet.OLEDB.4.0';"
"Data Source= "
DBname
)
)

(setq ADOcmd
(vlax-create-object "ADODB.Command")
)

(setq strCmd
(strcat
"SELECT [BLOCKTABLE].[BLOCKNAME], Count([BLOCKNAME]) As QUANTITY "
"FROM [BLOCKTABLE] "
"GROUP BY [BLOCKNAME];" ))
; where QUANTITY is the name of newly created column in the query
(vlax-put-property ADOcmd "CommandText" strCmd)
(setq ADOXvws (vlax-get-property ADOXcat "Views"))
(setq ADOXview (vlax-invoke-method ADOXvws "Append"
"Counting"; <-- Query name
ADOcmd))
(setq ADOXcn (vlax-get-property ADOXcat "ActiveConnection"))

(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function
(lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list ADOXcat ADOXcn ADOcmd ADOXvws ADOXview)
)
(gc)(gc)
(princ)
)


~'J'~

[jwanstaett]

yes I do, it seems like the libraries are not working together

my company is really behind on its OS, we run Excel 2000 on our drafters computers

you need to change the Refferences to your rev. of the autocad type library.
as is it use Autocad 2000 type library

[d_m_hopper]

Fixo, I have been reading the developer help 'v' functions and noting them on my print out of your code....trying to learn & understand it.

I figure it can only help me, right?!

jwanstaett, I will change the library and let you know how it gos

[fixo]

Do you talking about Visual Lisp Developer Bible?
Take a look at this thread, this book is a good
place to start:

http://discussion.autodesk.com/thread.jspa?threadID=347710


For the my last lisp I just used procedure from VBA Access Help file:


; from Help
Sub Main()
On Error GoTo CreateViewError

Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog

' Open the Catalog
cat.ActiveConnection = _
"Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='C:\Temp\ACCESS\" & _
"d_m_hopper.mdb';"

' Create the command representing the view.
cmd.CommandText = "Select * From tblQueries"

' Create the new View
cat.Views.Append "AllCustomers", cmd

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing
Exit Sub

CreateViewError:

Set cat = Nothing
Set cmd = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub


Then I just rewrote it on VLIsp with accuracy step-by-step,
nothing else
You can compare these both
Of course, you need to know object model and object hierarchy
I'm so sorry, I can't explain you more

~'J'~

[d_m_hopper]

you need to change the Refferences to your rev. of the autocad type library.
as is it use Autocad 2000 type library

works fine now, thanks

[d_m_hopper]

Fixo, this is not working with 2008..can you help?

[fixo]

Fixo, this is not working with 2008..can you help?

Hm, I tested it in A2008 too
WinXP HE, MS Office 2003
worked nice on my machine...
I don't know the reason of your trouble, sorry

~'J'~

[d_m_hopper]

Hm, I tested it in A2008 too
WinXP HE, MS Office 2003
worked nice on my machine...
I don't know the reason of your trouble, sorry

~'J'~

Thats what I run as well, I will retry later....