How do I find "modified date" of table

I want to include in a web page a statement something like:
"The above data was last modified on nn/nn/nnnn"

Using MSAccess I see that on the Tables window and using the 'Details'
icon or the 'properties' icon for the table in question there is a
modified date recorded.

Can this date be extracted using ASP VB & ADO so that I can put it into
the displayed statement above?

I have spent hours searching the web and Googling but haven't found the
answer.

Any help much appreciated.

Thanks
David

Re: How do I find "modified date" of table

David Floyd wrote:
> I want to include in a web page a statement something like:
> "The above data was last modified on nn/nn/nnnn"
>
> Using MSAccess I see that on the Tables window and using the 'Details'
> icon or the 'properties' icon for the table in question there is a
> modified date recorded.

That property does not refer to the data in the table: it refers only to the
table design.
If you need to track the data modifications, you will need to add a
DateModified field to the table and query it using Max(DateModified)

>
> Can this date be extracted using ASP VB & ADO so that I can put it
> into the displayed statement above?
>
> I have spent hours searching the web and Googling but haven't found
> the answer.
>
I don't believe this property is exposed to ADO. I've checked here:

http://msdn.microsoft.com/library/en-us/oledb/htm/oledbprovj et_appendix_a_properties.asp

and found no sign of this property.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How do I find "modified date" of table

David Floyd wrote:
> I want to include in a web page a statement something like:
> "The above data was last modified on nn/nn/nnnn"
>
> Using MSAccess I see that on the Tables window and using the 'Details'
> icon or the 'properties' icon for the table in question there is a
> modified date recorded.
>
> Can this date be extracted using ASP VB & ADO so that I can put it into
> the displayed statement above?
>
> I have spent hours searching the web and Googling but haven't found the
> answer.
>

You can access the DateModified property of the table object using
ADOX.

http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/ado270/htm/adobjtable.asp

<%
Dim ConnStr, Adox, Conn, tbl
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source="
Set Adox = Server.CreateObject("ADOX.Catalog")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConnStr
Adox.ActiveConnection = Conn

For Each tbl In Adox.Tables
Response.Write "" & tbl.Name & "
"
Response.Write "Date Created: " & tbl.DateCreated & "
"
Response.Write "Date Last Modified: " & tbl.DateModified & "
"
Next

Conn.Close: Set Conn = Nothing
Set Adox = Nothing
%>

--
Mike Brind

Re: How do I find "modified date" of table

Mike Brind wrote:
> David Floyd wrote:
>> I want to include in a web page a statement something like:
>> "The above data was last modified on nn/nn/nnnn"
>>
>> Using MSAccess I see that on the Tables window and using the
>> 'Details'
>> icon or the 'properties' icon for the table in question there is a
>> modified date recorded.
>>
>>
>
> You can access the DateModified property of the table object using
> ADOX.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/ado270/htm/adobjtable.asp
>
Ah! I missed that.Thanks
Unfortunately, that does not address his stated intention to diplay the
date the data was last modified.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How do I find "modified date" of table

Bob Barrows [MVP] wrote:
> Mike Brind wrote:
> > David Floyd wrote:
> >> I want to include in a web page a statement something like:
> >> "The above data was last modified on nn/nn/nnnn"
> >>
> >> Using MSAccess I see that on the Tables window and using the
> >> 'Details'
> >> icon or the 'properties' icon for the table in question there is a
> >> modified date recorded.
> >>
> >>
> >
> > You can access the DateModified property of the table object using
> > ADOX.
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/ado270/htm/adobjtable.asp
> >
> Ah! I missed that.Thanks
> Unfortunately, that does not address his stated intention to diplay the
> date the data was last modified.
>

No, but I see you've covered that already.

--
Mike Brind

Re: How do I find "modified date" of table

