I am working on a system where I need to pull in the results of a query and then allow the user to update the query result back to the database. As I am using ADO to get the information from SQL Server, I can certainly get the initial Top query results. However, when the client tries to update the information in the display, an error message stating that the result set is not able to be modified occurs.

I've also started taking the alternate route of using VBA to load the data into form fields, but we're talking hundreds of fields here in the long run.

What is the best way for me to approach this?

Thanks in advance,

Michael

Two things:

When you say "pull" the data, what exactly are you doing to move data from the SQL Server to the Access front end?

The best way to do this depends on your requirements. What are those for this application?

Normally when I work with SQL Server, I bring local copies of the data to the front end, and then any new records or modifications are sent to the server as a seperate transaction. How are your coding skills?



mmcdonal

I'm using SQL from within VBA using ADO, which returns resultsets. As the data fields that I'm working with are spread across multiple records and not on one contiguous record, it is problematic.

From a SQL database, obtain select records for a tab control to display fields that are on separate records, compute a verification value for the records selected and allow for the update of said records when necessary.

As an example, I have 13 records containing year, schedule, line items and long numeric values. I also have a record that contains the balance as reported. I need to retrieve the records for a particular year and schedule, compute the total of the long numeric values (based on the fact that some line items are to be subtracted rather than added to the balance) and verify that the reported balance is correct. When it isn't, allow the client to update the values.

This is a multi-user project and I have yet to figure out how to create local temp tables within a project. When I create tables, they are created on the SQL server and that isn't what I need, I think.

As far as coding skills, I'm fairly good, I think. This is the first project that I've used SQL server, so the interaction between the Access project and ADO is new to me, but I'm figuring things out as I go along. This particular issue has me scratching my head bald, though!

Thanks in advance for your help,

Michael

Multi users are prblematic in this case since if one user pulls data over to a local table and wants to make corrections, and another user does the same, then which update is correct and which do you take? Normally this is resolved with first come, first served.

Can you implement a check our scheme?

Normally I pull data to local tables and base forms and reports off of those, and then the user can update the necessary fields based on the ID that was pulled with the record they are updating, or a new record can be easilt added.

I would hate to connect to the SQL Server for this function. That is a total last resort.

How heavy and concomitant are the multi users?

mmcdonal

I think they can live with first come, first served. The updates are usually accomplished by one or two people out of 10 users, so it isn't that big of an issue.

A check-out scheme may be something I'd look into if the updates become more frequent than we think they are, but they're not heading in that direction at the moment as far as planning goes.

So I think we're back where I started. I need to implement something that will retrieve the resultset, allow me to calculate and verify the balance and then allow the client to update the values as necessary. Got any ideas?

Michael

Yes, pull the results locally as needed, even using the On Current event of the form.

Allow the user to do calculations on the form. Check the new value agains the old, and then pull the next recordset.

I would create the query on the server. Then pull the record(s) you need from that. Store them either in memory or in a local table. Then pass updates back. You can also use public variables to store single records and check for changes.

Why does a user have to confirm a calculation by hand?

How do you want to handle it?



mmcdonal

Using VBA and ADO recordsets, I'm getting the data I want, so that isn't a problem.

The calculations I mentioned are locked textboxes that I have on the form, so in reality the calculations are being done when the form loads. I'll have to add code to the form so that when a value is updated in the query that the locked textboxes are recalculated.

I'm at a complete loss on how to create a local table in an ADP/ADE project. When I attempt to create any table, Access assumes that I want to save the table on the server. How does one go about creating a local table?

Again, thanks for your help!

Michael

Oooooh, ADP!? I never use them. I just create MDB/MDEs using Access SQL. I use Enterprise Manager and SQL Query Analyzer instead of the ADP functions. I create the tables using the Enterprise Manager New Table..., and the same with Views (queries.) Then I pass data as I want to. This also allows me greater control of the business rules. ADP/SQL wants business rules in the database. I always put them in the application layer where they belong. Can't help with the local table issue in an ADP. Can't do it. Your best bet would be an array, but that has its own headaches (think of an array as a client side Temp table.)


mmcdonal