Re: Is it possible to link via ADODB from an Access 2K .mdb file?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Locking records on SQL Server from any client is a BIG mistake. SQLS
is very efficient at holding locks for the minimum amount of time
required. Locking records on the client for long periods of time
causes blocking and deadlocks (scenario--user runs code that locks
records, goes to lunch, leaving records locked). Another process
cannot even SEE the data if you are using the default READ COMMITTED
isolation level (see SQL Books Online for more info).

You should use other methods to control concurrency violations, such
as designing table schema to partition tables so that users don't
access the same record at the same time, using timestamps to detect
concurrency problems, or creating a column in the table that
increments each time a record is updated (you check this value in your
code prior to updating and increment during the update). If you care
about efficiency and network traffic, don't use DAO. Using ADPs will
provide no benefits in your situation--rewriting your DAO as ADO will
be less work. Also, don't use any kind of recordset to update data
unless you are trying to slow your application down. Use UPDATE
statements instead.

--Mary

On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@xxxxxxxxxxxxxx> wrote:

Thanks Mary, but is it possible to lock records on SQL server with DAO?
If not I will have to convert my .mdb into a project as I think ADO is only
possible if the Access client application is a project file (.adp extension).
I do not like to do this because I then have about 850 Queries that do not
work anymore! I would then need to convert all queries into stored procedures
and views - is that correct or is there a way around it?
Thanks.

Oliver

"Mary Chipman [MSFT]" wrote:

Let me give this a try, assuming I understand your scenario correctly.
You have an Access .mdb front-end that you wish to link
programmatically to a SQL Server database. If that is correct, then
you can create the link using a DAO.TableDef, not a recordset. You set
the properties of the TableDef, which include the connection string,
name, etc. The linked table is a Jet object, and DAO is always the
best choice when working with Jet objects. If you wish to create a
recordset based on SQL Server data, then use an ADO recordset. To
summarize: Jet=DAO, SQL Server=ADO.

--Mary

On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@xxxxxxxxxxxxxx> wrote:

Hi all,

I am a newbie to SQL server and I am trying to link via ADODB from an Access
2000 .mdb file in Visual Basic to SQL server but I receive an error during
compilation at the "Dim rs As ADODB.Recordset" statement already.
It works if I do the same from an Access project file.

I assume this is not possible and I need to connect via DAO.
Does this also mean that I do not have the option to lock records at all if
I work
with a .mdb file?

Please help - I am puzzled.
Thanks.

Oliver

.

Relevant Pages

  • Re: Need help with DAO and SQL Server locking issue
    ... I will forward your response to the client. ... > Absolutely nobody uses DAO to work with SQL Server, ... I'd advise moving code that uses recordsets ... >>Locks are being placed on tables inside of a transaction each time the ...
    (microsoft.public.sqlserver.odbc)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I think it will ameliorate the situation if you clean up the client as ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Best way to populate webpages from multiple tables
    ... tables within hash tables, custom classes etc. ... > Set a session expiring cookie at the client that stores the Customer ID. ... > send it to the client, instead store it in your session mgmt sql server - ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Best way to populate webpages from multiple tables
    ... tables within hash tables, custom classes etc. ... > Set a session expiring cookie at the client that stores the Customer ID. ... > send it to the client, instead store it in your session mgmt sql server - ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)