Re: Access and processor usage

Hi, Rich.

If you are pulling data from a sql server (MS Sql Server, Oracle, ...)
and you are using an ODBC connection, that would be your bottle neck.

Have you ever received the "There is not enough disk space or memory to undo
the changes" warning message when _not_ using Jet tables? Isn't this a
Jet-specific message?

The fix is to use ADO (com based ADO) to pull your data (much more
bandwidth than ODBC).

Years ago, we tested linked (ODBC) Oracle tables with DSN's and ADO with
DSN-less connections, and the speeds were similar. We didn't get markedly
"increased bandwidth" using ADO. And addressing the bandwidth (network
throughput) isn't going to solve Joel's warning message that the action
query can't be undone because there's not enough disk space or memory on his
workstation. Even if you speed up the network, thereby increasing the
bandwidth, where are those bytes going? Either to memory or to disk, which
Access is already complaining there isn't enough of to undo the changes when
<CTRL><Z> is pressed.

If Access is your backend, then Access is the
bottleneck - need to step up to Sql Server.

He can fix the problem with a change of his Jet Engine settings, instead of
replacing the problem with an expensive and time-consuming upgrade to SQL
Server.

Access
has a 1 gig data limit (more like a 500 meg limit).

Access 95 and 97 can hold 1 GB of data, while Access 2000 and newer can hold
2 GB. With the horsepower Joel has, it's doubtful he's using a version of
Access older than Access 2000.

If you are working
with genuinely big data you need to step up to server based tools like
.Net and sql server.

If the data fits into a 2 GB database file, it isn't "genuinely big" yet.
Tens or hundreds of terabytes is "genuinely big." 2 GB ain't much, but if
the database is pushing that size limit, then it's time to migrate the data
to a stronger and bigger database engine. Several of them are free, such as
SQL Server 2005 Express, Oracle 10g Express, and IBM DB2 Express-C. The
first two hold up to 4 GB of data, and the last isn't limited by data file
size.

ADO.Net has way more bandwidth than com based ADO.

I didn't know that. What database connection technology can ADO.Net use
that's superior to the database connection technologies ADO is limited to?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"Rich P" <rpng123@xxxxxxx> wrote in message
news:46142592$0$501$815e3792@xxxxxxxxxxxxxxxxx
If you are pulling data from a sql server (MS Sql Server, Oracle, ...)
and you are using an ODBC connection, that would be your bottle neck.
The fix is to use ADO (com based ADO) to pull your data (much more
bandwidth than ODBC). If Access is your backend, then Access is the
bottleneck - need to step up to Sql Server.

If neither of the above are your scenario, and you really are pulling in
hundreds of megs of data, then Access is still the bottleneck. Access
has a 1 gig data limit (more like a 500 meg limit). If you are working
with genuinely big data you need to step up to server based tools like
.Net and sql server. ADO.Net has way more bandwidth than com based ADO.

BTW, core2Duo is sweet, heh? I just upgraded my workstation to one last
month - the shop that upgraded me said that you can maximize its full
potential by using 4 gigs of memory.

Rich

*** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • Re: Strange Problems with ODBC connection to SQL Server
    ... When accessing Jet data, use DAO. ... This way you are not exposing connection information in clear ... >accesses an SQL Server 2000 repository via ODBC data source. ...
    (microsoft.public.data.odbc)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()) er
    ... I understand that you encountered the ODBC error message on your ecommerce ... TCP/IP protocol to your SQL Server instance by calling conn.Open, ... SQL Server instance and see if there is a connection reset when this issue ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.connect)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()
    ... remote connection. ... should I just check named pipes only or both TCP/IP and ... I understand that you encountered the ODBC error message on your ecommerce ... TCP/IP protocol to your SQL Server instance by calling conn.Open, ...
    (microsoft.public.sqlserver.connect)
  • Re: Performance of ODBC
    ... ODBC / MDB is a waste of time. ... Usiing Access front-end to connection a back end through ... the database via ODBC with the Jet database engine. ... that Access is the front end, and the SQL server is the back end. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Using SQL Server Express on Shared PC
    ... you don't need to install SSMS or VS on the client system. ... using ODBC you're making the job of getting connected more difficult. ... ODBC requires either a REGISTERED DSN, a file-based DSN or a "DSN-less" connection. ... You also need to configure the Network-shared SQL Server service to permit access by whatever credentials you have chosen. ...
    (microsoft.public.sqlserver.connect)