473,605 Members | 2,089 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trouble with DAO "SEEK" in converting application to SQL Express back end.

Hello,

I have an application that I'm converting to Access 2003 and SQL Server 2005
Express. The application uses extensive use of DAO and the SEEK method on
indexes. I'm having an issue when the recordset opens a table. When I
write

Set rst = db.OpenRecordse t("MyTable",dbO penTable, dbReadOnly)

I get an error. I believe it's invalid operation or invalid parameter, I'm
not in front of the application at the moment, but will let you know if it's
important.

When I remove the dbOpenTable, it works but I can't use the SEEK method on
the index.

Any ideas?

Thanks!
Mar 30 '06 #1
59 7476
"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS
PART IN CAPS> wrote in news:bDUWf.2026 92$H%4.69991@pd 7tw2no:
I have an application that I'm converting to Access 2003 and SQL
Server 2005 Express. The application uses extensive use of DAO
and the SEEK method on indexes. I'm having an issue when the
recordset opens a table. When I write

Set rst = db.OpenRecordse t("MyTable",dbO penTable, dbReadOnly)

I get an error. I believe it's invalid operation or invalid
parameter, I'm not in front of the application at the moment, but
will let you know if it's important.

When I remove the dbOpenTable, it works but I can't use the SEEK
method on the index.


Is SEEK a Jet-only operation? Given that it's based on indexes, I'd
think that it would be.

I don't think there are very many situations at all where SEEK is
justified at all. Either filter your recordset or use .FindFirst.
The latter may very well be 10 times slower than SEEK, but that
would only matter if you're doing loops that require hundreds of
thousands of operations that reposition the current record pointer.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Mar 30 '06 #2
"Rico" wrote
When I remove the dbOpenTable, it works but
I can't use the SEEK method on the index.


You have never been able to use dbOpenTable or Seek on anything but native
Jet tables in the same MDB/MDE. As far as I know, that is well-documented.

My personal view is that use of SEEK most often implies a poor design --
there may be a few cases in which it is needful to open a dataset with a
large number of records, and then find the one you want (SEEK if you can
open as a Table, or FIND / FINDFIRST if opened as a Dynaset).

It is more efficient, especially if you are using an Access client with a
Server DB (as you are now), to include criteria in your Query or SQL
statement so that the extraction of the one record you want is performed at
the server (or no record if it does not exist). It is really amazing to
realize just how often we need only one record (if it exists) or none (if it
does not exist).

Larry Linson
Microsoft Access MVP
Mar 31 '06 #3
SEEK is a fast native Access/Jet method.

If you want the same performance from SQL Server 2005,
you are going to need to use SQL Server native methods.

However, there are alternatives. At this point, you have
the choice of using ADO methods, DAO methods, or Stored
Procedures.

I can't say that there is much to choose between them.
Make a personal decision, flip a coin, or ask a friend.

Then ask here for an example using your chosen technology.
You will have to give an example of what you are trying
to do, because there is no direct plug-in replacement.

(david)
"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:bDUWf.2026 92$H%4.69991@pd 7tw2no...
Hello,

I have an application that I'm converting to Access 2003 and SQL Server
2005 Express. The application uses extensive use of DAO and the SEEK
method on indexes. I'm having an issue when the recordset opens a table.
When I write

Set rst = db.OpenRecordse t("MyTable",dbO penTable, dbReadOnly)

I get an error. I believe it's invalid operation or invalid parameter,
I'm not in front of the application at the moment, but will let you know
if it's important.

When I remove the dbOpenTable, it works but I can't use the SEEK method on
the index.

Any ideas?

Thanks!

Mar 31 '06 #4
"david epsom dot com dot au" <david@epsomdot comdotau> wrote in
news:44******** *************** @lon-reader.news.tel stra.net:
SEEK is a fast native Access/Jet method.

If you want the same performance from SQL Server 2005,
you are going to need to use SQL Server native methods.

However, there are alternatives. At this point, you have
the choice of using ADO methods, DAO methods, or Stored
Procedures.

I can't say that there is much to choose between them.
Make a personal decision, flip a coin, or ask a friend.


From ADO help:

"Indexes on fields can greatly enhance the performance of the Recordset
object's Find method and Sort and Filter properties. You can create an
internal index for a Field object by setting its dynamic Optimize
property."

--
Lyle Fairfield
Mar 31 '06 #5
"Larry Linson" <bo*****@localh ost.not> wrote in
news:Vr_Wf.1397 0$VL2.5902@trnd dc04:
You have never been able to use dbOpenTable or Seek on anything
but native Jet tables in the same MDB/MDE. As far as I know, that
is well-documented.


Er, you mean in the same MDB as referenced by the database object
with which you created the recordset.

It's perfectly easy to open a different database that has the tables
in it, assign it to a db variable, and then open a table-type
recordset on it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Mar 31 '06 #6
Seek is the fastest method to find a record in an MDB database using DAO,
and generates the least network traffic. FindFirst is one of the slowest.

Steven

"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS
PART IN CAPS> wrote in news:bDUWf.2026 92$H%4.69991@pd 7tw2no:
I have an application that I'm converting to Access 2003 and SQL
Server 2005 Express. The application uses extensive use of DAO
and the SEEK method on indexes. I'm having an issue when the
recordset opens a table. When I write

