SQL Select / Delete (1 Viewer)

[NickBridgens]

I am trying to get my head round DoCmd.RunSql. I have a bit of code...

strSQL = "DELETE * FROM Cases WHERE [DateOut] >= #" & DateForDeletion & "#;"

DoCmd.RunSQL strSQL

They work.

Change DELETE to SELECT and it won't ("A RunSQL action requires an argument consisting of an SQL statement").

I am also trying to run a query form which collects filters from various fields if they are filled in to give a big SQL filter. Same problems.

Any clues where I am going wrong?

Cheers

Nick Bridgens

 

[Jacob Mathai]

It happened to me.
What I did :

strsql = "Select * into temp1 from tblxxx where ........."

docmd.opentable "temp1"

so, I created a temporary table and then displayed it. It worked for me.

 

[chrismcbride]

I believe that RunSql is design to run only action queries - Delete, MakeTable, Append...
Usually you use a select to populate a recordset and then take some action on the recordset...
Dim strSql as String
Dim rst as Recordset
Dim dbs as Database

Set dbs = CurrentDB
strSql = "SELECT * FROM tblProduct WHERE sup_no = " & Me.supplier_number & ";"
set rst = dbs.OpenRecordset (strSql, dbOpenDynaset)


...now you could loop thru the recordset and up date the table records in any way you choose...

HTH
Chris

 

[NickBridgens]

Thanks Chris.

Changed the way I was doing things and it worked a treat and pretty fast. Selected a record range based on one of the date fields, created a record set, copied to another table (it's an archiving routine) and then deleted the original records. Ace!

However, when I took it out to the customer's site with the same original data the routine first of all didn't work (needed to change the references priority on the client PC) then worked but the date selection didn't. It gave 4,500 records < Feb 1st when it should have been about 1,000 and entering date of 28/1/02 (UK style) it said the date was later than curent date (one of the validations in the code).

Presumably, the client's PC is not set to look at dates the same way as mine. Is it possible to enforce specific date layouts in code. For instance, the date format in the table and the form is short, input mask 99/99/00;0. On my PC (Win2k, Access XP) the date format in regional options is set to short (01/05/2002). On the forms dates show as 25/03/2002, but on the client's PC they show as 25/03/02.

An add-on from that is how do I ensure the same references are available? One PC is njot an issue, but there are lots of them out there!

Cheers

Nick B

 

[chrismcbride]

Nick
Dates continue to be an ongoing problem for me as well. What I know/think about them is this.
1. The stored value in an Access table is a Long Integer that basically represents the number of seconds since some arbitrary start date. (1000 BC, perhaps)
2. Any fomatting you do to a date is just that, FORMATTING. It has no effect on the original value.
3. Settings that determine how Access interprets a date are set in the Regional Settings in Windows.

While I am not sure of the exact error you are getting, it sounds like one that I had once. I solved it with the help of The Access Web and Joe Foster... http://www.mvps.org/access/datetime/date005.htm
There is lots of other useful stuff at this site as well.

As far as setting references on other machines, I know of no other way but manually. Others probably no more...
HTH
Chris