Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sql Server And ADP (2002)

    Hi guys, im really interested in writing and access project with an sql back end, but there is a few things that i need to clear up and put into leymans terms for myself before i get started as im want to fully understand them
    if i use a connection string and the currentproject.openconnection command, the tables and SP's appear in the project. ok so far so good. so if i type into a table directly will this update the server, or is this just a kind of reflection of what it on the server at that time. If this is so, when i run the views and stored proceedures, do they use this data in the front end or is it run from the server.
    I probably sound really crap at this but it is essential that i understand it properly. Can anybody give me some real simplified explanations of how projects work with sql regarding forms and running views , SP's etc cos i dont want to get lost along the way here and wonder what happened.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sql Server And ADP (2002)

    ADP projects in Access connect directly to a SQL Server database using the OLEDB database interface, so if you write data to a table when viewing the table, it's updated at that point. The same is true of forms that are bound to a table or a view. Stored procedures that return data are frequently not updateable, and so are usually useful only for display or reporting purposes.

    Now some questions:
    1. <LI>Why are you choosing to use ADPs rather than the MDB format linking with ODBC? ADPs will continue to be supported in later versions (Access 12 and up), but are not being enhanced. One of the reasons is that development using ADPs usually takes 2 to 3 times the effort of an MDB, and developers have found that in most cases you can get acceptable performance with ODBC linked tables.
      <LI>Presuming you have a good reason for using an ADP, are you using the full version of SQL Server or the MSDE for your back-end? The full version of SQL Server includes the Enterprise Manager, which provides some very useful tools for designing and admininistering a SQL Server database - but of course the MSDE is included with Access.
      <LI>How proficient are you with SQL Server and the construction of views and stored procedures? The use of Sprocs in particular requires that you know a good deal about T-SQL which is the SQL Server version of SQL, and is quite different from the Access version.
    Based on the answers to those questions, we can try to give you some useful feedback.
    Wendell

  3. #3
    New Lounger
    Join Date
    Sep 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sql Server And ADP (2002)

    It is going to be an ongoing project so im going to be learning as i go along ...which i dont mind as its my hooby so i enjoy acomplishing new things.
    We want to use ADP with ADO becuase the system that we have is growing so big and the functionality requirements are so much of the forms that speed is becoming a big issue as datasets often exceed 1GB and users are 10+

    We wrote a version using odbc before and it was way too slow for what we need. This was unfortunate. Our system processes a lot of data every day and performance is the key issue. Our system is a comprehensive accounts and consignment tracking system which is used on a trading floor where things need to be done very quick. We feel ADO is the way that this needs to proceed.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sql Server And ADP (2002)

    In this situation you may be on the correct path, but about the only situation where we've found you need an ADP (or a Visual Basic front-end) is where you are doing heads-down data entry in large volumn. We have large SQL Server databases with as many as 200 users using an Access MDB as the front-end, and routinely get subsecond response times. What we do in situations like you are looking at is focus on the performance problems once we've built the front-end. Also note that tests we've done comparing ADO with DAO and ODBC doesn't always show ADO to be the winner. Generally you get about a 6% improvement, but on certain things it is noticeably slower. Not what you would expect.

    There are a number of tricks and techniques to optimizing performance that we use in the performance tuning process. One is to deploy the front-end to the workstation. Another is to link to SQL views. Of course the biggest is to make sure you are only passing a small amount of data to the workstation. Access will do some optimization and actually perform queries in SQL Server if they aren't very complex. When you get into complex queries, then Jet tries to do the joins and the rest all on the workstation, which is bad news if you have large datasets. In those cases we use either pass-through or ODBC direct queries so SQL Server does the work for us, and gives us just the data we were after. In a system we recently installed, we are getting subsecond response on a complex form with more than a dozen subforms, where several tables have 100K or more rows, and two actually have around 3M rows. Hope this is useful.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •