« previous next »
Pages: [1] 2  All |   Go Down

Author Topic: Lisp to get items from MS Access 2010 (64bit)  (Read 13593 times)

0 Members and 1 Guest are viewing this topic.

Rabbit

  • Guest
Lisp to get items from MS Access 2010 (64bit)
« on: September 06, 2012, 04:29:17 PM »
After doing some searching and getting myself confused, I don't believe ADOLisp is going to work for this.  Anybody got anything that will work, or know where to look?  Once I'm able to get the Access information into lisp, I can do everything else I need to from there.

Thanks,
Rabbit
Logged

BlackBox

  • King Gator
  • Posts: 3770
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #1 on: September 06, 2012, 05:29:50 PM »
FWIW -

Code - Auto/Visual Lisp: [Select]
  2.  
  3. ;; <snip>
  4.  
  5. (if (setq oAccessApp (vlax-get-or-create-object "Access.Application"))
  6.   (progn
  7.    
  8.     ;; <- Your code
  9.  
  10.     (setq oAccessApp
  11.            (vl-catch-all-apply
  12.              'vlax-release-object
  13.              (list oAccessApp)
  14.            )
  15.     )
  16.   )
  17.   (prompt
  18.     "\n** Unable to create \"Access.Application\" Object ** "
  19.   )
  20. )
  21.  
  22. ;; <snip>
  23.  

** Edit - Be sure to catch *error* as well
Logged
"How we think determines what we do, and what we do determines what we get."

dgorsman

  • Water Moccasin
  • Posts: 2437
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #2 on: September 06, 2012, 06:36:41 PM »
ADOLisp should work, but you need to change the connection type to use ACE rather than JET.  A quick search here should reveal the appropriate changes.
Logged
If you are going to fly by the seat of your pants, expect friction burns.

try {GreatPower;}
   catch (notResponsible)
      {NextTime(PlanAhead);}
   finally
      {MasterBasics;}

HYPERPICS

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #3 on: September 06, 2012, 07:20:09 PM »
Here is what I have done in the past:

Code - Auto/Visual Lisp: [Select]
  1. ;; Works with MDB Files MS Office 2007 and earlier
  2.   ;; Load the DAO Library
  3.   (if (= daoLibImport nil)
  4.     (progn
  5.       (vlax-import-type-library :tlb-filename (strcat strProgramFiles "\\Common Files\\Microsoft Shared\\DAO\\dao360.dll")
  6.                                 :methods-prefix "daom-"
  7.                                 :properties-prefix "daop-"
  8.                                 :constants-prefix "daoc-"
  9.       )
  10.       (setq daoLibImport T)
  11.     )
  12.   )
  13.  
  14.   ;; Create a reference to the Database engine
  15.   (setq daoObj (vlax-create-object "DAO.DBEngine.36"))
  16.  
  17.   ;; Open the database
  18.   (setq dbObj (daom-opendatabase daoObj "C:\\Datasets\\mydata.mdb"))
  19.  
  20.  
  21. ;; Works with MS Office 2007 and later
  22.   ;; Load the ADO library
  23.   (if (= adoLibImport nil)
  24.     (progn
  25.       (vlax-import-type-library :tlb-filename (strcat (ExpEnvStr "%PROGRAMFILES%") "\\Common Files\\System\\ado\\msado15.dll")
  26.                                 :methods-prefix "adom-"
  27.                                 :properties-prefix "adop-"
  28.                                 :constants-prefix "adoc-"
  29.       )
  30.       (setq adoLibImport T)
  31.     )
  32.   )
  33.  
  34.   ;; Create a recordset and connection object
  35.   (setq adoRecordset (vlax-create-object "ADODB.Recordset"))
  36.   (setq adoConnection (vlax-create-object "ADODB.Connection"))
  37.  
  38.   (setq strConnection (strcat "Provider=Microsoft.ACE.OLEDB.12.0;"
  39.                               "Data Source=C:\\Datasets\\mydata.mdb;"
  40.                               "Jet OLEDB:Database Password=;"))
  41.  
  42.   ;; Open the database
  43.   (adom-open adoConnection strConnection "" nil nil)
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #4 on: September 07, 2012, 11:00:36 AM »
HAHAHA!  To even learn how ADOLisp is used, the example .MDB file is an older version and my Access 2010 won't open it.  It won't even let me link it in.  I've gone through dozens of web pages about how to open it and none have given me any answers.  Looks like now I'm going to have to wing it and hope for the best.

