ODBC JET ADO MDB ADP ACCDB???
Jim Bennett
client/server apps using Access. From what I can tell Microsoft is reverting
back to linked tables in MDB (ACCDB) as opposed to using an ADP. Any comments?
Mary Chipman [MSFT]
with Access-SQL Server apps. ADPs will of course continue to be
supported, but the linked table design pattern gives more flexibility,
among other things.
--Mary
Jim Bennett
now use linked tables? Do I set the recordsource to an SQL string? It seems
like a step backwards since I planned on consolidating my data access code on
the SQL server. I realize that ADP's will be supported but I do not want to
be out-of-step with future plans since my app is in the early stages.
Douglas J. Steele
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Jim Bennett" <JimBe...@discussions.microsoft.com> wrote in message
news:7D6A9CCF-8875-437C...@microsoft.com...
Jim Bennett
a read only recordset. My forms are bound.
Douglas J. Steele
I used to think pass-through were always read-only, but apparently there
have been changes (it may even have been Mary who told me that). I haven't
tested, though, so you'll have to try it and see whether it works with
yours.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Jim Bennett" <JimBe...@discussions.microsoft.com> wrote in message
news:BC2587F9-A7F2-49C8...@microsoft.com...
david@epsomdotcomdotau
or too Stored Procs to make them look more like tables.
To be updatable, your Stored Proc has to look like an updatable
table, with a primary key, not like a Stored Proc. I believe that it
is possible.
Then you can link to it.
"Jim Bennett" <JimBe...@discussions.microsoft.com> wrote in message
news:7D6A9CCF-8875-437C...@microsoft.com...
Sylvain Lafontaine fill the blanks, no spam please
pass-through queries are read-only and you must pepper them with the NOLOCK
hint to limit locking issues with other users. Besides ADP (which offers
better support for forms and sub-forms linked to stored procedures but
nothing else), you can directly create your own ADO recordsets and bind them
to a form: http://support.microsoft.com/?kbid=281998
Besides a better support for stored procedures as the reading source for
bound forms, sub-forms, reports and sub-reports; ADP doesn't really offer
more in comparaison to a MDB file with linked tables. For example, you
don't have support for update, insert and delete commands, transaction
control or a very good performance over the WAN & VPN; so the current
recommendation from MS is to either roll back to a mdb file with linked
tables (if your needs are basic) or to move forward with .NET if you want
more sophistication.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23mK7hWp...@TK2MSFTNGP03.phx.gbl...
Jim Bennett
Now one more dilemma maybe you could shed some light on. Should development
be based on ADO or DAO? It seems like Access 2007's default is to use DAO. I
must admit I am still more fluent in DAO than ADO. Are there any advantages
or disadvantages to choosing one over the other?
Sylvain Lafontaine fill the blanks, no spam please
be supported only under DAO, not ADO. Outside of ADP, ADO is no longer
widely in use. It's main domain of application was classical ASP but now
that must ASP web sites have been replaced by ASP.NET (or other things) and
that ADP is hardly supported by MS, ADO is probably on a vanishing line of
course.
Beside VBA (mainly Access & ADP, Word, Excel), the main point of ADO was to
be used inside VBScript, Javascript, ASP and VB6. Now, VB6, ASP and DAP are
gone, that probably ADP will soon follow (maybe even in the next release of
Access) and that there are few people making some quick vbscript/javascript
codes or using it with Word and Excel; I would think that the future of ADO
doesn't look so bright.
DAO is not really object oriented and ADO was to replace it for the
COM/DCOM/COM+/ActiveX world. However, MS is now in the process of replacing
all (?) technologies based on the later world with .NET technologies; so
there is not so much interrogation about the future of ADO. DAO will take
longer to be replaced because the coupling between Access and DAO is more
tight then between Access and ADO and the replacement of *both* DAO and ADO
for VBA would have required a .NET version of Access and Office; something
that will not happen before some years.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Jim Bennett" <JimBe...@discussions.microsoft.com> wrote in message
news:4692DD40-BD76-4802...@microsoft.com...
Mary Chipman [MSFT]
objects (TableDefs, QueryDefs, etc.). Do not use DAO when doing data
access against a SQL Server (i.e, open a recordset). In that case, use
ADO. However, data access through the programming layer is never a
good idea. It's better to use direct SQL statements through bound
forms/linked tables, pass-through queries or stored procedure
invocations.
--Mary
On Mon, 15 Jan 2007 06:25:01 -0800, Jim Bennett
Mary Chipman [MSFT]
are sending a T-SQL statement directly to SQL Server, which then
parses and executes it. There is no processing of the command until it
hits the server. SQL Server then returns a result set to the client.
For stored procedures you send "EXEC sprocname". SQLS finds the sproc
and returns either a result set or a done-in-proc message (aka records
affected) or the value of an output parameter. Calling a stored
procedure from a pass-through query does not hold any locks on the
server and forms based on pass-through queries are always read-only.
One common design pattern is to use a stored procedure to populate a
read-only form for users to browse. To edit a record, load it into
another form based on a SELECT statement that returns a single record.
--Mary
david@epsomdotcomdotau
'optimisation' lets DAO transactions work with multiple threads,
which block each other.
That means your complex transactions can't be based on Jet
queries, so for me the choice between ADO and DAO is almost
a moot point. You have to put all your stored procedures inside
the SQL Server database, not inside the Jet database. DAO
makes it easier to work with stored procedures inside the Jet
database, but you can't do that, so who cares? You're left
with code that is equally difficult - or equally trivial - regardless
of your connection method.
(david)
"Jim Bennett" <JimBe...@discussions.microsoft.com> wrote in message
news:4692DD40-BD76-4802...@microsoft.com...
Mary Chipman [MSFT]
surprised it doesn't work. Rule of thumb: use DAO with Jet objects,
ADO for programmatic data access to SQLS (Recordsets, etc), DAO to
programmatically work with QueryDef objects to manipulate or call
stored procedures via passthrough queries. You can also use DAO
against TableDef objects to dynamically link to SQLS tables or views.
--Mary
Sylvain Lafontaine fill the blanks, no spam please
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D2/S5A2BD.asp ?
Another possible solution could be using the ODBCDirect workspace instead of
the default JET workspace.
At this rate of going back into the past from MS; I won'b be surprised if
next year, they start suggesting us to use again RDS and RDO.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
<david@epsomdotcomdotau> wrote in message
news:utCFXp6O...@TK2MSFTNGP03.phx.gbl...
david@epsomdotcomdotau
work with SQL Server databases?
I'll give you that DAO does not work with SQL Server
databases, but I'm not ready to accept that there was
an intention there.
Because firstly, DAO 3.5 did work with SQL Server
databases,
And secondly, intentionally breaking DAO would be
a big step.
It could be true, but I'll stick to the 'misguided optimisation'
theory until I see a smoking gun.
(david)
"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
news:ail1r293mpbqpa6v5...@4ax.com...
david@epsomdotcomdotau
> > with multiple threads,
I don't know how that crept in: I knew at the time of writing
that I meant
> > with multiple connections.
> using the ODBCDirect workspace instead of
Yes, my point exactly: since you can't use DAO transactions,
you can't use mixed local and SS tables, so it doesn't matter
which technology you use for connections.
(david)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:efgl4kA...@TK2MSFTNGP06.phx.gbl...
Sylvain Lafontaine fill the blanks, no spam please
is exactly about DAO and multiple connections.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
<david@epsomdotcomdotau> wrote in message
news:elZ$VECPHH...@TK2MSFTNGP03.phx.gbl...
david@epsomdotcomdotau
It's talking about the inverse of the (newer) problem I was talking about.
The IsolateODBCTrans Property was designed to prevent multiple
workspaces using the same connection.
The existing problem with DAO 3.6 is that a single Jet workspace
will use multiple connections.
I've never used that property, and I guess that when I have the
chance I'll try it out. It is certainly possible that a property designed
to prevent sharing connections might also limit the number of
connections in a workspace.
I'll also have a look at the 2003 help if I get a chance. In the A97
help, the property only applies to a Jet workspace, the example
(given at the URL previously quoted) uses a Jet workspace, but
the /comment/ in the example claims that an ODBCdirect workspace
is in use. :~)
(david)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23dKFIuE...@TK2MSFTNGP05.phx.gbl...
Sylvain Lafontaine fill the blanks, no spam please
affinity to only one processor will solve the problem; see:
http://support.microsoft.com/?id=178650 .
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
<david@epsomdotcomdotau> wrote in message
news:eOGSgU6P...@TK2MSFTNGP02.phx.gbl...
david@epsomdotcomdotau
problem. Jet 3.6 starts multiple connections inside a transaction:
they block each other. You can see it happening as you watch
the connections to your server.
(david)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:e$nkQGFQH...@TK2MSFTNGP05.phx.gbl...
Mary Chipman [MSFT]
>work with SQL Server databases?
Yes, that is correct. DAO was designed specifically for the Jet
engine. I don't remember the exact date, but we're talking about the
Access 2.0 timeframe, which is well before SQL Server matured and
became the widely-used RDBMS that it is today. Later API's, such as
ODBCDirect and then ADO, were created to overcome DAO's programmatic
limitations when working with engines other than Jet.
That doesn't mean that you can't use it with SQL Server, but it was
designed for, and intended to be used with, Jet.
--Mary
david@epsomdotcomdotau
Access 1.0 was intended to work with - Access 1.0.
Access 2.0 was intended to work with Access 2.0
and ODBC.
DAO 3.0 was intended to work with Jet 3.0 and ODBC, but didn't.
DAO 3.5 was intended to work with Jet 3.5 and ODBC, and did.
DAO 3.6 was intended to work with Jet 3.6 and ODBC, and didn't.
The failure with Jet 3.6 wasn't their fault: WinXP service pack 2 was
still including fixes to the underlying database engine, the network
redirector. The SQL Server failure is because of problems with the
way locking is done. There is no indication that the locking changes
had anything to do with Jet ISAM: they appear to be misguided
optimisations of the ODBC connections, to 'take best advantage'
of the new features of SQL Server, including the new transaction
levels. That is, it appears that the changes to Jet ODBC were made
because Jet ODBC was intended to work with SQL Server.
(david)
"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
news:7ui1s219f7o738gbj...@4ax.com...
Mary Chipman [MSFT]
the section on DAO code in this whitepaper helpful:
http://msdn2.microsoft.com/en-us/library/bb188204.aspx
--Mary
david@epsomdotcomdotau
and used my words carefully.
Access was designed to work with heterogenous data. One of the
data sources Access 2.0 was designed to work with was ODBC.
Subsequently, DAO abstracted out an OLE data interface.
I've just reviewed some of the Access 2.0 documentation. It is
clear that Access 2.0 was designed to work with ODBC.
I've just reviewed some of the Access 95 documentation. It is
clear that DAO was designed to work with ODBC data,
as was Access 95.
In particular, DAO was designed to work with SQL Server
and Oracle.
Having read this material, it is clear that DAO was designed to
work with ODBC. To say otherwise is to re-write history.
(david)
"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
news:c24cs2p6he37aftj0...@4ax.com...
david@epsomdotcomdotau
and used my words carefully.
Access was designed to work with heterogenous data. One of the
data sources Access 2.0 was designed to work with was ODBC.
Subsequently, DAO abstracted out an OLE data interface.
The DAO interface was designed to work with heterogenous
data, in exactly the same way that the ADO interface was later
designed to work with heterogenous data.
I've just reviewed some of the Access 2.0 documentation. It is
clear that Access 2.0 was designed to work with ODBC.
I've just reviewed some of the Access 95 documentation. It is
clear that DAO was designed to work with ODBC data,
as was Access 95.
In particular, DAO was designed to work with SQL Server
and Oracle.
Having read this material, it is clear that DAO was designed to
work with ODBC. To say otherwise is to re-write history.
Having read this material, it is clear that DAO was designed to
work with SQL Server and other data sources in exactly the
same way that ADO was later designed to work with SQL Server
and other data sources. To say otherwise is to re-write history.
MS had a choice about re-writing DAO for OLEDB or creating
a replacement, and they choose to create a replacement. I respect
the fact that re-writing something often breaks it.
Having created a replacement, using current technology, they
did not wish to spend a lot of duplicate effort updating DAO.
So DAO was depreciated.
I am aware that for a number of years, DAO was depreciated.
I am aware that for a number of years, ADO was the preferred
method for SQL Server access.
I am aware that DAO 3.6 against SQL Server 2K+ is badly
broken in some respects.
ADO was a replacement technology for RDO, ODBCapi and OLEDB.
DAO was also a replacement technology for RDO and ODBCapi, so
ADO was also a replacement technology for DAO. The design criteria
was effectively the same. The difference was not what ADO included:
it was what ADO left out.
(david)
<david@epsomdotcomdotau> wrote in message
news:ORf%23U8LSH...@TK2MSFTNGP02.phx.gbl...
> I am aware of the distinctions between Access, DAO, and Jet,
> and used my words carefully.
>
> "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
Aaron Kempf
neither should MDB
move to SQL Server and then .NET
"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
news:c24cs2p6he37aftj0...@4ax.com...
Aaron Kempf
do you know how hard it is to bind a form to a sproc in MDB?
it takes about 100 lines of code
ADP is 100 times easier
if you're already usign SQL Server; it's a no-brainer
if you're not using SQL Server; ADP is still a no-brainer
"Aaron Kempf" <ake...@dol.wa.gov> wrote in message
news:%23HWtq$4hHHA...@TK2MSFTNGP04.phx.gbl...