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

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
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@.programmer.com> 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
|||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@.programmer.com> wrote:
>
|||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@.programmer.com> wrote:
[vbcol=seagreen]
>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:
|||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
"Mary Chipman [MSFT]" wrote:

> 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@.programmer.com> wrote:
>
|||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
On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>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
>"Mary Chipman [MSFT]" wrote:
|||Thanks Mary, in the meantime I found a good link to an old documentation
about the use of ODBCDirect,
http://msdn.microsoft.com/archive/de...l/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
"Mary Chipman [MSFT]" wrote:

> 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
> On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
>