Retriving data from a Microsoft Access database using OLE automation

Chris Kelleher

Administrator
Staff member
<BLOCKQUOTE><font size="1" face="Arial, Verdana">code:</font><HR><pre>
/***********************************************************************
*********
access.p - Connect to an Access Database using OLE automation

Uses the Data Access Objects model from Microsoft. This is documented
in
the help file DAO35.hlp

This code is provided AS IS. The author will not be held responsible
for any
damage caused.

Copyright 1998
Clinton Hastings
Golden Circle Limited
chastings@gcl.com.au
************************************************************************
********/

def var lh-access as com-handle no-undo. /* The Access Application */
def var lh-dbs as com-handle no-undo. /* The Database Object */
def var lh-table as com-handle no-undo. /* A table/recordset */

def var lc-dbname as char no-undo init "c:\tmp\northwind.mdb".
def var lc-tablename as char no-undo.
def var li-cnt as int no-undo.

def var lc-custcode as char no-undo format "x(7)".
def var lc-company as char no-undo format "x(40)".
def var lc-contact as char no-undo format "x(30)".

/* Create instance of Access Application */
create "Access.Application.8" lh-access.

/* Hide Access */
lh-access:visible = No.

/* We are going to use the DBEngine Object of the Access Application */
/* Connect to database in Shared Mode */
lh-dbs = lh-access:dbengine
redface.gif
pendatabase(lc-dbname).

/* Get our table from the Database - open as dynaset type recordset */
/* First parameter can be either a table name or an SQL statement */
lh-table = lh-dbs
redface.gif
penrecordset("Customers",2,,).

/***********************************************************************
*********
Recordset objects have the following methods:
movefirst
movelast
movenext
moveprevious
findfirst
findlast
findnext
findprevious
addnew
edit
update
.... etc
************************************************************************
********/

/***********************************************************************
*********
Reading records - Loop through first 10 records in the table
Each field is an entry in the recordset's fields collection.
For some reason, this is very slow.
************************************************************************
********/
do while not lh-table:eof() and li-cnt < 10:
assign
lc-custcode = lh-table:fields("CustomerID"):Value
lc-company = lh-table:fields("CompanyName"):Value
lc-contact = lh-table:fields("ContactName"):Value.

display
lc-custcode
lc-company
lc-contact
with down width 90.

down.

lh-table:movenext().
li-cnt = li-cnt + 1.
end.

/***********************************************************************
*********
Add a new record
1. Add the record
2. Set the field values
3. Write the changes back
************************************************************************
********/
lh-table:addnew.
assign
lh-table:fields("CustomerID"):Value = "TMP"
lh-table:fields("CompanyName"):Value = "Test Company"
lh-table:fields("ContactName"):Value = "Fred Smith".
lh-table:update(,).

/***********************************************************************
*********
Find a record.
The findfirst/last/next/previous methods all work on dynaset type
recordsets
only.
They each take a string parameter, which is an SQL where clause.
The nomatch propertie can be tested to see if find was successful.
************************************************************************
********/
lh-table:findfirst("CustomerID = 'TMP'").
if lh-table:nomatch
then do:
bell.
message "Customer not found" view-as alert-box error.
end.

/***********************************************************************
*********
Update a record.
1. Lock the record (using the Edit method)
2. Change the values of the fields
3. Write the changes back (using the Update method)
************************************************************************
********/
else do:
lh-table:edit.
lh-table:fields("ContactName"):Value = "Fred Flinstone".
lh-table:update(,).


/***********************************************************************
*******
Finally, delete the record using the delete method.

************************************************************************
******/
lh-table:delete.
end.


/* Close Access */
lh-access:quit(2).

/* Release COM Objects */
release object lh-table.
release object lh-dbs.
release object lh-access.
[/code]
 
Top