Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: SQL server - ODBC or Access database Projects??

    Hi. I'm in the process of upsizing an Access database. Currrently it's just a front end on 10 cleint pcs linked with the backend on a server. Problem is it's a bit slow (longest form takes 10 seconds to load).

    I have setup a SQL 2008 server, but now I'm not sure which way to use it. If i connect to it using Access Database Projects, it is much easier as I'm very familiar with access and won't need to change all the SQL code to TSQL.

    WOuld this give me the same advantage as if I linked all the tables via ODBC and rewrote the SQL to pass through queries??

    If not, will I see any speed differences? or will all the backend tables still be downloaded and then processed? (which is what's causing the speed issue).

    any help or other alternative solutions is appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've always favored Access Data Projects, but I've pretty much given up trying to do development in the latest release (Access 2007), because it sucks so incredibly bad.
    For instance, good luck figuring out how to create an ADP in Access 2007. Search through the menus, you won't find it. Search through the help system, no luck. Search google, maybe you'll stumble across it.
    The answer? Give your file a .adp extension when you create it.
    GOD, how I hate Office 2007.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2009
    Posts
    89
    I don't really want to write it off just because it was slightly harder to find the feature. IT will save me a lot of time to use projects but will i see the same benefits as the MDB with ODBC links.

    I've found many MDB vs ADP threads (wasn't searching for accronyms before), but most say ADP is only good with heavy use of VBA, which i don't really want to do as it's too time consuming. is thsi true? Or will ADP without VBA still be much faster?

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by rudeboymcc
    I don't really want to write it off just because it was slightly harder to find the feature. IT will save me a lot of time to use projects but will i see the same benefits as the MDB with ODBC links.

    I've found many MDB vs ADP threads (wasn't searching for accronyms before), but most say ADP is only good with heavy use of VBA, which i don't really want to do as it's too time consuming. is thsi true? Or will ADP without VBA still be much faster?

    I like using ADPs too but from what I understand there is no more ADP is in 2007 microsoft 86'd it apparently. I really dont like that they did that, was a bad move

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I have not touched access in a couple of years, but the whole point of VBA is that is easy and pretty cheap and quick to do.

    I concur on Office 2007 sucking. They moved all of the buttons and menus around and I feel all confuzzled every time I touch it. So I stay away.

    there was a rumour that I thought got shot down that VBA was going away.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jun 2009
    Posts
    89
    So are you saying I should stick to MDB on the basis that ADP and VBA are gonna be fased out in the next few years?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by desireemm
    I like using ADPs too but from what I understand there is no more ADP is in 2007 microsoft 86'd it apparently. I really dont like that they did that, was a bad move
    No, as I pointed out above, you can still create ADPs. Its just hidden, and poorly documented.
    MDBs (with linked tables) are simpler to create, but are really not appropriate for applications running against large databases. I can't think of any reason why you would need more VB code with an ADP, anyway.
    ADP is (was) always my first choice for Access/SQLServer applications.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2009
    Posts
    89
    just tried to throw together a quick ADP to see what would happen - apparently my Access 2007 SP2 is not able to edit a query on the SQL Server (2008) database.

    so that defeats the whole purpose. Am i gonna have to drop down to sql server 2005?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you should be editing your query using SQL Server Management Studio anyway. Not Access.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2009
    Posts
    89
    i thought one of the advantages of ADP is that you can edit queries from Access?

    and what's the difference between writing the queries in Server managemetn studio, than writing a pass through query in access?

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by blindman
    No, as I pointed out above, you can still create ADPs. Its just hidden, and poorly documented.
    MDBs (with linked tables) are simpler to create, but are really not appropriate for applications running against large databases. I can't think of any reason why you would need more VB code with an ADP, anyway.
    ADP is (was) always my first choice for Access/SQLServer applications.

    Oh ok sorry my mistake. I am working with 2007 now and I was having a heck of a time creating an ADP when I posted that on the Access side of this forum thats what I was told

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by blindman
    No, you should be editing your query using SQL Server Management Studio anyway. Not Access.
    Agree with Blindman its much better if you do that from Sql rather then access. Then when you are done just go to the file and then Connection then server connect and it will bring your query over
    Last edited by desireemm; 06-19-09 at 15:54.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudeboymcc
    i thought one of the advantages of ADP is that you can edit queries from Access?

    and what's the difference between writing the queries in Server managemetn studio, than writing a pass through query in access?
    The difference is that the data-oriented business logic is kept in the database, where it is more maintainable.
    Write it as a pass-through query (keeping the business logic in the presentation layer) and you will need to duplicate this logic for every application that accesses your database. And should you need to update or modify your database schema, you will have a hard time tracking down all the application nodes that will be affected.
    Putting this logic in a view or a sproc avoids these issues. Your code will be more efficient, more robust, easier to debug, and easier to refactor.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2009
    Posts
    89
    just found SQL server management studio Query builder :-)

    reason i wanted to use access was to use the query builder and then convert to Tsql. Now i can do that in sql server management so I will do that.

    cheers for the help guys!

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code like a pro. Don't use GUIs. They are crutches. Just write your code in the query editor.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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