MS Access and Object data

[domagoj.culinovic]

Any idea how to import table structure from MS Access or MS SQL, and use this structure for Object data in Map, or how to import directly table definition to create the same table structure for Object data.

Thanks

[MHultgren]

domagoj,

Have you looked at the ATP course from last month on using Access and Map? You may be able to find it in the archives for last month still. This should help you tremendously. You can get there by clicking on the Education link in the header. Then click on ATP and browse the archives.

[mohobrien]

I use John Fleming's ADO_Library.lsp as a starting point and go from there. It is freely available at http://www.fleming-group.com This is a straight plagerism from there to give you a list of all tables and their fields as a dotted list.


(defun get_tblfld (sourcedb /)
(vl-load-com)
(load (findfile "ADOLISP_Library.lsp"))
(setq ConnectString
(strcat "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
sourcedb
";Persist Security Info=False"
)
)
(if (not (setq ConnectionObject
(ADOLISP_ConnectToDB ConnectString "admin" "")
)
)
(progn (prompt "\nConnection failed!")
(ADOLISP_ErrorPrinter)
)
)
(princ (ADOLISP_GetFieldsandTypes ConnectionObject))
;; Disconnect
(prompt "\n\nDisconnecting from the database\n")
(ADOLISP_DisconnectFromDB ConnectionObject)
;; Although the following is unnecessary in this case (because
;; ConnectionObject is a local variable), it's never a _bad_
;; idea to NIL-out the connection object.
(setq ConnectionObject nil)
)


;;; A function to obtain the names and data type of the fields
;;; in the tables in a database.
;;; Argument: ConnectionObject An ADO Connection Object
;; Return value:
;;; A list of two lists.
;;; The first list contains the field names.
;;; The second list contains the data type.

(defun ADOLISP_GetFieldsandTypes (ConnectionObject
/ TempObject
TempList FieldsList
TypesList FieldNumber
FieldCount
)
(setq ADOLISP_ErrorList nil
ADOLISP_LastSQLStatement nil
)
(setq RecordSetObject (vlax-create-object "ADODB.RecordSet"))
;; If we fail getting a recordset of the tables and views
;; ...
(if (vl-catch-all-error-p
(setq RecordSetObject
(vl-catch-all-apply
'vlax-invoke-method
(list
ConnectionObject
"OpenSchema"
ADOConstant-adSchemaColumns
)
)
)
)
;; Save the error information
(setq ADOLISP_ErrorList
(ADOLISP_ErrorProcessor RecordSetObject ConnectionObject)
)
(progn
;; Got the recordset!
;; We're about to get tricky, hang on! Convert the
;; recordset object to a LISP list ...
(setq
TempList
;; Uses Douglas Wilson's elegant
;; list-transposing code from
;; http://xarch.tu-graz.ac.at/autocad/lisp/
;; to create the list of rows, because
;; GetRows returns items in column order
(apply
'mapcar
(cons
'list
;; Set up to convert a list of lists
;; of variants to a list of lists of
;; items that AutoLISP understands
(mapcar
'(lambda (InputList)
(mapcar '(lambda (Item)
(ADOLISP_variant-value Item)
)
InputList
)
)
;; Get the rows, converting them from
;; a variant to a safearray to a list
(vlax-safearray->list
(vlax-variant-value
(vlax-invoke-method
RecordSetObject
"GetRows"
ADOConstant-adGetRowsRest
)
)
)
)
)
)
)
;; end setq
;; Now filter out the system tables and
;; sort the tables and fields into the
;; correct lists
(while (> (length TempList) 0)
(setq TName (nth 2 (car TempList)))
(foreach Item TempList
(if (= (nth 2 Item) TName)
(setq FieldsList (cons (vl-list* TName (nth 3 Item)) FieldsList)
TempList (cdr TempList)
)
;;end setq
)
;; end if
)
;; end foreach
)
;; end while
)
;; end progn
)
;; end if

;; Close the recordset
(vlax-invoke-method RecordSetObject "Close")
(vlax-release-object RecordSetObject)
;; maybe also take out the system tables with vl-remove-if.
(setq FieldsList (reverse FieldsList))
)