How do I delete rows from an access database using a script?

Question

I am trying to programmatically remove rows from a Microsoft Access Database using a script (such as vbscript or whs).

It looks like there are two or more engines that can be used to connect to an mdb file which are the ADO extension Jro.JetEngine or DAO.Database DBEngine.

In addition to this, there is a column in the table called CreatedDate which contains the date that the entry was created.

I plan to use this to remove entries that are older than N days old.

How would I achieve something like this?

I don't really think you have to use JRO -- just use the standard OLEDB/ADO commands for executing a SQL statement. I'd vote for DAO, since it's Jet's native interface, but for something as simple as this, there's no real advantage one way or another. If you're using ADO, it's the natural choice. — David-W-Fenton
– David-W-Fenton, Commented Apr 2, 2011 at 2:03

renick · Accepted Answer · 2011-03-31 18:49:02Z

1

You need something like this script.

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & yourDatabase & ";"
sql = "delete from yourTable where CreateDate < " & yourDateString
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")
cn.open connectionString
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.execute
cn.Close

The specific connection string for your MS Access version can be had at connectionstrings.com

answered Mar 31, 2011 at 18:49

renick

3,9112 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I delete rows from an access database using a script?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related