Re: SQL conversion for back end?

From: el zorro (anonymous_at_discussions.microsoft.com)
Date: 10/25/04 

Date: Mon, 25 Oct 2004 12:58:02 -0700

Thanks, Brett. I appreciate your response!
>-----Original Message-----
>Sometimes you don't get better performance from SQL
Server. It is not
>the panacea for performance. A badly designed Access
database will be
>a badly designed SQL one and may even run slower if its
really bad.
>
>---------- About comparing Access to SQL Server -------- 

----
>
>SQL Server has great security, great backups and 
management of large
>data files but may not be the cure-all people expect of 
it   Here are
>some of the considerations courtesy of "Access 
Developers Guide to SQL
>Server", SAMS Publishing, ISBN 0-672-319944-6
>
>- Users:  Access will work well with 15-20 *concurrent* 
users (meaning
>you might have 50 workstations but only 20 users are 
generally logged
>in at any one time)
>
>- Database SIze:  Access is limited to 2Gb from A2000, 
1Gb for
>versions before that.  This is a *huge* amount of data, 
so not many
>upsizing decisions are made because of size.
>
>- Performance: There is an assumption that, no matter 
how slow the
>Access database is, upsizing will magically speed things 
up.  Often
>just the opposite is true, and upsizing results in even 
slower
>performance.   Performance has as much to do with 
database design and
>application design as it does with the platform.
>
>- Transaction Logging:  Because the Jet databse engine 
runsd on each
>user's machine, it cannot keep a comprehensive record of 
all the data
>changes made by multiple users.  SQL Server on the other 
hand,
>processes all the data changes centrally, so it 
maintains a complete
>up-to-date log of everything that has happened in the 
Transaction Log.
>
>- Data Integrity  Users can corrupt an access database 
by turning
>their system off while in the middle of a critical 
operation or by
>simply opening the database in MS Word, which 
irretrievably corrupts a
>database.  On the other hand it is very difficult to 
corrupt a SQL
>Server database.
>
>- Security  If security is a big issue its an important 
reason to
>upsize to SQL Server.  With it you can take advantage of 
the more
>robust and even more convenient security through its 
strong
>integration with Windows security.
>
>- Administration  The SQL Server Enterprise Manager 
makes it possible
>to centrally administer local and remote servers and you 
can add
>linked servers for distributed querying.  Although Jet 
database is
>easier to administer, that is because it severely limits 
your
>administrative options. 
>
>- Features: There are some Access functions that SQL 
Server cannot
>support and are ignored in upsizing
>   - Access defined field properties such as input 
masks, formats,
>captions and descriptions are ignored.
>   - If a function is used in a field validation rule 
that has no SQL
>Server equivalent, the validation is ignored
>   - Nested queries are not supported in SQL Server
>   - If a function is used in a field's Default 
Property, it will not
>be upsized.
>   - The Allow Zero Length property is ignored.
>   - All Access security is ignored.
>   - Default Value expressions have to be stripped 
before upsiing or
>the whole field is ignored.
>   - Lookup fields results in tables are ignored.  Only 
the underlying
>ID number is retained.
>   - If a field in a table has a Yes (No Duplicates) 
index AND the
>Required Property is set to True AND the field contains 
more than one
>Null value, the data from the table cannot be upsized, 
only the
>structure.
>
>---------------- compiled by Brett Collings -------------
----
>
>
>On Thu, 21 Oct 2004 13:32:20 -0700, "el zorro"
><anonymous@discussions.microsoft.com> wrote:
>
>>I have an Access 2000 program that is used by about 10 
>>people, out of which maybe 5 use it concurrently. The 
>>Front End is installed on each workstation, linked to a 
>>BE on a server.
>>
>>The issue is that it runs slower than molasses in this 
>>environment. Whenever I need to do design work on the 
>>Front End, I use a test BE on my local drive and it's 
>>much perkier. SO I asked our Tech Services guy if maybe 
>>he could move the BE to a faster server, or look at the 
>>network conection.
>>
>>His response was that there is plenty of processing 
power 
>>and disk storage where the BE sits right now, and that 
>>the problem is Access itself. He says its clunky, it 
uses 
>>too much computing power to compile or something. He 
>>advised me to convert the back end to SQL (I think he 
>>said), and use Access for the front end. He thought 
that 
>>would be great.
>>
>>I'm not convinced, but does anyone understand what the 
>>heck he's talking about? Can I convert or upload the BE 
>>to a more robust format via Access, then use Access for 
>>the front end? If so, should I?  Any thoughts on this 
>>will be appreciated!
>
>Cheers,
>Brett
>
>----------------------------------------------
>Be adventurous, be bold, be careful, be a star
>----------------------------------------------
>Brett Collings
>Business & Systems Analyst
>Management Information Systems Developer
>.
>

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
Loading