Updating records where a DATE field has gotten too old.

[lestermadoc]

I'm having trouble figuring out how to compare a record's date with the current date on a timer event. I want to perform this on every record in the database where a date field is not empty. I know how to use the datediff function, but not in an update query. Here is my code that doesn't work:

threshold = 24

rst.Open "update cases SET cases.flag = 0 WHERE" & DateDiff("h", rst!datein, Now) > threshold, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

No one seems to be able to help me!! any help would be greatly appreciated.

 

[Nkuha]

Are you using microsoft access as a front end to the database?

 

[lestermadoc]

Yes, I am using access as a front end. The "timer event" is on a form, so when the form is loaded I want my code to execute periodically at given intervals. In this way I will keep my records up to date, and any records that have been in a certain state for too long of a time will be flagged so the user knows something must be done with them.

 

[tlbroadbent]

An Update query doesn't return a record set. Therefore you shouldn't be opening a record set. You should Execute an action query.

Your code should be something like the following.

Conn.Execute ("Update cases SET cases.flag = 0 WHERE _
DateDiff(""h"", [datein], Now) > threshold" Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[lestermadoc]

Thanks tlbroadbent. I'm trying to exectute your code right now. I get this error message with it, however: "Operation is not allowed when the object is closed". I have limited experience with setting up a connection event, but here is the way I did it:

Set cnn = New ADODB.Connection

cnn.Execute ("Update cases SET cases.flag = 0 WHERE DateDiff(""h"", [datein], Now) > threshold&quot

cnn.Close
Set cnn = Nothing


In a class module I put this code:

Public WithEvents cnn As ADODB.Connection

Let me know if I can do this better!

 

[Guest_imported]

cnn.close

cannot be performed because the recordset isn't actually open at this point. Try removing that line and see if your code works then.

 

[lestermadoc]

Thanks katskorner. I took out the cnn.close and I get the same results.

 

[tlbroadbent]

You need to OPEN the connection before Executing the UPDATE.

Dim cnn As ADODB.Connection
Dim strCnn As String
Set cnn = New ADODB.Connection

' Open connection.
strCnn = "Provider=sqloledb;" & _
&quot;Data Source=<yoursource;Initial Catalog=dbname;&quot; & _
&quot;User Id=<user>;Password=<pasword>;&quot;
cnn.Open strCnn

cnn.Execute (&quot;Update cases SET cases.flag = 0 WHERE DateDiff(&quot;&quot;h&quot;&quot;, [datein], Now) > threshold&quot

cnn.Close
Set cnn = Nothing Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[lestermadoc]

Thanks! Please be merciful with this next mundane question. About this line:

&quot;Data Source=<yoursource;Initial Catalog=dbname;&quot; & _
&quot;User Id=<user>;Password=<pasword>;&quot;

I cannot seem to be putting the right data in. I have an sql server running on a computer with this name: &quot;Hal9000&quot;. The database I want to connect to is &quot;Client3&quot;. The sql server uses windows authentication (win2k AS) alone. I created a system DSN on the workstations connecting to this database. The name of this system DSN is &quot;Client3&quot;.

What's the correct syntax that I should use for this line?

 

[tlbroadbent]

Use this connection string. Note: You do not need a DSN.

&quot;Server=Hal9000;Database=Client3;Trusted_Connection=yes&quot;

Check the following link for more info about programming SQL server and ADO. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[lestermadoc]

strCnn = &quot;Server=Hal9000;Database=Client3;Trusted_Connection=yes&quot;

This string did not work - I get the error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

 

[lestermadoc]

p.s.

I've been working on this for the past month so your help is greatly appreciated. I created about 6 pages of code as an alternate solution, but it is unstable.

 

[tlbroadbent]

Sorry, I left off the provider.

strCnn = &quot;Provider=sqloledb;Server=Hal9000;Database=Client3;Trusted_Connection=yes&quot; Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[lestermadoc]

Great! This worked!

I'm getting an error with the execute query though:

&quot;'h' is not a recognized datediff option&quot;

I tried putting only one set of quotes around the &quot;h&quot; but then it says: &quot;expected: list separator or )&quot;

 

[Nkuha]

we must be very close now.

It does accept the connection string.

 

[tlbroadbent]

Use 'hh' or 'hour' rather than 'h'. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[lestermadoc]

now it gives the error &quot;Invalid column name 'now'&quot;

 

[tlbroadbent]

You definitely need to learn and use SQL Server T-SQL syntax rather than Access Jet SQL syntax. Replace Now with Getdate(). The () are required. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[lestermadoc]

Woo Hoo!

Tnks much, it all works now. For the record, this is the line that worked:

cnn.Execute (&quot;Update cases SET cases.flag = 0 WHERE cases.casestatus = 1 and DateDiff(&quot;&quot;hh&quot;&quot; ,[datein] ,Getdate()) >&quot; & threshold)

Thanks very much.