Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Hey all,

    I guess I'm just hoping for some general advice. Our place is currently using Access 97 (SR-2). The upgrade schedule for XP is for November (although I work for the gov't, so I'm sure this won't happen). I have a lot of stuff that I know I'm going to have to update to ADO connections (everything is in DAO), but at the moment, I'm working on something that probably should start there. Oh, I also need to state that although my backend in this case is SQL server 7 (and normally is DB2), I am NOT allowed to touch the server (no stored procedures or anything even similar). (The only reason for that is that I work for the gov't.)

    So right now I'm (mostly) using linked tables to pull data from SQL Server 7, then I run all my queries locally. The problem with this is that the download takes at least 10 minutes, and I'm just at the beginning of the project, so this download is going to grow. (I have a few projects that download using passthru queries, but they're all written in DAO anyway and will probably have to change, too.) My questions:

    --What is the most efficient way to download information from SQL Server 7 into Access 97?

    --Is there any easy way to write this so I don't have to worry about conversion issues with Access 2002?

    --Should I be writing my download module in ADO instead of DAO?

    --Can anyone point me to any good references on how to convert my DAO code into ADO?

    If anyone has any advice for me, general or specific, I would greatly appreciate it....

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

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Hi Cecilia -

    What you are asking could be the topic for at least a one volume book. So let me give you a few generalities and then point you to some more detailed references.

    <UL><LI>The most efficient way to get data from SQL Server 7 into Access 97 is to use either a view or a stored procedure in SQL 7 - that way Access only sees the data it needs. To be fair, if you use linked tables with ODBC, and if your queries are well behaved, the ODBC interface will often use a SQL string that is reasonably efficient, but nothing beats a direct connection.
    <LI>Unfortunately there isn't a way to avoid all the conversion issues with Access 2002
    <LI> Access 97 doesn't support ADO, so you don't have a choice as long as you are working with 97.
    <LI> I would suggest you look at a couple of resources - Helen Feddema has a book out that talks in some detail about ADO in 2002 called Access 2002 Inside Out, and the "Access Developers Handbook" is now available for 2002. Also, Alison Balter has a new book due out June 20 that should cover it pretty well. There are also some white papers available, but they are a bit less user friendly.[/list]Hope this is useful.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Hi Wendell!

    Thanks for the resource list! I think I'm going to put off the XP issues for a bit and worry about them after I've had time to look at some of those books.

    I had thought that you could set a reference in 97 to allow you to use ADO, but I guess this is not true? Hmmmm.

    So I will focus on the problem of efficiently transferring the data from SQL-Server to Access 97.

    I wish I could do stored procedures, but unfortnately, I only have read access to the tables and it would take a tornado to get those others to do anything with the server at all. So basically, I'm stuck with what I've got, transferring the data from SQL Server to Access.

    I had been told at one point that Passthru queries were more efficient than linked tables, due to interference with the Jet engine. Is this not still a belief? If there's any truth in it, is there a preferred way of doing it?

    Thanks!

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

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    This is small solace, but the government isn't the only one that won't let users use their databases! We have a client in the financial services who has to make a copy of their server table (in Access) so they can use it to do their job! Of course the data changes daily, so they have to update it frequently and rebuild numerous tables! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Actually, if you have read access to the tables and give the POWERS THAT BE the scripts for views and/or stored procedures you may be able to do more than you think. Views in general can be made read-only, as can stored procedures (at least sort of). Finally, your comment about pass-thru queries is generally correct, but what you find is that in many cases you need to pass a parameter to SQL Server, which means you need a stored procedure - a Catch 22 apparently in your case. But they are worth exploring - the real issue is the volume of data you need to capture - if it's only a few thousand records then it doesn't make much difference. If it's 100s of thousands or more on the other hand . . .

    One idea that occurs to me is that you might consider using the MSDE as an alternative if your user population is small - no more than 3 or 4. You would still have to use tools to transfer the data, and there are size constraints similar to MDB files, but you can have views and sprocs that way. Just a thought.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Hi Wendell,

    Basically what your client does is what I have to do. Security, security, security! LOL

    I'm taking your advice at this moment, but I'm not very hopeful. I've sent out a request for a few stored procedures (this is a transactional db, so I'm thinking that if I can just pass it a start and end date & therefore only hit records, say, for the current quarter or full year (the db contains 5 or 6 years at this point), it should speed things up? More info: when I pulled down a quarter's worth of data, I got about 350,000 records.). We'll see if anyone wants to help me. _Then_ I'll worry about learning how to do this ;-)

    My user population for this db is small, it's only to build reports for budgeting, so even if potentially there could be 5 or more using it, probably never more than 1 or 2 at a time. What is the MSDE? (Aside from another acronym I need to learn ;-)

    Thank you so much for all of your advice here!

    Cecilia :-)

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

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Hello again -

    Actually all you might need for the quarter or year of data would be a view, not a stored procedure (or sproc for short). Views are very much like simple select queries in Access, and are updatable under some conditions, especially with SQL Server 2000, but cannot accept a parameter. So if you need a parameter (the start and/or end dates for example) then you would need a sproc. But if you are getting 350K records you are dealing with lots of data. On that presumption, you would bet 1.4M records for a year, and that's pushing Access Jet data to the max if you have to store it in a local table and manipulate it. Is there any way to reduce the recordset further?

    Sorry about the alphabet soup - MSDE stands for the Microsoft Desktop Engine, which is a slightly dumbed down version of SQL Server 2000. It will handle 3 or 4 users at once, has a limit of 2GB I believe, and has only rudimentary admin tools. However, if you can get the system guys to help you out, it can be administered with SQL Enterprise Manager and all of the normal SQL tools. It and SQL Server are the two options if you opt to go with an ADP rather than an MDB backend in Access 2000/2002. It can for example be populated with the Binary Copy Program, or with the Data Transformation Services that come with SQL Server. Either of those should be substantially faster than an Access append query using ODBC.

    Wendell
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    LOL! I guess your client DOES have better access than I do. I can't do anything server side at all. I'm not even allowed to install software on my pc, regardless of its use, and apparently under XP they're trying to strip away my ability to write code. They've already destroyed our ability to set references or use ActiveX. The chances that they'll let me try anything like what you're suggesting are less than zero.

    Basically, I'm trying to give the guys here in budgeting a button to press to produce their reports, so I don't think MDSE is going to cut it. I'm trying to sum my rows now, so I can reduce them a bit. If so, I should be able to get them below 100,000 per quarter, therefore 400,000 per year.

    Thanks again, Wendell, for your advice! I have so much work to do....hee hee....

    Cecilia :-)

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    You can use ADO in 97, but you don't have the capabilities that you have in A2k and later. For example, you can't use Set cnn = CurrentProject.Connection because there is no CurrentProject in A97. The vanilla ADO will work but it's most useful for executing stored procedures, and you don't have that luxury.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Thanks Charlotte, that makes sense.

    Can you point to any references that will make it "easier" for me to convert from DAO to ADO when the time comes?

    Unfortunately I also don't have the luxury of time. My users (or at least half of them--ugggh) will be getting XP at the exact same time I do, so I will have no time to do all this conversion, or to even learn how to do it. Uggggh.

    Cecilia :-)

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

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    OUCH! That's a disaster in the making. As soon as your users start opening 97 databases, they are going to get prompts about converting them to 2000/2002. If somebody does, then the fun begins. We had 2000 for 9 months before our largest client started converting, and at that it took nearly 2 months to convert all the databases and the PCs. Best you start making lots of noise soon, or plan a long vacation <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> about the time they do the upgrade.
    Wendell

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    LOL! Don't I know it. Luckily most of my stuff is compiled, so it won't even open. But I need to convert everything to have an option for the XP users.

    Unfortunately, I'm only a cog in the wheel. (Part of me thinks that they _want_ my stuff to die, so they can point fingers, but bottom line my stuff is needed by the people I report to. Uggggh.) My making noise does nothing. In the gov't, it all falls on deaf ears.

    This is going to be _so_ much fun.

    Cecilia <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  12. #12
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Hi Cecilia,

    I'm in the process now of converting my 97 apps to XP. So far I've only done a couple but they were big ones (lots of code). Plus, I upsized the backend of one of them to SQL 7 since it's a multi-office app and our main office is converting first so I have to be able to use it in both 97 and XP. That was fun!

    I've decided to convert them with the existing DAO code and then go back later to change it to ADO. With over 100 databases and a limited amount of time I don't have much choice. It may not be the most efficient but as long as the database is compiled in Access97 before conversion so far it's been a smooth process. And I haven't noticed any performance difference yet. Anyway, I thought I'd mention it FWIW.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Excellent suggestion, Paul. We tend to forget that DAO still exists in Access 2002, even if Microsoft is hoping everyone will immediately convert to ADO.

    In fact, I converted most of my A97 databases to 2000 without doing anything except disambiguating my code by specifically declaring objects as DAO.whatever. That way I could slip in an ADO reference and some ADO code going forward without going nuts trying to track down the errors but didn't have to completely convert the apps to ADO. In A2k, at least, the DAO method is often faster than ADO for operations that involve interface/Jet objects, since DAO is optimized for Jet. I haven't tested that in AXP but I would guess that you wouldn't pay any real penalty for continuing to use the DAO code for a while.

    I never had the habit of using the compatibility libraries to support obsolete code, so my 97 code was current to that version, not a patchwork of stuff from earlier versions which might cause problems in the conversion. It wouldn't be a bad idea to check on those things prior to conversion, because there are a few techniques that definitely don't work in the later versions of Access. The query engine changed somewhat between 97 and 2000, which can cause you some grief; and 200x is less forgiving of things like trying to close the database with a hidden form still open. You could spend your time chasing down the potential memory leaks and stuff that would otherwise get you in trouble and save yourself some grief later.

    Better yet, get a copy of AXP and install it on your machine so you can try it out. Even the government sees reason occasionally (well, most of them ... once in a while), so you might be able to sell them on that. It's most likely the IT department rather than the "government" that is unwilling to give you an advance copy because they aren't ready to support it. I was lucky when I was a government contractor because my government client had enough clout to get me the Office 2000 software and I had permission to install pretty much anything on my own workstation.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    >> disambiguating <<

    I like that word <img src=/S/smile.gif border=0 alt=smile width=15 height=15> But does it realy exist? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>


    Peter

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hoping for Advice: ADO, SQL Server, Passthrus (Access 97 on Win 98)

    Only in the mouths of software geeks since ADO was thrust upon us. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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