LeeA,
Are you using the code you posted instead of (setq ConnectString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\CAD\\ADOLISP_test.mdb;Persist Security Info=False")
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #5 on: September 07, 2012, 11:32:37 AM »
Okay, I've followed the instructions here:  http://www.theswamp.org/index.php?topic=39667.0  Using Excel te get the connection string then using (getstring t "Paste: ") to get the converted string for lisp.

I've created my connection string:  "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\\FHU_CAD Operations\\FRANKE_CAD\\Schedules\\2011 MCD\\MASTER.mdb;Mode=Share Deny Write;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"

When I use (ADOLISP_ConnectToDB ConnectString "admin" "")  I get the error "bad argument type: string nil

My ConnectString variable isn't getting set.
Logged

owenwengerd

  • Bull Frog
  • Posts: 451
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #6 on: September 07, 2012, 12:18:56 PM »
Quote from: Rabbit on September 07, 2012, 11:32:37 AM
My ConnectString variable isn't getting set.

You haven't shown how you're setting it.
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #7 on: September 07, 2012, 12:26:14 PM »
Straight from the ADOLisp:

Code: [Select]
(if (not ADOLISP_ConnectToDB)
  (load "ADOLISP_Library.lsp")
)

(defun C:Example (/ ConnectionObject Result ConnectString SQLStatement
                  TablesList ColumnsList
                 )

  ;; Connecting to the database ...
  (setq ConnectString
         "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\\FHU_CAD Operations\\FRANKE_CAD\\Schedules\\2011 MCD\\MASTER.mdb;Mode=Share Deny Write;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
  )
  ;; An alternative connect string
  (setq ConnectString "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\\FHU_CAD Operations\\FRANKE_CAD\\Schedules\\2011 MCD\\MASTER.mdb;Mode=Share Deny Write;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False")
  (prompt (strcat "\n\nConnecting to the database using \n\""
                  ConnectString
                  "\""
          )
  )
  (if (not (setq ConnectionObject
                  (ADOLISP_ConnectToDB ConnectString "admin" "")
           )
      )
    (progn
      (prompt "\nConnection failed!")
      (ADOLISP_ErrorPrinter)
    )
    (prompt "\nResult: succeeded!")
  )
Logged

owenwengerd

  • Bull Frog
  • Posts: 451
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #8 on: September 07, 2012, 12:38:35 PM »
What leads you to conclude that it's not getting set? What is displayed in the text winodow before the error occurs?
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #9 on: September 07, 2012, 02:32:02 PM »
This is what it looks like when I do an INSPECTon the setq.  (see atchment) When I do an INSPECT on the variable ConnectString, it comes up as nil.  I can't figure out why it's coming up nil.  It's just a string after all.  Do you think there is a limit on how long a string can be?  That string is 700 caracters long.
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #10 on: September 07, 2012, 02:37:21 PM »
Just did some testing.  If I end the string at the third line, then it will give me a return.  If I end the string at the beginning of the fourth line, then it returns nothing.  It craps out after 453 caracters.

This will return something:
(setq ConnectString
         "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\\FHU_CAD Operations\\FRANKE_CAD\\Schedules\\2011 MCD\\MASTER.mdb;Mode=Share Deny Write
         ;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0
         ;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False")

Anything more won't.
« Last Edit: September 07, 2012, 02:55:21 PM by Rabbit »
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #11 on: September 07, 2012, 03:15:56 PM »
Wow.  Just found out that my Access 2010 is a 32 bit version. from what I'm reading, that may be a big problem also.
Logged

owenwengerd

  • Bull Frog
  • Posts: 451
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #12 on: September 07, 2012, 03:16:38 PM »
You can take most or all of that extended property junk off.
Logged

owenwengerd

  • Bull Frog
  • Posts: 451
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #13 on: September 07, 2012, 03:18:14 PM »
It's not your Access that matters, you just need to make sure your connecting to a driver of the same architecture as the AutoCAD process.

EDIT: Actually, come to think of it, you may have trouble installing 64 bit drivers on a system that has Access 32 bit installed. Not that they can't be installed, but Microsoft's installer craps out.
Logged

Rabbit

  • Guest
Re: Lisp to get items from MS Access 2010 (64bit)
« Reply #14 on: September 07, 2012, 05:03:47 PM »
After digging I found this little snippet:

Yes, you can use 32-bit and 64-bit ODBC drivers on the same machine. All you have to do is use the correct ODBC Data Source administrator.

For 64-bit (on a 64-bit machine) :: Start | Control Panel | Administrative Tools | Data Sources (ODBC)

For 32-bit (on a 64-bit machine) :: C:\Windows\SysWOW64\odbcad32.exe

So, from what I gather, I would need to invoke the C:\Windows\SysWOW64\odbcad32.exe driver somehow.  This is where I get lost.
Logged
Pages: [1] 2  All |   Go Up
« previous next »