Results 1 to 9 of 9
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I almost never use ADP files in Access, but now I'm trying to understand some reports that were built by another developer using an ADP front-end to SQL Server. The reports are typically built using a Stored Procedure as the data source, and the command to open the report passes a parameter or parameters to the stored procedure to filter the data. The data sources for the stored procedures are typically views, but may also be a table. However the text box controls are nearly all unbound, and I don't see any VBA that would set the values for the text boxes. Can someone who works in this format make a suggestion as to where to look to find how the text boxes are being populated? Thanks in advance.
    Wendell

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I fear that there are few Loungers who use ADPs. Sorry!

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Is the ADP small enough for you to post (and not commercially sensitive)?

    I'm not an expert. but if the application is actually working then there must be some code residing somewhere, either in an open event or a click event or somewhere else.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='jeremybarker' post='787442' date='04-Aug-2009 00:58']Is the ADP small enough for you to post (and not commercially sensitive)?

    I'm not an expert. but if the application is actually working then there must be some code residing somewhere, either in an open event or a click event or somewhere else.[/quote]
    Unfortunately it is not - it's a 60+ MByte ADP linked to a 500 MByte SQL Server back end - and it contains sensitive personal information . My suspicion is that there are class modules for each report that do the binding, but I've been diverted by other things and haven't had time to research the situation. And the lack of response confirms what I suspected - very few developers choose the ADP approach - it's much faster to develop using MDBs.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Hang on, an ADP has no local "data storage" - just code and user interface elements like forms and reports. It must be pretty intricate to comprise 60MB of those alone!!

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Well, it appears my memory is more than a little flakey - the ADP is actually 10 times that size - I looked at the wrong database. And the ADE version is almost as large. It is a poster child for how not to do things. I suspect it was never compressed, as I see it growing in size over the course of a year to much larger than you would normally expect. But it appears to have over 1000 sprocs, and several hundred forms and reports. My only interest is I have to replicate a few of the reports.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    I'm still very very confused (and therefore not able to provide much help)

    Access databases grow because of table growth through record insertion, and need to be compacted to reclaim space freed up by record deletion from those LOCAL tables.

    I don't understand how an ADP can "grow" over the course of a year - it has no local data storage (tables, views and sprocs live in the back-end i.e. SQL Server). The forms and reports and modules would have to be getting bigger all the time. I feel I must be missing something very fundamental here : (

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='jeremybarker' post='787617' date='05-Aug-2009 02:11']I don't understand how an ADP can "grow" over the course of a year - it has no local data storage (tables, views and sprocs live in the back-end i.e. SQL Server). The forms and reports and modules would have to be getting bigger all the time. I feel I must be missing something very fundamental here : ([/quote]
    Well I was puzzled too, but I think I finally figured that piece out. The ADP file was nearly 1GB in size, so I tried a compact on a copy, and it reduced it some 30 MBytes. So I tried to import all of the objects into a new ADP, and the tables an queries only took up a few MBytes. Then I tried to import all the forms, and the xxxx hit the fan. Access failed with an out of memory error part way through, and the resulting ADP was again nearly 1GB. So I started looking at the form design, and each form has a graphic object as a background on the form. And the file is a BMP file that is 28 MBytes in size!
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Ah, well done Wendell - the only thing I could think of was something large on a form, or forms being created dynamically.

    Still no joy on tracking down the source of the Stored Procedure execution I suppose?

Posting Permissions

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