Re: Linked Tables in Access

any use of SQL Passthru, Linked Tables or any other use of MDB / MDE
_WILL_ lead to corrupted data

you should just use Access Data Projects.
ADP 2002, 2003, 2007 have wizards for writing 'action queries' on the
SERVERSIDE.

it's more efficient to have ONE FLAVOR OF SQL AND DO IT WELL then to
always deal with differences in dialects

-Aaron
ADP Nationalist


Declan wrote:
If you want the processing to be done
on the server, use SQL passthrough queries<
Do action queries *without* Access specific functions get processed on the
server, or would I also need to convert *those* queries to passthrough
queries to get them to be processed on the server?

Even SQL Server database files can become corrupted, but the risk is far lower<
I've been trying to understand why Access database files become corrupt.
I've read that the processing is done on the client and that the Access
database file on the server becomes corrupt if a write operation is
interrupted, but I want to understand the details of a write operation. I.E.
Suppose that records need to be updated. Is the following sequence of
operations correct? ... The data pages that need to be updated are locked on
the server, sent over the network and loaded into the memory of the client.
They are then changed by the client (all the while being in the client's
memory and never being written to the client's hard drive). Then the changed
data pages are sent back over the network to the server where they are loaded
into the memory of the server. Then, once the server has all of the changed
data pages in its memory, they are written to the Access database file on the
server's hard drive. ... Doesn't the server wait until all of the changed
data pages are loaded into its memory, until it gets the complete change back
from the client, before it writes the change to the database file? What are
the actual sequence of events of a write operation, and where in that
sequence of events could the write operation be interrupted leaving the data
file corrupt?

Jet doesn't use transactions, so failed transactions can't be rolled back.<
Even if the Jet Expression Service processes a query on the client, does SQL
Server still use transactions to write the changes to the database, thereby
minimizing the chance of corruption? Over the past 12 years I've written
considerably large access database programs (backend database files combined
are about 1 GB with up to 50 users accessing them at once). Recently we've
been getting corrupt databases 3-4 times a week, and I have to kick everyone
out of the programs to repair a backend Access database file. This is a much
bigger problem than any performance issues that we are having. If I export
the tables to SQL Server and link to them from the front end Access programs
without making any changes to the front end Access programs, will this help
us not get as many corruptions? Will I need to make any other changes for
the programs to function correctly? Will it hinder the performance of the
programs? I realize that front end programs would need to be re-written to
take advantage of the power SQL Server, but the programs are considerably
large so I would want to first put the tables in SQL Server without changing
the programs at all, and then re-write the programs one form, one report, one
module at a time, gradually changing the queries to pass through queries and
gradually making other changes to the programs after the data is put in SQL
Server and while the users continue to use the programs. Is this a viable
approach?


"'69 Camaro" wrote:

Hi, Declan.

action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client?

If you're using Access built-in functions in your queries, then you're using the
Jet Expression Service, which means you're doing the processing on the client,
not the server, because only Jet can access the Jet Expression Service, but SQL
Server and other database engines can't. If you want the processing to be done
on the server, use SQL passthrough queries, which would require T-SQL syntax.
Of course, T-SQL doesn't recognize VBA, so you'd have to use built-in T-SQL
functions or create your own custom T-SQL functions.

Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?

Even SQL Server database files can become corrupted, but the risk is far lower
that this will be a problem with linked SQL Server tables than when storing the
data in Jet tables. The reason for this is that if a write operation is
interrupted in a client/server database engine such as SQL Server, the failed
transaction will be rolled back so that the data remains in a consistent state.
Jet doesn't use transactions, so failed transactions can't be rolled back.

For example, if a debit of $200 were made from your savings account and a $200
credit were made to your checking account (which is what happens when you
"transfer" money from savings to checking), and the power failed after the debit
but before the credit to your checking account, Jet would "lose" $200 from your
savings account. If the same power failure happened in SQL Server, the $200
debit from savings would be rolled back when the database was recovered, as if
no transaction had occurred.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


"Declan" <Declan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9712B4F5-D0DF-47F0-B684-FC22576C9E88@xxxxxxxxxxxxxxxx
If SQL Server tables contained on a Server computer are used as linked tables
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?




.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
  • Re: How to commit tran log to .edb
    ... new database and move all the mailboxes to it. ... On our exchange server i get the databse corruption error and from the ... A corrupt database will generally not mount. ... delete those log files. ...
    (microsoft.public.exchange.admin)