-
Ofcource... DAO in local MSAccess database is the best (and fastest) way to working with recordsets.
But there is one little moment - IMHO:
using ADO for msaccess - masochism 'cause all querries in msacces using JetMachine and DAO using jet machine. And I think it's NOT coincidence.
But when you want to connect to MSSQLServer...all my fellows recommends to use ADO for better data safe.
PS: there is some fastes ways in MSAccess
=========
dim db as database
set db = currentdb
db.execute "select * from table"
db.close
=========
is faster in "update, insert, delete and select" than
=========
docmd.runsql "select * from table"
=========
and a kind of mystery: dbOpenDynaset is faser than dbOpenSnapShot
-
This is what I found out too.
DB_OPEN_DYNASET is quite a lot faster, than Snapshot.
The only reason why I thought to change to ADO in MS-Access is because it would be a lot easier to make the application compatible both with MS-Access ans SQL server. For SQL Server DAO does not work at all, as far as I found out.
This was the reason why I started to investigate in ADo with Access. But the performance is so poor, we cannot do this.
Strange enough: Microsoft says, ADO is the model to be used in the future, also it should have a better performance than DAO (see http://www.microsoft.com/msj/0299/ado20/ado20.aspx)
I don't know, what I should believe
-
Are you still beliveing in Microsoft?
If you want to make an interface in MSAccess to a MSSQLServer, then you should use ADO only for single work with recordset (not in cycle).
I linked tables throgh the ODBC and worked with DAO<=>MSSQL nice and easy =)
If you're good in SQL, the better way is to use dynamic querryes with parameters:
===============
Dim Name as string
Dim SurName as string
db.execute "insert into TABLE1 select '" & Name & "' as Name, '" & SurName & "' as SurName"
===============
And it would be much faster than work with recordset.
There is another way - you can make a lots of Stored Procedures in MSSQL, and start them from ACCESS with ADO, it will not hang the client machine, it will hang Server on more than 30 connections =).
All of it depends on business logic of your project.
The main problem is to work ONLY with recordset, if the client does't have an ODBC-driver in a client machine, for examle...
-
5 minutes ago I've made a test
Table OrderNumber contains 50.000 records and linked from MSSQLServer 2000 though the ODBC, client is MSAccess'2000. As you see, there were tested DAO And ADODB.
DAO - 25 sec.
ADODB - 35 sec.
Dim DB As Database
Set DB = CurrentDb
Dim RSol As DAO.Recordset
'Dim RSol As ADOdb.Recordset
'Set RSol = New ADOdb.Recordset
SqlStr = " select * " & _
" from OrderLine " & _
" order by OrderNumber ASC, OrderType ASC, LineNumber ASC"
Set RSol = DB.OpenRecordset(SqlStr, dbOpenDynaset, dbSeeChanges, dbPessimistic)
'RSol.ActiveConnection = GetConnectionStringNow
'RSol.Source = SqlStr
'RSol.CursorType = adOpenDynamic
'RSol.LockType = adLockPessimistic
'RSol.Open
'RSol.MoveFirst
While Not RSol.EOF
RSol.MoveNext
Wend
RSol.Close
Set RSol = Nothing
DB.Close
if recordset creation will be:
Set RSol = DB.OpenRecordset(SqlStr, dbOpenDynaset)
then time in DAO will be 9 sec. =)
-
Hi, Veresk,
thanks for the test. This is exactly what I got in my tests. So we better stick with DAO as long as possible, hoping, they will not make it die...
-
But i've noticed one little problem: both DAO and ADODB do't support adLockPessimistic and automaticly switches to adLockOptimistic, that's bad, and i have to develop a special Lock Class to protect User's data from each other.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|