Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is there anyway to tell when a database was last accessed or changed?

Is there a simple way to tell when a database was last accessed or changed?

We have some databases on old servers which we would like to discontinue but do not know if they are being used.

We would rather not just take them offline.

Is there anyway I can find out if they are still being used?

Please help if you can.

Regards, Major (that is my Christian name ;-)

I've run into this problem a few times. The best way I have found to deal with this is to set up a trace on the databases for reasonalbe period of time. With the trace, you should be able to see who accessed what, when, and with what application. This info can be helpful in determining how the database is being used in your organization.

The difficult part is to determine what constitutes a reasonable period of time. Some business groups access some databases on monthly, some on quarterly, and others on annual cycles. Other databases are used upon occurance of special events such as an audit or a lawsuit.

So, in addition to the trace, you may want to send out a survey asking for users to step forward. You will also want to be certain to take a few good backups and be able to access them very quickly should a user step forward after the database has been decommissioned.

Finally, get approval of higher-ups before dropping the database. This just helps spread the blame should someone in the business get upset.

Good luck,
Bryan

|||

Adding on to Bryan's comments, when the decision is finally made, I normally just take the databases 'off-line' for a period of time. Then if something breaks, it is quick and easy to put the database back online. (I've been know to keep databases in 'off-line' mode up to a year before completely removing them from the server.

Even with backups, when some upper level 'suit' is put out because the special use database isn't available and he must have a report in a hour, it is a boon to quickly revert to on-line status and say 'oops'.

Is there any way to know the last access time of a table?

I have some junk tables in my database, but I am not sure if they are really not in use.

Does sql server log the last time that a table gets accessed? when I say access I mean any actions toward the table, including select, update, insert, etc.

thanks in advance.

I am unsure about your answer, but you can log it manually to a table by generating stored procedures for selecting/updating/etc.

|||thanks for the suggestion. I thought about that too but I can't possibly rewrite all codes across the application for this. also it may be too resource intensive. shouldn't there be a way to check the last access in sql server? apparently in the file-based system you can do so.