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.
Code:
(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))
)