Topic: Microsoft Access Activex Controls

[masoud_123]

Is it possible opening, reading, writing to ms access db's using vlisp with the activex controllers?
There are some examples on ms excel but not for ms access!

Please help!

[Lee Mac]

Not sure if this thread will help?

http://www.theswamp.org/index.php?topic=38177

[irneb]

You didn't specify 2010, though the "more generalized" ADOLisp can handle anything which has an ADO/ODBC driver on your system. The sample included in the download is actually using an Access MDB file.

[masoud_123]

I need it for ms access 2010 and also other versions. is there any restriction for 64 or 32 bit?

I am totally confused by " Lee Mac" and ADOLisp examples!

What is ADO/ODBC driver?

I need to open an access .mdb file, and then save it as a .txt file. Is it possible?

[irneb]

ADO (Active Data Objects) is a library designed by Microsoft to connect to any database (at least all those which have a connector - driver).
 
 ODBC is an older, but more available library doing the same thing. Both come standard in windows, and usually the Access driver is already installed.
 
 E.g. open Control Panel --> Administrative Tools --> Data Sources. This opens the various preset Data Sources for ODBC, you could either generate one here, or use a connection string in ADOLisp.

Note, if all you want is to extract the data from an MDB file then you could do so using Excel. You basically link a spreadsheet to the data inside the MDB file. From there you should be able to saveas to CSV (or whatever you like). See my explanations in posts #2, #4 & #7 here: http://www.theswamp.org/index.php?topic=39667.0
Just note that Excel has some restrictive limits on the number of columns / rows - which is much less restrictive in Access.

As for 32/64 bit, that only applies to the MS OLE DB connections: http://www.connectionstrings.com/articles/show/using-jet-in-64-bit-environments
ADO/ODBC shouldn't give you trouble.

BTW, it would be "impossible" to save everything inside a MDB file to one TXT file. A MDB can contain many tables (like an Excel Spreadsheet can contain many tabs). It can also contain data-entry forms, reports & queries. And more: it can even have connections to outside data sources - strictly not part of the MDB file itself.

[masoud_123]

Thank you so much Irneb.

I am trying to find out ADO/ODBC.

Again thank you for your help.

[BlackBox]

I am totally confused by " Lee Mac" ...

*Off to MQotW thread, with this little gem* 

[fixo]

Thank you so much Irneb.

I am trying to find out ADO/ODBC.

Again thank you for your help.

Try this code, tested on big size table, A2010

Code: [Select]

; local defun
; get data from Access table
(defun ARD (/ adocn adorst dbname rowdata sqlstrread tabledata tblname x)
  (vl-load-com)

  (setq
    dbname
   (getfiled "Select an Access Database File"
     (getvar "dwgprefix")
     "mdb"
     4
   )

    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 tblname (getstring T "\nEnter name of the data table :"))
  (setq sqlstrread (strcat
" SELECT * FROM " tblname ";")
  )
  (vlax-invoke-method adorst "Open" sqlstrread adocn -1 3 1)
  (setq rowdata (vlax-invoke-method adorst "GetRows" T))
  (setq tabledata
(apply
   'mapcar
   (cons
     'list
     (mapcar
       (function
(lambda (x)
   (mapcar (function (lambda (y)
       (vlax-variant-value y)
     )
   )
   x
   )
)
       )
       (vlax-safearray->list (vlax-variant-value rowdata))
     )
   )
)
  )
  (vlax-invoke-method adorst "Close")
  (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)
  )
  (gc)
  tabledata
)

(defun lst2str (lst sep)
    (if (cadr lst)
      (strcat (vl-princ-to-string (cond ((car lst))("---")))
        sep
        (lst2str (cdr lst) sep)
      )
      (vl-princ-to-string (cond ((car lst))("---")))
    )
)
; main part :
(defun C:demo(/ data fn fname  tblname)
 
  (setq data (ard))
  (setq tblname "MyText");<-- change text file name here
  (setq fname (strcat (getvar "dwgprefix")   tblname ".txt"))
  (setq fn (open fname "w"))
 
  (foreach lst data
  (write-line (lst2str lst "\t") fn)
  )
 
  (close fn)
  (princ)
  )
(princ "\nStart command with DEMO ...")
(princ)
(or (vl-load-com)(princ))
~'J'~

[dgorsman]

+1 for ADOLisp.  Easy to use and understand, just needs a minor update for handling the 64-bit environment.

[Kerry]

+1 for ADOLisp.  Easy to use and understand, just needs a minor update for handling the 64-bit environment.

dgorsman ,
What changes did you make to ADOLisp ??  ... just for those following along

[dgorsman]

Ah, right   It uses Microsoft.Jet.OLEDB.4.0 as a provider but that needs to be replaced with Microsoft.ACE.OLEDB.12.0.  Good reminder - we're on the path to upgrading.  We don't use it that much but its one of the more important things to check.

I did a massive hack'n'slash on the lot (it's noted the code is 'functional' rather than 'efficient') to delay-load the TLB stuff until its called for, make use of some in-house list- and error-handling functions, and some general re-indenting so I could follow it easier.

[Faster]

Please refer to the web site:

http://acad.fleming-group.com/index.html

[irneb]

Ah, right   It uses Microsoft.Jet.OLEDB.4.0 as a provider but that needs to be replaced with Microsoft.ACE.OLEDB.12.0.

You mean you need to change your connection string in order to use the newer ADO drivers ... yep exactly as per the link in my previous post. The Jet driver comes from the 90's, no wonder it doesn't work on new Windows. But then , as you'll note on the ADOLisp's MDB file - that new driver will error out because "It can't connect to old version files"  ... typical M$ BS!!!!!

BTW, this is one of the major reasons I prefer using the older but much more compatible ODBC method. Not to mention ODBC is not even just an MS thing (that's probably why it always just works) - I can even use it on Linux! And the old connection string still works on Win7-64bit. No need for changing as in MS's constantly changing mindset (OLEDB/ADO/DAO/NET/etc etc etc).

Here's the ODBC connection string to ADOLisp's sample MDB file:

Code: [Select]

Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DBQ=J:\T\ADOLisp252\ADOLISP_test.mdb;DefaultDir=J:\T\ADOLisp252;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;FILEDSN=J:\T\ADOLisp252\ADOLispTest.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"Looks long, but I didn't need to figure it out myself. Just open Excel and use it's data linking wizard to connect to that MDB file then copy-n-paste the connection string it generates.

Again as per my previous post, see full description of how-to in this thread: http://www.theswamp.org/index.php?topic=39667.0

[irneb]

And if the text tutorial doesn't give you enough info, try this instead.