Best Practise For Updates between Access 2000 and SQL Server

[PeteP]

Hi,

Let me paint a picture...Access 2000 frontend, linking to a SQL Server
backend, inherited database, me - fairly new at this! )

The developer has, for better or for worse, (I'm not sure), designed a
frontend that grabs a set of order records from SQL and pulls them into
a form. While the records are there, the user can allocate the order to
a person by selecting their name, we would only be talking about 300
records at a time. When the user closes the relevant form, the orders
that have been updated in the frontend are sent to the SQL-Server.

Now, from what I can make of the code that Updates back to SQL, the
developer initially grabs a recordset of those orders in the Frontend
requiring an update and then loops through them one by one and updates
the SQL-Server record using a field call OrderID.

My questions are: Is this the only method of doing this? Meaning -
surely there is a way to do a bulk Update between the two applications
that doesn't require cycling through records.

And what is considered best practise for doing bulk record updates
between Access and SQL-Server? I could probaly re-invent the wheel
myself, but if anyone can point me in the right direction it would be
appreciated.


Kind Regards

Pete

 

[Pat Hartman]

I would run an update query unless the conditions of the update were too
complex to recreate in a query. The update query would almost certainly be
faster than the code loop. The batch updating process currently in place is
a little frightening to me in a multi-user environment. Is there a
possibility of someone else updating an overlapping set of data during the
same time frame? Is there any error handling to take care of this
possibility?

 

[PeteP]

Hey Pat,

The update query would only need to be a really basic one...but not I'm
not sure of the syntax would be, presuming it would involve some kind
of inner join maybe?

ie UPDATE dtblActivities
Set SQL-SERVER.PersonalId = ACCESS.PersonalD
WHERE SQL-SERVER.RecordId = ACCESS.ID

In practise, it is very unlikely that users will update the same
records at the same time.

 

[Brendan Reynolds]

It sounds to me as though the developer has attempted to implement an
'intermittently connected' approach - connect, grab the data, drop the
connection, work with the data locally, connect, update the data, drop the
connection. The developer may have adopted this approach for good reason.
Alternatively, the developer may have adopted this approach because he/she
was more accustomed to working in environments such as Web-based
applications, were this kind of approach is more common. From this distance,
I wouldn't like to attempt to guess which of these two scenarios you're
dealing with, or whether the original developer's approach is the optimal
approach or not. The devil, as they say, is in the details.

 

[Gary Walter]

Hi,

Let me paint a picture...Access 2000 frontend, linking to a SQL Server
backend, inherited database, me - fairly new at this! )

The developer has, for better or for worse, (I'm not sure), designed a
frontend that grabs a set of order records from SQL and pulls them into
a form. While the records are there, the user can allocate the order to
a person by selecting their name, we would only be talking about 300
records at a time. When the user closes the relevant form, the orders
that have been updated in the frontend are sent to the SQL-Server.

Now, from what I can make of the code that Updates back to SQL, the
developer initially grabs a recordset of those orders in the Frontend
requiring an update and then loops through them one by one and updates
the SQL-Server record using a field call OrderID.

My questions are: Is this the only method of doing this? Meaning -
surely there is a way to do a bulk Update between the two applications
that doesn't require cycling through records.

And what is considered best practise for doing bulk record updates
between Access and SQL-Server? I could probaly re-invent the wheel
myself, but if anyone can point me in the right direction it would be
appreciated.

Hi Pete,

I could imagine where the above makes sense
in my limited experience.

The goal might be to *not* have a heterogeneous join
between Access and SQL Server (which has the possibility
of bringing *all* of the records over from SQL Server table(s)
to accomplish the update).

If I pull out the Access records (in a recordset) that need to be
sent back to SQL Server, I can pull out the field values
for each record that needs updating assigning to
variables, then construct a simple update query
for each record that uses *values*, thus no heterogenous
join is needed.

The only other way I could imagine would be
to append Access records to an empty SQL table,
then run stored proc or passthrough that updates
via both tables on the server.

Again, "within my limited experience!"

good luck,

gary

 

[Pat Hartman]

No, the update should refer to the table being updated. dtblActivities
doesn't appear any place else. If you are not familiar with SQL syntax, it
can be easier to build queries with the QBE. To build a query that updates
tblA with corresponding values from tblB, Add both tables to the QBE grid.
Draw the join line to connect them. Select the columns from tblA that you
want to update. Change the query type to Update. Populate the UpdateTo
cells with the names of the related columns from tblB. Use the fully
qualified name and enclose the parts in square brackets - [tblB].[fld1]