Need a few pointers, please. DAO -> ADO, many others.
Steve
though. Can someone help me? I have a database application that I
wrote that works, but my first problem is it was written using DAO.
I'd like to change that to ADO. But I know my application is full of
problems, holes, and inefficient methods. Let alone the fact that it
was not written too intelligently.
My first question is: Is it appropriate to ask someone to take a look
at my application to just give me ideas and pointers? Is there
someone out there who has a bit of time who's pretty well experienced
would be willing to help me out? My code is < 800 lines.
A specific question, though: I wrote the program to NOT use databound
controls, because I wanted the flexibility that comes with Recordsets
and traversing and editing data manually. Can someone tell me how to
replace the following code with an ADO connection?
Option Explicit
Public dbs As DAO.Database
Public rsCustomer As DAO.Recordset
Public rsOrders As DAO.Recordset
Private Sub Form_Load()
Set dbs = OpenDatabase("c:\mydatabase.mdb")
Set rsCustomer = dbs.OpenRecordset("Customer", dbOpenDynaset)
Set rsOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
End Sub
I have more questions that can be possibly asked here, but if someone
would be willing to help me out, I'd definitely put you on my
Christmas card list! :) Please reply here or better yet, email me
at: ocs...@REMOVETHISyahoo.com.
Thanks a bunch and I'd appreciate any help at all. Happy
Thanksgiving!
Steve
Larry Linson
DAO to ADO_?
For an Access/ Jet database, DAO is, despite Microsoft's continued promotion
of ADO, better because it is the native language of Jet, is thus faster for
Jet, and is more complete for Jet than the combination of ADO and ADOX.
Microsoft states that Jet, DAO, and ADO/ADOX support for Jet are in
"maintenance mode" -- that is, there will be no new development on any of
them, but defects will still be considered for correction. That means, of
course, that their "relative status" is not going to change.
The "conventional wisdom" is that ADO, using ADODB which is based on OLE DB,
is faster if you are directly accessing MS SQL Server or other server
databases which have a dedicated ADO data provider. However, I have seen
anecdotal evidence from people whose skills I believe to be good and whose
word I trust that indicates that there are at least some situations where
using Jet and ODBC drivers has turned out to give better performance that
comparable ADO. I would not contend that this is universally the case, but
it has convinced me that, even in this area, DAO and ODBC can still give a
good account of themselves.
In that regard, I have personally used DAO with an Access client, Intersolv
ODBC drivers, and an Informix database that supported nearly two hundred
users, only a few on fast local networks, some on rather slow WAN
connections, and some thousands of miles away on a WAN that shared a T-1
leased telephone line connection, with satisfactory performance. Others,
again people whose skills I believe to be good and whose word I trust, have
also reported using DAO/ODBC/Server DBs to support user audiences in the low
hundreds, with Access and VB clients.
The final "nail in ADO's coffin", in my estimation, is that its .NET
successor, ADO.NET, shares only part of the name, is built on a different
model (not programmed using the same methods as "original ADO") and is not
even based on the formerly-highly-touted OLE DB.
That said, ADO is useful in .ASP pages, and it is useful if you need to use
XML tables, which it supports.
"Steve" <ocs...@yahoo.com> wrote in message
news:b5af4727.01112...@posting.google.com...
David Wimbush
a good reason. "Because it's the new way to do it" isn't good enough
now that ADO is being replaced by ADO.Net. DAO is about ten times
faster than ADO with Acess 95 or 97, but with Access 2000 ADO is
slightly faster. If you've made heavy use of DAO's features like
TableDefs and QueryDefs you may find it quite difficult to convert to
ADO. I recommend the Microsoft white paper "Migrating from DAO to
ADO", which you can find in your MSDN or online at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp
It takes just about everything you can do with DAO and shows how to do
it with ADO.
You wanted to know how to convert this code to ADO:
> Option Explicit
> Public dbs As DAO.Database
> Public rsCustomer As DAO.Recordset
> Public rsOrders As DAO.Recordset
>
> Private Sub Form_Load()
> Set dbs = OpenDatabase("c:\mydatabase.mdb")
> Set rsCustomer = dbs.OpenRecordset("Customer", dbOpenDynaset)
> Set rsOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
> End Sub
Incidentally, congratulations on avoiding bound controls. They're not
worth bothering with. Here's one way to do it:
modMain:
--------
Option Explicit
Public g_Con As ADODB.Connection
Private Sub Main()
Dim strConnect As String
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\mydatabase.mdb;" _
& "Persist Security Info=False"
Set g_Con = New ADODB.Connection
With g_Con
.ConnectionString = strConnect
.CursorLocation = adUseClient
.Open
End With
End Sub
Form:
-----
Option Explicit
Public m_rstCustomer As ADODB.Recordset
Public m_rstOrders As ADODB.Recordset
Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT * FROM Customer"
Set m_rstCustomer = New ADODB.Recordset
With m_rstCustomer
Set .ActiveConnection = g_Con
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Source = strSQL
.Open
End With
strSQL = "SELECT * FROM Orders"
Set m_rstOrders = New ADODB.Recordset
With m_rstOrders
Set .ActiveConnection = g_Con
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Source = strSQL
.Open
End With
End Sub
A bit of an explanation. The Connection object is basically the
Workspace and Database objects combined. For simplicity I tend to open
one at the start of the program and hang on to it until the end. This
is not the officially recommended method (connection pooling) because
it is not scalable but it works fine for up to about 50 people which
is getting near Access's limit anyway.
Val Mazur
Of course DAO is native to Access and oriented to use with
it, but ADO is not ten times slowly that DAO with Access
95/97. It depends on how you implemented ADO in
application. There are a lot of different options and ways
to improve performance in ADO, and when you convert
application from DAO to ADO you should think about another
implementation way to achieve good performance. Not just
to replace one statement from DAO with analog in ADO. I
also can give examples whet DAO will be slowly than ADO
with Access 95/97.
Val
>.
>