Tracking record changes from Access application (1 Viewer)

Rakesh935

Registered User.
Local time
Today, 19:08
Joined
Oct 14, 2012
Messages
71
Hi,

I've developed a MS Access application (UI) having linked tables from SQL Server.

The application would be used by multiple users (about 8 users) for record creation, deletion and update through forms (TextBox and ComboBox controls).

I was able to achieve/complete the development until record creation and deletion with user and date for creation/deletion tracking details, but stuck with tracking the record update process.

For tracking the user details, I'm using/storing the login ID as user used while logging into the access application.

Request your advise.

Note: I tried through doing it through macros but it didn't work.

Please let me know if any further information required.

Thanks,
Rakesh
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2002
Messages
42,970
If you have recovery correctly configured for SQL Server, it will log all changes to all records. Making sense of these log records can be difficult but they are there.

What do you need in addition to what SQL Server is already doing for you? There are many posts here on simple logging systems intended for Jet/ACE since they do not log changes.
 

Rakesh935

Registered User.
Local time
Today, 19:08
Joined
Oct 14, 2012
Messages
71
If you have recovery correctly configured for SQL Server, it will log all changes to all records. Making sense of these log records can be difficult but they are there.

What do you need in addition to what SQL Server is already doing for you? There are many posts here on simple logging systems intended for Jet/ACE since they do not log changes.
Thanks for your reply Pat.

I was actually bit confused with the .old value property. However, I got it figured and completed the development as well.

Now, the only area of concern for me is the performance of the application since there are about 8 concurrent users of the application on a daily basis along with database (SQL Server) holding about 5 million records.

It would be really great and helpful for any advices with regards to the performance optimization of the application.

Thanks,
Rakesh
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2002
Messages
42,970
#1. Don't bind forms to tables or naked queries. One of the important points about using ODBC is having the database engine do the heavy lifting. So, bind your main forms to queries that have criteria to severely limit the number or rows returned. My main forms almost always return only a single row.
#2. Be careful with your queries. Although Access naturally attempts to make all queries pass-throughs, you can defeat it. SQL Server doesn't know VBA so any query that contains VBA or UDF functions, could be deconstructed by Access and not sent as a pass-through. Access can convert VBA functions that have SQL equivalents so those are not a problem. Functions in the select clause are less of a problem because Access can send the rest of the query and apply the function on the recordset that is returned. Functions in other clauses will prevent access from passing through the query. It will ask for the data from all the involved tables to be returned and it will apply the criteria, etc locally.
#3. Sometimes views can optimize your joins so use them to speed up common joins. Just make sure they are updateable or you run into other problems.
#4. Sometimes you might need a stored procedure for complex reports.

In the twenty-five years I've been working with Access, I've never had to use an unbound form. As long you control the data you select, you should be fine.
 

Rakesh935

Registered User.
Local time
Today, 19:08
Joined
Oct 14, 2012
Messages
71
#1. Don't bind forms to tables or naked queries. One of the important points about using ODBC is having the database engine do the heavy lifting. So, bind your main forms to queries that have criteria to severely limit the number or rows returned. My main forms almost always return only a single row.
#2. Be careful with your queries. Although Access naturally attempts to make all queries pass-throughs, you can defeat it. SQL Server doesn't know VBA so any query that contains VBA or UDF functions, could be deconstructed by Access and not sent as a pass-through. Access can convert VBA functions that have SQL equivalents so those are not a problem. Functions in the select clause are less of a problem because Access can send the rest of the query and apply the function on the recordset that is returned. Functions in other clauses will prevent access from passing through the query. It will ask for the data from all the involved tables to be returned and it will apply the criteria, etc locally.
#3. Sometimes views can optimize your joins so use them to speed up common joins. Just make sure they are updateable or you run into other problems.
#4. Sometimes you might need a stored procedure for complex reports.

In the twenty-five years I've been working with Access, I've never had to use an unbound form. As long you control the data you select, you should be fine.
Thanks Pat, will surely consider your inputs while finalizing the development process.

Just have one last question:

In a form I have two combobox (e.g. Combo A and Combo B, and Combo B is dependent on Combo A's input), so post update of Combo A the population in Combo B takes a while (about 10 sec) to populate (Requery Method) so is there a way to optimize this process, please advice.

Additionally, If I assign index in SQL server for the table having respective field elements then would that be of any help?

Thanks, Rakesh
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2002
Messages
42,970
Access automatically creates hidden indexes for foreign keys. With SQL Server, you need to make your own. There is a fine line between not enough indexes and too many indexes. Start with the minimum and add more as you find other things you want to search on. Remember that using LIKE will almost always prevent the use of any index so that will always slow things down.

10 sec is a long time for a combo to populate. How many rows? Combos should not be used for large recordsets.
 

Users who are viewing this thread

Top Bottom