Procedures

[Countdown]

Hi,

Is it possible to have a procedure that automatically triggers when a field (ex. row 5 column 3 in table tTable) changes?

Remember something about that beeing possible in microsoft sql server, but not how and that may have absolutely nothing to do with access. Anyway, thats my question

Any hints or help would be most welcome

/Mattias

 

[M8KWR]

I could do with getting a nit more information off you if thats ok

when you say row 5 column 3. It sounds like you want this to be executed in excel and not access.

You are using access?

If you are using access, and then you want a certain field within the database, after it has been updated/changed to trigger another event then this is quite simple. But i could do with knowing exactly what you mean, if that ok.

Also, because you said row 3, does this mean you only want this to happen against one particular record in the table???

 

[Countdown]

Hi, that I said row and column number where to expalin things, which didnt work

Anyway, what I want to do is that when the value of a column (on any of the rows) changes I want something to happen.

Exactly what I want to happen is this:

When a program changes a value in a table, I want to store the old value and the new value in another table.

Would be really great if that where possible, but if it isnt, but it is possibe to atleast trigger a procedure (or something) when a change occurs, that would be good to know to.

Hope it expalins my question.

And, yes, I am using access and not excel.

Regards Mattias

 

[WayneRyan]

Mattias,

Don't really agree with the concept here.

Get your form in Design View.
Right-Click on the field in question, Select Properties.
Select the Event Tab and put this "Event Procedure" in the AfterUpdate event:

DoCmd.RunSQL "Insert into OtherTable (Field1, Field2) " & _
             "Values ('" & Me.YourField.OldValue & "', '" & Me.YourField & "')"

hth,
Wayne

 

[Pat Hartman]

When a program changes a value in a table, I want to store the old value and the new value in another table.

One of the big differences between Jet and a real RDBMS is that Jet doesn't provide triggers. The closest thing is form events. So, you can do what you are asking but ONLY in a form event. If the table is changed some way other than via the form where you placed the event, the event will NOT trigger and no copy will be made. A trigger in a RDBMS is processed by the db engine REGARDLESS of how a table is changed.

 

[Countdown]

Hi and thanks for your replies.

Thats exactly what I want to do, but what is wrong with the concept?

Oh, so if I am using Jet to connect to a .mdb file from my web app, it is not using a RDBMS? If I use a program that is seems to be programmed in acces, it ends with .mdb and when I open it it will open Access, but it will look very different, does that use a RDBMS? And why do you want to connect an access database/file to a SQL Server?

Sorry for all this questions.

/Mattias

 

[Pat Hartman]

Oh, so if I am using Jet to connect to a .mdb file from my web app, it is not using a RDBMS?

- Although Jet does a pretty good job of simulating a real RDBMS, it is lacking in some areas. The reason that it cannot support triggers is because nothing runs on the server. Everything runs locally. That makes Jet a file server rather than a database server. With SQL server, Oracle, DB2, etc. a process runs on the server that waits for requests. Access or whatever you are using as a front end, sends a request to the server process and the server process performs the requested action and returns the requested recordset.

And why do you want to connect an access database/file to a SQL Server?

- Access is BOTH a development environment and a data store. You can develop Access applications that use local Access (Jet) tables OR you can develop Access applications that use linked ODBC tables (SQL server, Oracle, DB2, etc.). There are many reasons to use ODBC tables rather than Jet tables. In fact, most of the Access apps that I develop do just that. Linking to an ODBC database removes ALL the constraints imposed by Jet. This type of app will support many more users and larger databases.

Although you can access Access (Jet) tables from a web page, that is far less robust than using a real RDBMS. Since Jet doesn't support triggers, anything you would need to do to edit the data would need to be done by the web page since you can't write a stored procedure or trigger to run on the server.