Set rst = db.OpenRecordse t("MyTable",dbO penTable, dbReadOnly)

I get an error. I believe it's invalid operation or invalid
parameter, I'm not in front of the application at the moment, but
will let you know if it's important.

When I remove the dbOpenTable, it works but I can't use the SEEK
method on the index.


Is SEEK a Jet-only operation? Given that it's based on indexes, I'd
think that it would be.

I don't think there are very many situations at all where SEEK is
justified at all. Either filter your recordset or use .FindFirst.
The latter may very well be 10 times slower than SEEK, but that
would only matter if you're doing loops that require hundreds of
thousands of operations that reposition the current record pointer.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Apr 1 '06 #7
"Steve" <st***@nospam.n et> wrote in news:Fk******** *******@fe10.lg a:
Seek is the fastest method to find a record in an MDB database
using DAO, and generates the least network traffic. FindFirst is
one of the slowest.


But if you're trying to find only one record, you'd use a WHERE
clause, and it will be faster than either SEEK or FindFirst.

If you're jumping around a recordset (which is the only reason you'd
ever use SEEK), then the difference between SEEK and FindFirst will
be obvious to an end user only if you're moving to thousands of
records.

The circumstances where that is a justifiable design are very few.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 2 '06 #8
See http://support.microsoft.com/kb/143237/en-us for an example provided by
Microsoft when seek is the most efficient method.

Steven

"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
"Steve" <st***@nospam.n et> wrote in news:Fk******** *******@fe10.lg a:
Seek is the fastest method to find a record in an MDB database
using DAO, and generates the least network traffic. FindFirst is
one of the slowest.


But if you're trying to find only one record, you'd use a WHERE
clause, and it will be faster than either SEEK or FindFirst.

If you're jumping around a recordset (which is the only reason you'd
ever use SEEK), then the difference between SEEK and FindFirst will
be obvious to an end user only if you're moving to thousands of
records.

The circumstances where that is a justifiable design are very few.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Apr 3 '06 #9
Yes seek is fast.

So is a BMW M series.

But more often than not a Toyota Corlloa will do the trick without the
overhead of the BMW.

I have used seek extensively. But the only place that it pays its way
is when I want to do multiple (as in thousands or more) finds in the
same recordset. As seek seems not to be supported in MS-SQL I have
abandoned it pretty much all together.

Most of MS's help and kb articles are just crap ...

(my evil twin Kyle adds, "Except when they agree with him in which case
he quotes them profusely!")

Apr 3 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1366
by: WC Justice | last post by:
I have a web site that uses an Access 2000 back end. Most of the recordsets use the rsName.open "qryName"... method. So far, in converting to a SQL Server back end, I have been changing to the rsName.open "SQL Statement"... method. Is there a way to create a recordset by calling a stored procedure or view?
2
251
by: Matt Hawley | last post by:
I'm attempting to run a .NET windows application, but continuously get the following error when starting it up: Application has generated an exception that could not be handled. Process id="0xe04 (3588), Thread id=0xe14 (3604) Does anyone know what the problem could be? The correct .NET framework is installed on the machine. Matt Hawley, MCAD .NET http://www.eworldui.net
5
2535
by: Sonu | last post by:
Hello everyone and thanks in advance. I have a multilingual application which has been built in MFC VC++ 6.0 (non-Unicode). It support English German Hungarian so far, which has been fine. But now I need it to work on Russian computers and I realized that the application should be converted to Unicode to work in Russian. I am totally new to .NET so I'm not sure of this, but I read somewhere that if converted my apllication to .NET...
12
1713
by: Mindy | last post by:
Hey, I want to build a database which has an in front end application and back end tables. I am new to access, and all my knowledge is from Access for Dummies. Could any one give me a clue where to find information regarding this topic? should I use macro to set up in front end application or I could get it down by the GUI. I have a big book "Running Access for Window 95" by hands, but not sure which chapter will solve my problem. Any...
0
1085
by: Jon Gabel | last post by:
I have a WindowsForms application that communicates via TCP/IP or SNMP (using IP*Works components) with various devices on my local network. I would like to duplicate the same functionality in a WebForms application. Instead of running a native Windows application, the user would use a browser linked via http or https to a server running the equivalent ASP.NET app. How can I get access to the locally networked devices if I use this...
1
1575
by: JLC | last post by:
I am having some trouble displaying the following. I want to display the startdatetime and enddatetime in a particular table using an offset. In that table is also a timezoneid that would correspond with a time zone offset in another table. I can figure out the joins to join the two tables to retrieve the corresponding offset, but how do I use that offset to display the correct startdatetime and enddatetime in my select statement? ...
1
1863
by: kphip123 | last post by:
Hi! I recently converted an application from VS2003 to VS2005. The application consists of a main 'unmanaged C++ executable' which calls a managed C++ .dll. The .dll basically launches a separate form. The application use to work fine in VS2003. Now that I have converted to VS2005 the main executable is working but an exception gets thrown when it trys to call the .dll (basically the new separate form
0
1119
by: Glenn Spiller | last post by:
I am trying to pass a JAVA url that needs variables from my application express app. Anybody know how to pass these variables. Even if there is a way to pass it using PL/SQL I could also use that.
0
8009
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7938
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8430
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8427
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8078
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8298
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6752
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5452
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2441
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.