Getting Data from an Access Database

[Anonymous]

Hi All,

I'm working on a new program for creating a part that I have to create pretty often. One of the things my program will do is search for an existing part based on input. I have come up with an access database that has 2 tables, 1 for Galvanized Parts and 1 for Weathering Parts. Is it possible to have LISP look at the database and see if a part matches the information required? If it is possible, how would I go about getting the information from Access into LISP? If it isn't possible, is there another way I could do this? Currently my tables have 11 columns in them, but as I write the program I may find I need other pieces of information that I haven't though of yet so it could end up with more columns.

[dgorsman]

Yes you can.  You may run into problems though, if you have 32-bit Office and 64-bit AutoCAD.

[voseo]

Try using ADOLisp by the Fleming Group...  This will allow you to connect to an 'mdb' file using lisp (ADOLisp_connecttoDB..) function by passing a connect string and the path to your Database (mdb), which will return a connection object, and then simply issue SQL Statements from your Lisp Routine passing it the connection object to query the database and return the values into your lisp function.  It is now possible to use this on a 64bit platform (as well as 32bit).  It's all explained on the Fleming Groups Web Site...

To Connect to a DB

(setq dbconn (adoconnect "c:\\temp\\mymdb.mbb"))

;fn = mdb locn

(defun adoconnect (fn)
 (setq processor_type (getenv "processor_architecture"))
 (if (= processor_type "AMD64")
  (setq ccname (ADOLISP_ConnectToDB (STRCAT "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" fn) "" ""))
  (setq ccname (ADOLISP_ConnectToDB (strcat "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" fn ";Persist Security Info=False") "" ""))
 )
)

To run a query

(setq argin "select empno from employees where name  = 'smith'")

(ADOLISP_DoSQL dbconn argin)

Works for me!! - Good Luck.

Pete

[Anonymous]

---

@dgorsman wrote:

Yes you can.  You may run into problems though, if you have 32-bit Office and 64-bit AutoCAD.

---

Hi dgorsman,

How would I go about getting the data? And is it possible to also write data back to the access database? Also, how can I determine if I have 32 or 64bit for Office and AutoCAD? I believe AutoCAD is 64bit but I'm not completely sure.

---

@voseo wrote:

Try using ADOLisp by the Fleming Group...  This will allow you to connect to an 'mdb' file using lisp (ADOLisp_connecttoDB..) function by passing a connect string and the path to your Database (mdb), which will return a connection object, and then simply issue SQL Statements from your Lisp Routine passing it the connection object to query the database and return the values into your lisp function.  It is now possible to use this on a 64bit platform (as well as 32bit).  It's all explained on the Fleming Groups Web Site...

To Connect to a DB

(setq dbconn (adoconnect "c:\\temp\\mymdb.mbb"))

;fn = mdb locn

(defun adoconnect (fn)
 (setq processor_type (getenv "processor_architecture"))
 (if (= processor_type "AMD64")
  (setq ccname (ADOLISP_ConnectToDB (STRCAT "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" fn) "" ""))
  (setq ccname (ADOLISP_ConnectToDB (strcat "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" fn ";Persist Security Info=False") "" ""))
 )
)

To run a query

(setq argin "select empno from employees where name  = 'smith'")

(ADOLISP_DoSQL dbconn argin)

Works for me!! - Good Luck.

Pete

---

I see voseo's post mentions a program by someone called the Fleming Group for connecting to a database, but I would rather not use an outside source's program as I've never heard of the Fleming Group so I don't know how reliable the program would be.

[hmsilva]

---

@Anonymous wrote:
I see voseo's post mentions a program by someone called the Fleming Group for connecting to a database, but I would rather not use an outside source's program as I've never heard of the Fleming Group so I don't know how reliable the program would be.

---

Jon Fleming's ADOLISP is not a compiled code, is a '*.lsp' file, you can open the file and inspect the library functions...

Henrique

[Anonymous]

---

@hmsilva wrote:

---

@Anonymous wrote:
I see voseo's post mentions a program by someone called the Fleming Group for connecting to a database, but I would rather not use an outside source's program as I've never heard of the Fleming Group so I don't know how reliable the program would be.

---

Jon Fleming's ADOLISP is not a compiled code, is a '*.lsp' file, you can open the file and inspect the library functions...

Henrique

---

Hi hmsilva,

Where would I find this code/program? I've never heard of Jon Fleming so I'm not sure where to go to get it.

[voseo]

typing ADOLISP FLEMING into Google, gives:

http://acad.fleming-group.com/Download/ADOLisp/ADOLisp.html

[hmsilva]

---

@Anonymous wrote:

---

@hmsilva wrote:

---

@Anonymous wrote:
I see voseo's post mentions a program by someone called the Fleming Group for connecting to a database, but I would rather not use an outside source's program as I've never heard of the Fleming Group so I don't know how reliable the program would be.

---

Jon Fleming's ADOLISP is not a compiled code, is a '*.lsp' file, you can open the file and inspect the library functions...

Henrique

---

Hi hmsilva,

Where would I find this code/program? I've never heard of Jon Fleming so I'm not sure where to go to get it.

---

Hi AR12!

'I've never heard of Jon Fleming'

do a search in 'Search this board' for  Jon Fleming....

Henrique

[dgorsman]

---

@Anonymous wrote:

---

@dgorsman wrote:

Yes you can.  You may run into problems though, if you have 32-bit Office and 64-bit AutoCAD.

---

Hi dgorsman,

How would I go about getting the data? And is it possible to also write data back to the access database? Also, how can I determine if I have 32 or 64bit for Office and AutoCAD? I believe AutoCAD is 64bit but I'm not completely sure.

---

Yes, it is possible to both read and write to an Access database, as well as other types.  Have a look at ADO.lsp as mentioned, but first I think you need to step back a bit and bone up on the basics first.  Its one of those "If you have to ask..." things - you need to have a grasp of database operations before you start trying to use them programmatically.

[Anonymous]

Thanks everyone. I'm in the process of looking at the ADOLISP program. I'm thinking this program I'm attempting to write may get pushed to a back burner until I've had time to understand how to use SQL as I don't think I've ever worked with SQL before.

[voseo]

Once you've made the connection I'd be happy to guide you through some basic SQL to get you started. First thing to master is creating the connection object. Look at the adoconnect function I posted to see the basic method. You may also find that searching and installing the ODBC MS Access drivers onto your PC may solve any 32/64bit issues. Once that's done it's pretty much plain sailing. It's well worth the effort to get comfortable with ADOLisp as it can enable some great functionality.


Peter

[voseo]

I'd be happy to provide a few basic SQL Statements to get you Started out...   The first priority would be to establish the connection Object so that your AutoCAD Session can talk to the MS Access Database (use the 'adoconnect' routine in my earlier post as a start point - if it returns an 'Object' you're up and running.). Once you have that in place it's pretty much plain sailing, just read and write using the ADO functions (passing an SQL Query string to the Connection Object)_.  The issues with 32 and 64bit MS Access can generally be overcome by installing the MS Access ODBC Engine Driver updates onto your PC (can be easily found on the web) and this may be worth running before you try to establish the connection in your lisp.