;; Load COM environment
;; Shows how to use expanding environment strings
;; Usage: (ExpEnvStr "%TEMP%\\MYDATA")
;; Results of sample: "C:\\DOCUME~1\\Lee\\LOCALS~1\\Temp\\MYDATA"
(defun ExpEnvStr
( strVal
/ wshShell strValRet
) ;; Create reference to Window Scripting Shell Object
;; Expand the string and any variables in the string
;; Release the Window Scripting Shell Object
strValRet
)
;; Get Program Files Folder
;; (setq strProgramFiles (ExpEnvStr "%PROGRAMFILES(X86)%"))
(setq strProgramFiles
(ExpEnvStr
"%PROGRAMFILES%"))
;; Global path for Microsoft Office
;; Check for Microsoft Office 2010 (Office14)
((/= (findfile (strcat strProgramFiles
"\\Microsoft Office\\Office14")) nil) (setq strMSOfficePath
(strcat strProgramFiles
"\\Microsoft Office\\Office14\\") strOfficeVer "14"
)
)
;; Check for Microsoft Office 2007 (Office12)
((/= (findfile (strcat strProgramFiles
"\\Microsoft Office\\Office12")) nil) (setq strMSOfficePath
(strcat strProgramFiles
"\\Microsoft Office\\Office12\\") strOfficeVer "12"
)
)
;; Check for Microsoft Office 2003 (Office11)
((/= (findfile (strcat strProgramFiles
"\\Microsoft Office\\Office11")) nil) (setq strMSOfficePath
(strcat strProgramFiles
"\\Microsoft Office\\Office11\\") strOfficeVer "11"
)
)
;; Check for Microsoft Office 2000 (Office10)
((/= (findfile (strcat strProgramFiles
"\\Microsoft Office\\Office10")) nil) (setq strMSOfficePath
(strcat strProgramFiles
"\\Microsoft Office\\Office10\\") strOfficeVer "10"
)
)
(setq strMSOfficePath
"" strOfficeVer
"") )
;; Opens an Access Database file and reports on the number of records in a table and
;; the records contained in the table.
;; Works with MDB Files MS Office 2007 and earlier
(defun c:AccessDatabase
( / daoObj dbObj rstObj fieldsObj
)
;; Load the DAO Library
:methods-prefix "daom-"
:properties-prefix "daop-"
:constants-prefix "daoc-"
)
)
)
;; Create a reference to the Database engine
;; Open the database
(setq dbObj
(daom
-opendatabase daoObj
"C:\\Datasets\\CP417-7\\data files\\AU2011.mdb"))
;; Open the table
(setq rstObj
(daom
-openrecordset dbObj
"tblEmployees" daoc
-dbOpenDynaset
)) ;;daoc-dbOpenDynaset daoc-dbOpenTable
;; Step to the first and then last record to get the number of records in the table
(daom-movefirst rstobj)
(daom-movelast rstobj 0)
(prompt (strcat "\n" (itoa (daop
-get
-recordcount rstObj
)) " records in table."))
;; Step back to the first record
(daom-movefirst rstobj)
;; Report each of the records that are in the table
(while (= (daop
-get
-eof rstObj
) :vlax-false) (setq fieldsObj
(daop
-get
-fields rstObj
))
;; Move to the next record
(daom-movenext rstobj)
)
;; Do a search for the employee with the first name Bob
(daom-movefirst rstobj)
(daom-findfirst rstobj "FirstName='Bob'")
;; If the record is found them return some information about the record
(if (= (daop
-get
-nomatch rstobj
) :vlax-true) (prompt "\nNo matching record") (setq fieldsObj
(daop
-get
-fields rstobj
))
;; Open the record for edit
(daom-edit rstobj)
;; Change the value of the RoomNumber field
(daop-put-value (daop-get-item fieldsObj "RoomNumber") "103A")
;; Inform of record change
(prompt "but is now in room 103A.\n")
;; Update the record
(daom-update rstobj 1 0)
)
)
;; Close the recordset and database
;; Release the DAO object
)
;; Works with MS Office 2007 and later
(defun c:AccessDatabaseADO
( / daoObj dbObj rstObj fieldsObj
)
;; Load the ADO library
:methods-prefix "adom-"
:properties-prefix "adop-"
:constants-prefix "adoc-"
)
)
)
;; Create a recordset and connection object
(setq strConnection
(strcat "Provider=Microsoft.ACE.OLEDB.12.0;" "Data Source=C:\\Datasets\\CP417-7\\data files\\AU2011.mdb;"
"Jet OLEDB:Database Password=;"))
;; Open the database
(adom
-open adoConnection strConnection
"" nil nil)
;; Open the table
(adom
-open adoRecordset
"tblEmployees" adoConnection adoc
-adOpenKeyset adoc
-adLockOptimistic adoc
-adCmdTable
)
;; Step to the first and then last record to get the number of records in the table
(adom-MoveFirst adoRecordset)
(adom-MoveLast adoRecordset)
(prompt (strcat "\n" (itoa (adop
-get
-RecordCount adoRecordset
)) " records in table."))
;; Step back to the first record
(adom-MoveFirst adoRecordset)
;; Report each of the records that are in the table
(while (= (adop
-get
-eof adoRecordset
) :vlax-false) (setq fieldsObj
(adop
-get
-fields adoRecordset
))
;; Move to the next record
(adom-movenext adoRecordset)
)
;; Do a search for the employee with the first name Bob
(adom-movefirst adoRecordset)
(adom-Find adoRecordset "FirstName = 'Bob'" 0 adoc-adSearchForward 1)
;; If the record is found them return some information about the record
(if (= (adop
-get
-eof adoRecordset
) :vlax-true) (prompt "\nNo matching record") (setq fieldsObj
(adop
-get
-fields adoRecordset
))
;; Change the value of the RoomNumber field
(adop-put-value (adop-get-item fieldsObj "RoomNumber") "103A")
;; Inform of record change
(prompt "but is now in room 103A.\n")
;; Update the record
(adom-update adoRecordset)
)
)
;; Close the recordset and database
(adom
-close adoRecordset
) (adom
-close adoConnection
)
)
