ADO, SQL, datediff, and tabledef 2

[cyberbiker]

I am working on converting an app that used ACCESS 97 tables and the jet database engine to use SQL Server 7 tables, MSDE and ADO.
I seem to follow most of what I need, but do not fully understand the code written by previous programmers.


There are numerous cases in the existing code where QueryDef and tabledef were used for no logical reason that I can see. Mostly, where Querydef and tabledef were used, I can handle pretty easily by using SQL Create Table, Alter Table etc.

But the following is an example of code that I do not know how to fix.


The function returns a value indicating the time difference between the last update and the current time using DateDiff.

How do I handle this without using TableDef?

I really should also ask whether I use tabledef with SQL Server and ADO? I would prefer to use another way if possible.

Function pfRackListRecent() As Integer
' Checks if the current rack list was recently generated
Dim db As Database, td As TableDef, sec As Variant


On Error Resume Next
Set db = DBEngine(0)(0)

db.TableDefs.Refresh
Set td = db.TableDefs(pcRACKLISTNAME)
sec = DateDiff("s", td.LastUpdated, Now)
pfRackListRecent = (ERR = 0 And sec < 120)
End Function
Thanks for reading this Terry (cyberbiker)

 

[MarkSweetland]

Function pfRackListRecent() As Integer
' Checks if the current rack list was recently generated
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = &quot;Select cnt=count(*) from pcRACKLISTNAME where &quot; & _
&quot; DateDiff( s , LastUpdated, getdate() ) < 120 &quot;
' I'm assuming the table may or may not be there
'and an error will result if not found
On Error GoTo err_handler
' if the table is not there, an error will result
rs.Open strSQL, &quot;<your connection info>&quot;
' The table was found, get cnt from rs
sec = rs(&quot;cnt&quot
'true if less than 120, otherwise false
pfRackListRecent = (sec < 120)
err_exit:
Exit Function
err_handler:
'Error occurred. Set the return value to false (probable no table)
pfRackListRecent = False
Resume err_exit
End Function




Mark

 

[CajunCenturion]

I think that I would use ADOX for this purpose. First, you need to include into the project a reference to Microsoft ADO Ext 2.7 for DDL and Security.

Then in your code you can do the following:

Dim Catalog as ADOX.Catalog

Catalog.ActiveConnection = <Your Connection String>
LastUpdate = Catalog.Tables(<TableName>).DateModified
sec = DateDiff(&quot;s&quot;, LastUpdate , Now)
If Err = 0 then
If Sec < 120
MsgBox &quot;Table Has been updated in last 2 minutes&quot;
End IF
Else
MsgBox &quot;Error Encountered ....&quot;
End If
Set Catalog = Nothing

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cyberbiker]

Thanks Mark
I think what I failed to ask is if the lastupdated property exists for a SQL server 7 table or does that only apply to a jet workspace? Will I need to perhaps add a field to each record?

Thanks a lot for your help and extra effort to point out the lack of error trapping. This app has been worked on by numerous people of different skill levels and work ethics. I am adding error trapping as I go. (along with fixing problems of having numerous connections open for no apparent reason, opening recordsets without closing them, etc, etc, etc.)


Terry (cyberbiker)

 

[MarkSweetland]

I didn't read your initial question close enough. LastUpdated is a DAO property that returns the date and time when the table's data was changed. SQL Server doesn't have a LastUpdated. As in CajunCenturion's example, the DateModified property can be retrieved, but that returns the date and time the table's structure was modified.

You may have to add an additional column and/or table in SQL Server and attach a trigger to the pcRACKLISTNAME table to update this column each time the data is modified. Or, have the process that modifies the data update this additional field when it runs.



Mark

 

[cyberbiker]

Thanks to both for your help.
If I have what passes for logic in this app figured out, I need to create this table and use it then delete it.

There is code that calls this function but for the life of me I cannot figure out why right now. I have just realized that the sub that is to call this function never executes.
It does seem to be a check on &quot;when&quot; the table was created though so Cajuns idea would work if I turn out to need to do anything.

Just &quot;wasted&quot; a couple more hours. I do check to be certain that each function or sub that I come to is actually called from somewhere, but did not think to check to see if the sub calling the function was actually called.
(I am basically going through this code &quot;line by line&quot; right now and had not yet reached that module)
The information you have given me will surely help me at some point though. Thanks a lot. Terry (cyberbiker)