;;;
;;; Hierfür benötigt man die "Microsoft Access Database Engine 2010 Redistributable"
;;; Download unter
;;;    http://www.microsoft.com/downloads/de-de/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d
;;;


(defun f:load-AdoDB-OBS (/ cmdCurVer cmdClsID cmdInprocServer32)
  (if (not AdoDB-c-adCmdFile) ; prüfen auf eine ADODB-Constante
    (progn
      (setq cmdCurVer         (vl-registry-read "HKEY_CLASSES_ROOT\\ADODB.Command\\CurVer")
            cmdClsID          (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" cmdCurVer "\\CLSID"))
            cmdInprocServer32 (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\CLSID\\" cmdClsID "\\InprocServer32")))
      (VLAX-IMPORT-TYPE-LIBRARY
        :TLB-FILENAME cmdInprocServer32
        :METHODS-PREFIX "AdoDB-m-"
        :PROPERTIES-PREFIX "AdoDB-p-"
        :CONSTANTS-PREFIX "AdoDB-c-"))))

(defun f:get-Produkt-from-MDB(artikelNr /
                              verzeichnis ODBCPath ConStr ConnectionString cnAdo cmdAdo AdoDBProps retVal rsProdukt)
  (f:load-AdoDB-OBS)
  
  (setq artikelNr         (if (numberp artikelNr)
                            (itoa (fix artikelNr))
                            (if (eq 'str (type artikelNr))
                              artikelNr
                              "101000"))
        verzeichnis       "M:\\CAD\\saint-gobain\\Library\\HES\\"
        ODBCPath          (strcat verzeichnis "HES.MDB")
        ConStr            (strcat "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" ODBCPath ";")
        ConnectionString  (strcat ConStr "Persist Security Info=False")
        cnAdo             (vlax-create-object "ADODB.Connection")
        cmdAdo            (vlax-create-object "ADODB.Command"))

  (if (= AdoDB-c-adStateOpen
         (AdoDB-p-get-State cnAdo))
    (AdoDB-m-Close cnAdo))

  (AdoDB-p-put-provider cnAdo "Microsoft.ACE.OLEDB.12.0")
  (setq adoDBProps(AdoDB-p-get-Properties cnAdo))
  (AdoDb-p-put-value
    (AdoDB-p-get-item adoDBProps "Data Source")
    ODBCPath)
  (AdoDB-p-put-cursorLocation cnAdo AdoDB-c-adUseClient)
  (AdoDB-p-put-mode           cnAdo AdoDB-c-adModeShareDenyNone)
  (AdoDB-m-open               cnado ConStr "Admin" "" -1)

  (adodb-p-put-ActiveConnection cmdAdo cnAdo)

  (adodb-p-put-CommandText cmdAdo (strcat "SELECT * FROM Produkte WHERE DatNam1='" artikelNr "'")) ; SQL-Commando
  (setq rsProdukt (adodb-m-execute cmdAdo nil nil -1))                                             ; SQL-Commando ausführen
  (if (not (zerop (adodb-p-get-RecordCount rsProdukt)))                                            ; Überprüfen oder der RecordSet leer ist
    (setq retVal ((lambda (/ lst fields fieldCnt column rows)
                    (adodb-m-moveFirst rsProdukt)                                                  ; Den Zeilenselector auf die  ersten Eintrag setzen
                    (setq rows    (vlax-variant-value (adodb-m-getRows rsProdukt nil nil nil))     ; Das Mehrdimensionale Variant-Array auslesen
                          fields  (adodb-p-get-fields rsProdukt)                                   ; Die Spalten-Definitionen
                          fieldCnt(vlax-safearray-get-u-bound rows 1)                              ; Anzahl der Spalten
                          column  -1)
                    (repeat fieldCnt
                      (setq lst(cons (cons (adodb-p-get-name (adodb-p-get-item fields (setq column(1+ column)))) ; Der Name der Spalte
                                           (vlax-variant-value (vlax-safearray-get-element rows column 0)))      ; Der Wert der Spalte im RecordSet
                                     lst)))
                    (vlax-release-object fields)
                    (reverse lst)))))
  (if (= AdoDB-c-adStateOpen
         (AdoDB-p-get-State cnAdo))
    (AdoDB-m-Close cnAdo)) ; Datenbank schließen

  (vlax-release-object rsProdukt)
  (vlax-release-object cmdAdo)
  (vlax-release-object cnAdo)

  retval)
;|
Aufruf  (f:get-Produkt-from-MDB "304451")
Rückgabe => (("ID" . 617)
             ("IDPGruppe" . 48)
             ("Name" . "DN 300")
             ("DatNam1" . "304451")
             ("DatNam2" . "304451")
             ("BildNr" . "34")
             ("HideOptions" . "HO")
             ("Typ" . "STUECK")
             ("KText" . "DN 300")
             ("LText" . "DN 300 \r\nArt.Nr. : 304451\r\n"))
|;