Is it possible to link via ADODB from an Access 2K .mdb file? -- sql server clients

Mary Chipman [MSFT]
2/3/2006 5:25:24 PM

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

[quoted text, click to view]

Oliver
2/4/2006 10:50:11 AM

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

[quoted text, click to view]

Mary Chipman [MSFT]
2/7/2006 9:46:27 AM

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

[quoted text, click to view]

Oliver
2/8/2006 1:13:27 AM

Hi Mary, thanks for the tips.
I just thought that it is too much work to convert all the DAO code and all
of the 600 queries that did not convert with the upsizing wizard. The views
are mostly not updateable after upsizing - it seems I will have to rewrite
the whole system and I think Microsoft should have left it to us programmers
to decide if we want to rewrite it all by just allowing record locking in DAO
ODBC links. I spent a whole day yesterday trying out if DAO allows record
locks but it does not (they could at least have mentioned this in the help
system).
After having tried this out I think you are right - there is not other way
than to convert all code into ADO in one go. You mentioned that I should use
UPDATEs instead of recordset updates - do you mean I should use ADO commands
executed from visual basic or should I write update procedures on the server
and call those stored procedures from the visual basic?
Thanks.

Oliver

[quoted text, click to view]

Mary Chipman [MSFT]
2/14/2006 12:12:59 PM

I think the reason you may have had trouble discovering how DAO works
with SQL Server in the help files is that there is an assumption that
you will use it only with Jet. It is not intended to work with SQL
Server, so nobody thought to document it. However, you can still use
DAO to execute pass-through queries, which are quite efficient. You
can use existing QueryDef objects and set the .SQL property in DAO
code to a SQL statement or to execute a stored procedure. Or you can
create dynamic pass-through queries that are not persisted in the mdb.
The syntax you use in the .SQL property is T-SQL, not Access SQL. The
reason they are called pass-through queries is that the SQL is not
parsed by Access--it is sent directly to the server. You can also use
ADO commands to execute SQL statements or parameterized stored
procedures. HTH,

--Mary

[quoted text, click to view]

Oliver
2/15/2006 2:15:26 AM

Thanks Mary, in the meantime I found a good link to an old documentation
about the use of ODBCDirect,
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/web/001.asp.
This gives me even the option of pessimistic record locking (I need this
sometimes). I already tried to convert everything into ADO but this is an
endless job with the amount of code and queries I have (I gave up!). Now I
can program new queries as stored procedures and views on the server but
still keep the old queries in Access functional. If a query is too slow I
just convert it as needed. This is a much better way of migration into SQL
server.

Oliver

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

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