In message of Sat, 30 Sep 2006, Bob Barrows [MVP] writes
>David Floyd wrote:
>> I want to include in a web page a statement something like:
>> "The above data was last modified on nn/nn/nnnn"
>>
>> Using MSAccess I see that on the Tables window and using the 'Details'
>> icon or the 'properties' icon for the table in question there is a
>> modified date recorded.
>
>That property does not refer to the data in the table: it refers only to the
>table design.
>If you need to track the data modifications, you will need to add a
>DateModified field to the table and query it using Max(DateModified)
>
>>

I see what you mean, and I hadn't noticed when the date actually
changed, because I'm still building a project.

I see now that that date doesn't change when you add data.

I understand what you say about adding a new field and using
Max(DateModified) and will now experiment with that.

Thank you for your help.

David

Re: How do I find "modified date" of table

In message of Sat, 30 Sep 2006, Bob Barrows [MVP] writes
>David Floyd wrote:
>> I want to include in a web page a statement something like:
>> "The above data was last modified on nn/nn/nnnn"
>>
>> Using MSAccess I see that on the Tables window and using the 'Details'
>> icon or the 'properties' icon for the table in question there is a
>> modified date recorded.
>
>That property does not refer to the data in the table: it refers only to the
>table design.
>If you need to track the data modifications, you will need to add a
>DateModified field to the table and query it using Max(DateModified)
>

I can't get this to work, my code (amended extraction) is:


<%

Dim objConn,db,DataConn

Set objConn = Server.CreateObject("ADODB.Connection")

DataConn = Server.MapPath("..\private\MyDataBase.mdb;")
objConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DataConn


Dim objRS
Dim strSQL
Dim dateModified

strSQL = "SELECT MAX(dateModified) FROM [Full Details Query] WHERE
county = '"& county &"'"
Set objRS = objConn.Execute(strSQL)
dateModified = objRS("dateModified").value

response.write(dateModified)
%>

I then receive this error:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested
name or ordinal.

Whereas:
If I change the strSQL line to:
strSQL = "SELECT * FROM [Full Details Query] WHERE county = '"& county
&"' ORDER BY dateModified DESC"

it works, but it does seem a long winded way to get there.

I'm wondering why my line using MAX doesn't work.

Thanks for any help
David

Re: How do I find "modified date" of table

David Floyd wrote:
> I can't get this to work, my code (amended extraction) is:
>
>
> <%
>
> Dim objConn,db,DataConn
>
> Set objConn = Server.CreateObject("ADODB.Connection")
>
> DataConn = Server.MapPath("..\private\MyDataBase.mdb;")
> objConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" &
> DataConn
>
> Dim objRS
> Dim strSQL
> Dim dateModified
>
> strSQL = "SELECT MAX(dateModified) FROM [Full Details Query] WHERE
> county = '"& county &"'"
> Set objRS = objConn.Execute(strSQL)
> dateModified = objRS("dateModified").value
>
> response.write(dateModified)
> %>
>
> I then receive this error:
>
> Error Type:
> ADODB.Recordset (0x800A0CC1)
> Item cannot be found in the collection corresponding to the requested
> name or ordinal.

Yep

>
> Whereas:
> If I change the strSQL line to:
> strSQL = "SELECT * FROM [Full Details Query] WHERE county = '"& county
> &"' ORDER BY dateModified DESC"
>
> it works, but it does seem a long winded way to get there.

Not only that, but you're retrieving ALL the records just so you can see the
content of ONE record. At least use
"SELECT TOP 1 ...

>
> I'm wondering why my line using MAX doesn't work.
>

Two options:

1. give the field a name by using a column alias:

strSQL = "SELECT MAX(dateModified) As LastModified ...
....
dateModified = objRS("LastModified ").value

2. Better - refer to the field by its ordinal position instead of using the
non-existent field name:
dateModified = objRS(0).value


Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How do I find "modified date" of table

In message of Sun, 1 Oct 2006, Bob Barrows [MVP] writes


[Many useful links in reply to my query]

Many thanks for your help. I will read all the links you have given,
and take on board your advice.

Thanks,
David