Read/Write/Delete Tables from an Access DB

Keywords: 	 read write delete tables access

Question:

I've been attempting to play with OLE, reading / writing / and deleting records from an Access database.table.
Let me put it this way, if there wasn't bad luck then I would have no luck at all. I've been fighting with this OLE for about 4 days and have achieved very little using OLE.
I tried something like this:
AcApp = AcAppOpen.Application AcApp.Visible = @True AcApp.Opencurrentdatabase("h:\test.mdb") AcCmd = AcApp.DoCmd AcTable = AcCmd.OpenTable("Client Information", , acadd) ; Client information is my Table name ADODB = objectopen ("Adodb.recordset") AcAppOpen = ObjectOpen("Access.Application") addnew = adodb.addnew ("location", "New York") ; Location = fieldname and New York is the value.
As you can see I am struggling. Could someone post an example for me to follow on how to read / write / delete and update an Access database or point me to where there is a good example.

Also if any of you could point me to a good source other than MS online help, for learning / understanding OLE that would be great.

Answer:

Your example seems to be a mix of ADO and Access VBA.

If you have several types of databases you need to work with (Access, Oracle, SQL Server etc) then ADO is the way to go.

If you're just using MS Access then stick with the Access VBA route (it's a bit simpler).

Here's a short script that opens an access database, reads data from a text file and updates the dbase.

AccessApp = objectopen("Access.Application.8")
dbname = "d:\report stage\oracle eaprod.mdb"
AccessApp.opencurrentdatabase(dbname)
db = AccessApp.currentdb

ih = fileopen("c:\bin\eaprint.txt", "read")

while @true
        yield
        x = fileread(ih)
        if x == "*EOF*" then break
        region = itemextract(1, x, @tab)
        code = itemextract(2, x, @tab)
        sqlstr = strcat("select * from [OMCTop20Rpt] where [flx_cal_date1] is null and [location_id] in (", code, ")")
;       message(region, sqlstr)
        rs = db.openrecordset(sqlstr)
        rfield = rs.fields("flx_cal_date1")
        while rs.eof == @false
                yield
                rs.edit
                rfield.value = region
                rs.update
                rs.movenext
        endwhile
endwhile

fileclose(ih)

objectclose(rs)
objectclose(db)
objectclose(AccessApp)

message("Debug", "All Done")

Most of Access ops with recordsets deals with the recordset object:

rs.addnew = add a new record 
rs.edit = edit/change

Both must use rs.update to save your changes. (see above).

Deleting records can be done via the db object, and it's usually faster to it this way rather than a record at a time...

db.execute ("delete from [table] where [field] = 'xyz'")

Article ID:   W15249

File Created: 2002:09:05:13:50:50

Last Updated: 2002:09:05:13:50:50

Database Search

TechHome

Read/Write/Delete Tables from an Access DB

Question:

Answer: