Writing to MS Access DB

Writing to MS Access DB

Post by jerry chap » Fri, 16 Sep 2005 21:30:34


I have written several programs to read from an MS Access DB, but now I
would like to write to one. Nothing complicated, I would just like to add a
line to an existing table or modify an entry in an existing line. Can
someone show an example of doing those things?
 
 
 

Writing to MS Access DB

Post by rony » Wed, 21 Sep 2005 07:07:11


------------------------- cut here -----------------------

conn=.oleobject~new("ADODB.Connection") -- create an ADO conncection object
conn~provider="Microsoft.Jet.OLEDB.4.0" -- tell it what driver to use

filename="test.mdb" -- use any existing MDB filename
mdbFullName=stream(filename, "C", "QUERY EXISTS") -- get full path

-- open a connection to the database
conn~open(mdbFullName) -- open connection
call dropTable conn -- make sure table is dropped

sql="create table myTable (memnr integer, name text (25) )"
say sql
conn~execute(sql)
say

do i=1 to 5 -- insert 10 records
sql="insert into myTable (memnr, name) values ("i ", 'name # "i"' )"
say i":" sql -- show sql statement
conn~execute(sql) -- execute the statement
end

say
call showTable conn
say
sql="delete from myTable where memnr=3" -- delete row with value "3" in "memnr"
say sql
conn~execute(sql)
call showTable conn
say

sql="update myTable set memnr=memnr*3 where memnr > 3" -- update some records
say sql
conn~execute(sql)
call showTable conn
say

conn~close


::routine showTable
use arg conn
rs=.oleObject~new("ADODB.Recordset") -- create an ADO recordset object

rs~CursorType = 1 -- .ole.const~adOpenKeyset
rs~LockType = 3 -- .ole.const~adLockOptimistic
sql="select * from myTable order by memnr"
rs~open(sql, conn) -- open the table
totRecs=rs~recordCount -- get recordCount
say "nr. of records:" totRecs
rs~moveFirst -- just make sure, it is pointing to the first record
do i=1 to totRecs while rs~eof=.false -- loop over record set
say i": memnr="pp(rs~fields["memnr"]~value) "name="pp(rs~fields["name"]~value)
rs~moveNext
end
rs~close

::routine dropTable
use arg conn
signal on any -- intercept any exception
conn~execute("drop table myTable")
say "dropped 'myTable'"
return
any:
say "could not drop 'myTable'"
say condition("C") condition("D")
return

::routine pp
return "[" || arg(1) || "]"

------------------------- cut here -----------------------

HTH,

---rony