Solved: Access 97 table question

[austenr]

I have inherited the task of converting some access tables to another system. I dont have much to work with and I was wondering if there is a way to find out when a table was last used?

[jackd]

Austenr,

Don't know of a way to determine last used date. But here is a small routine that will identify when each table in the database was LastUpdated.
[VBA]
Sub ListTableLastUpdateDates()
Dim db As DAO.Database
Dim tbl As TableDef
Dim s As String
Set db = CurrentDb
For Each tbl In db.TableDefs
s = s & tbl.LastUpdated & vbTab & tbl.Name & vbCrLf
Next tbl
MsgBox " Date of Last Update TableName" & vbCrLf & s
End Sub
[/VBA]

[austenr]

AFAIK, you cant run a VBA macro in 97. That would be fine in 200 or 2003 but not in 97 unless I am wrong.

[jackd]

austenr,

Yes, you are wrong. I ran the sample code in 2000 and 97 and it works fine.

Open your database (mdb),
go to modules
click new
paste in the code and run
It will list the Tables and the LastUpdateDate for Each

[austenr]

Thanks. Not used to working with such an archaic system.

[austenr]

That got me by that but I have a different problem now. I have a report linked to a button to run. If I do that, it works fine. If I got to the Reports tab and double click it, there are input boxes that pop up asking for input variables. As far as I can see there is nothing in the code that is prompting for that information. Any ideas?

[jackd]

Austenr,

There could be one or more queries somewhere in the code to get parameters in preparation of Selecting data to be included in the report.

In this type of report, the user would be prompted for some values, or select something(s) from a list, then a SELECT query would be created to get the appropriate data for the report.

[austenr]

Here is the only query that is running for the report I think:

[VBA]SELECT [Post-Award Information]![ADMIN FIRST NAME]+" "+[Post-Award Information]![ADMIN LAST NAME] AS [ADMIN NAME], [CompleteGCS Contact Info]![FirstName]+" "+[CompleteGCS Contact Info]![LastName] AS [Full Name], [Post-Award Information].[ACTIVITY#], [Post-Award Information].[PROJECT TITLE], [Post-Award Information].[CONTRACT#], [Post-Award Information].[BUDGET END DATE], [Post-Award Information]![BUDGET END DATE]+30 AS [B plus 30], [GCS's].Phone, [GCS's].Email
FROM ([GCS's] INNER JOIN [Post-Award Information] ON [GCS's].GCS = [Post-Award Information].GCS) INNER JOIN [CompleteGCS Contact Info] ON [GCS's].GCS = [CompleteGCS Contact Info].GCS
WHERE ((([Post-Award Information].[BUDGET END DATE])<=Now()+120 And ([Post-Award Information].[BUDGET END DATE])>=Now()+61) AND (([Post-Award Information].[ACTIVITY STATUS])="active"));[/VBA]

[jackd]

austenr,

I just went back to an old Acc97 database with a report that prompts for a Date. It's actually requesting the actual Date of the Next Friday.

Does your prompt form look like the attached image?

[austenr]

yep. exactly (well not exactly) the caption is different. Do you know what causes this?

[jackd]

austenr,

I found an interesting item on the report. In the report design there is a Textbox. The textbox shows a value NextFriday(). There is a Function in one of the modules called NextFriday(). Ths function works with today's Date and, if it is not Friday, it makes a calculation and sets the value of the textBox to the actual date (day/month/year of the next Friday.)

Here's the key point, if the value of the textbox had been set up as "=NextFriday()", the date would be calulated automatically via the Function. However, because the textbox vale is set up as "NextFriday()"

NOTE::: NO = SIGN

Then Access prompts for a Parameter value (see the little popup form in my previous message). I tried running the report and entered April 20, 2007 in response to the prompt -- worked fine. (But I wasn't sure where the prompt form was coming from).

When I modified the textbox value to =NextFriday() , the form loads and runs as anticipated.

I don't know if someone changed the textbox value, I've always used =FunctionName as the value. I wasn't aware that leaving out the = would force a prompt for a parameter - maybe this is just a red herring???

This may be helpful to your situation.

[austenr]

Here is the criteria in the query:

[VBA]<=Now()+120 And >=Now()+61
[/VBA]

Does this look right to you? It also prompts me for a format....Forgot to mention that earlier.

[jackd]

austenr,

I don't see anything wrong with that. It's looking at "active" records where the
...((([Post-Award Information].[BUDGET End DATE])<=Now()+120 And ([Post-Award Information].[BUDGET End DATE])>=Now()+61) ..

for 2 to 4 months from today.
It looks like this timeframe is "hardcoded". This is a report looking ahead, perhaps identifying upcoming "work"...

[austenr]

Thanks for the look. I am probably missing something here. Just fell into this situation with no documentation. Will have to give this a think over the weekend. The part that I can't seem to let go of is that if you assign it to a button it runs with no problems. Once you double click it you get the dialog boxes.