VB delete Access rows from table on condition

I want to be able to delete rows from a an access query where a condition (date) is met within the table?

im still stuck with this one guys any advise?

anyone know if this can even be done?

I don't understand what you are trying to do. Are you trying to do this within Access or Excel?

Access forumn link
http://www.access-programmers.co.uk/forums/

using excel vb code i would like to delete records from an access table based on a criteria

To do what you asked do so:
1) open excel and then Vb editor (alt + F11)
2) from the menu, choose tools, references and, from the list, choose Microsoft DAO 3.6 object librery
if you don't have 3.6 you can use also 3.51 or another

3) paste this code:
Sub DeleteRecords()
Dim db As Database
Dim dbName As String
Dim query As String

dbName = "c:\<path>\<db name>.mdb"

'connect to access database
Set db = dbengine.Workspaces(0).OpenDatabase(dbName)
query = "delete from <rable name> where <field name> = 'criteria'"

if your field is numeric don't use quote in criteria:
query = "delete from <rable name> where <field name> = criteria"

'deleting
db.Execute (query)
db.Close
Set db = Nothing

End Sub

Regards.
Antonio

try this but i get the following debug error

run time error 3131
syntax error in FROM clause

my database is named db1, the test table is called table and it contains three
columns table, names, number

from

Sub DeleteRecords()
Dim db As Database
Dim dbName As String
Dim query As String

dbName = "C:\Documents and Settings\Home\My Documents\db1.mdb"

'connect to access database
Set db = dbengine.Workspaces(0).OpenDatabase(dbName)
query = "delete from table where names = 'nick'"

'if your field is numeric don't use quote in criteria:
'query = "delete from <rable name> where <field name> = criteria"

'deleting
db.Execute (query)
db.Close
Set db = Nothing

End Sub

Excuse me but I wrongly wrote:
query = "delete from <rable name> where <field name> = 'criteria'"

when the right was:
query = "delete from <table name> where <field name> = 'criteria'"

Here you have to change <table name> with your table name.

For instance, if you need to delete rows from the table named 'Clients' where field named 'Name' is equal to 'Philip'

you need to write:
query = "delete from Clients where Name = 'Philip'"

if the table name or the field name have blanks in the name use []:
query = "delete from [Top Client] where [Main Name] = 'Philip'"

Regards,
Antonio

i got ths working but how can i get this working if i want to rffer to a date in a variable

works with text
query = "delete from [Top Client] where [Main Name] = 'Philip'"

want it to work like this
mydate = 25/10/2005
query = "delete from [Top Client] where [Main Name] = mydate"

Hi,

Use the following:
query = "delete from [Top Client] where [Main Name] = #25/10/2005#"

thanks for the reply but it has to be from a variable as i pulling the date from a cell you see

Hi,
Sorry, should've read your question closely before posting.

You can use
query = "delete from [Top Client] where [Main Name] = #" & mydate &"#"
or
query = "delete from [Top Client] where [Main Name] = #" & Sheets(1).[a1] & "#"
asssuming [a1] holds the value you want to use

For my experience, it's better to use format with ms access dates

For instance:
query = "delete from [Top Client] where format([birth day],""yyyymmdd"") = " & format(Sheets(1).range("a1"),"yyyymmdd")

Regards,
Antonio

i got this working but it only removes the first records how can i get this to remove all records in the record set where the condition is equal to the date in question?