Delete records from Access Database via VB.NET 1

[rborel]

Please help,
I'm trying to create a little app to archive data out of an Access Db. I've created a DSN that I can use MSQuery to connect to the mdb with and select/delete but when I try to connect from inside the .NET app it doesn't delete.

Here's the code I've got. Plz excuse, I am very new to VB programming.

Private Sub sArchive()
dt = dtpAchDate.Text
Dim sql1 As Object
sql1 = &quot;delete from tblInvoice where InvDate < &quot; & dt
Dim td
Dim cn1 As ADODB.Connection
cn1 = New ADODB.Connection
cn1.Open(&quot;DSN=LCAdata&quot
cn1.Execute(sql1)
cn1.Close()
cn1 = Nothing
End Sub

I'm getting dt from a &quot;date/time picker&quot; and the sql1 = delete from tblInvoice where InvDate < 1/1/2000 in the watch window. It is the sql1 watch that I can copy/paste into MSQuery and run the delete fine.

I've tried changing the Dim sql1 as Object to String, there is no difference, although I believe it should be String shouldn't it? Once past this little bump, I have a few more questions like, how to list all the tables in an Access db and run the same query against each of them, like I mentioned, I'm trying to create an archiving app (that the creator of the Access db should have created in the first place) if I'm on the wrong path, please suggest, I am very much open to suggestions.

Thanks for any assistance anyone can provide. If this is a repeat thread again, I apologize, I did search and didn't find anything close enough to answer my problem.

Thanks again.

 

[CCLINT]

sql1 = &quot;delete from tblInvoice where InvDate < #&quot; & dt & &quot;#&quot;

The date has to be in US format MM/dd/yyyy, or, as yyyy-MM-dd.
So you can make sure this is passed correctly by using:


or
...where InvDate < #&quot; & Format$(dt,&quot;MM\/dd\/yyyy&quot & &quot;#&quot;

...where InvDate < #&quot; & Format$(dt,&quot;yyyy-MM-dd&quot & &quot;#&quot;

which will work with non-US date formats and date seperators.

You could also use the JET/ACCESS dbms specific Date convervion function DateValue:

...where InvDate < DateValue('&quot; & dt & &quot;')&quot;

However, I would not use this last solution...

 

[rborel]

CCLINT,

You ROCK. . . That did it. I didn't realize it was looking for the pound signs. I tried everything else. Anyway, thanks to you, I have it working.

Do you have any suggestions to the second question?

I have a few more questions like, how to list all the tables in an Access db and run the same query against each of them?

 

[CCLINT]

Use either the Connection's OpenSchema method, or ADOX.
I have a simple example on this posted in: thread709-501345