Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: ADP vs ACCDB linking to SQL

    Would just like to hear peoples views on using either ADP or ACCDB version to work with data in SQL using Access 2007 as front end. Many thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ACCDE is your best bet.

    I believe ADPs have been dropped along with a fistful of other stuff in 2007.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    According to Allen Browne's site "Access 2007 does not support ADPs as well as previous versions." I'm not sure exactly what that means, but ADPs are still supported:

    http://office.microsoft.com/en-us/ac...679531033.aspx

    That said, as StarTrekker said, most (but not all) people "in the know" recommend an MDB/ACCDB with linked tables rather than an ADP.
    Paul

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I developed the same 2 projects (both large scale with about 5 million recs) in both an ADP (using version 2003) and an mdb (using version 2003 - which is now an accdb in 2007). There was no advantage at all using an ADP. Actually, it was a big disadvantage using an ADP versus an mdb since you lose the capabilities of utilizing queries in an ADP (versus an mdb/accdb using linked tables.) Plus it took 3 times as long to develop the ADP (since everything has to be done in an unbound form fashion.)

    There was also no speed difference (for returns on 5 million recs) between using an mdb (with unbound forms) versus the ADP (where everything is already unbound.)

    Make your life much easier on yourself and stay away from ADP's. You're much better off development time-wise, troubleshooting-wise, and option-wise creating an mdb/accdb versus an ADP.

    The only advantage I saw to an ADP is the ability to make design changes to the SQL Server data tables directly in the ADP (without having to open Enterprise Manager in SQL Server.) But I also did have 1 developer who deleted all the tables in the ADP (thinking he could do this like you can with linked tables in an mdb file.) This of course, also deleted all the SQL Server tables as well. It was much more difficult to get other developers onboard with the ADP concept and if you're intent on using an ADP, I can only suggest that you pre-plan out all the procedures, development process, function names, etc..etc.. very carefully with all the other developers before diving into an ADP. I'd also start with a very small ADP project keeping in mind that your routines/forms would double/triple on a larger scale project.

    I personally have avoided ADPs since it took so much longer to develop and troubleshoot.

    Of course you should always compile your mdb/accdb/adp into an mde/accde/ade for the users.
    Last edited by pkstormy; 11-04-09 at 21:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2010
    Posts
    13
    Hey guys, i've begun programming in Access 2007 and i'd like to know what the best option is:

    1. I have about 10 users (10 client pc's) who like using Access 2007
    2. I have SQL Server 2008 software on the server that is using Windows server 2003 os
    3. My original idea was to complete the project in Access 2007 and then use the migration tool to have a front end access 2007 with back end sql server 2008. The project is only 10 % finished right now.

    I've been reading alot about scenerios, like accdb, adp's, Jet vs TSQL and i'm not sure what the best options is for this kind of setup. Should I continue with accdb or use adp ? or what is the best setup/option ?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Are you looking for opinions other than the 3 concurring opinions already expressed?
    Paul

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're looking for an opinion:

    1. I'd disregard ADP (it double/triple your coding time and you lose the ability to do queries). They can be a headache and MSAccess doesn't support ADP well. You lose more than you gain and there's no significant speed difference versus using linked tables into an accdb (and/or designing unbound forms - see 4 below).

    2. Design your tables in MSAccess and use the upsizing wizard to SQL Server 2008 (after you work out all your coding/form design, etc... and table structure is finalized - it works VERY well (make it one of your last steps.) Double-check your identity field (ie. autonumber) and relationships just to make sure (it works very well with typically good relational setups but can have problems with poor relational designs.) Make sure to have your primary key, autonumber, relationships all setup correctly in MSAccess before upsizing (again, the upsizing wizard is superb for upsizing and maintaining everything - I use it all the time! and it only takes minutes to do.) NOTE: to make it easier, name your MSAccess tables dbo_mytable name, prefixing it with dbo_. After upsizing, rename the tables in SQL Server (ie. removing the dbo_ in front of each of the table names - SQL Server will nicely rename them without problems), then link them into MSAccess (after step 3 below). They will automatically link into your MSAccess accdb as dbo_myTableName and then you won't have to rname the table or change any MSAccess coding after linking since your coding is using the dbo_ syntax for the table name.)

    3. Create an ODBC DSN and use linked tables. Linked tables work very well (again, you can then utilize queries against them and design your reports easier).

    4. If user's have a poor or slow connection to the network drive (where the front-end will reside), consider using unbound forms (it's basically the same as an ADP project where you need to write functions to write/retrieve data to/from the forms but easier than ADP - again, you can utilize queries if need be. Queries can save you a lot of design time versus trying to work out coding syntax (or writing stored procedures against SQL Server) which you could EASILY design quickly in a query in the accdb.)

    5. Check the code bank for an example I posted which automatically creates the ODBC DSN for the user so you don't have to create it for each user's computer. You can add this code (which is basically just a module) in your frontend and then you never have to worry about creating the ODBC DSN for users. NOTE: All ODBC DSN names on each user's computer to the same SQL Server tables MUST be named EXACTLY the same name (otherwise you will have problems!)

    6. Consider using Citrix (or terminal server or VPN) for EXTERNAL users connection to the network. These work very well (although citrix licenses get pricey but I've used citrix with very few problems for external users). VPN is also very nice for external users if you have a good network person to set it up correctly.

    7. Check the code bank (page 2) for a vb script which clones the front-end with the user's name and launches the cloned copy (users always use this script to get in the front-end). It prevents "locked by another user" type errors when multiple users (ie. more than 3) get into the same front-end and also allows you to edit/copy new code at any time (since no users are ever in your source mdb - it just uses it to clone off of when the user runs the vb script).

    8. Create a compiled version of the accdb (ie. accde) for your user's front-end (since an accde doesn't error out to the actual code it bombed on and user's can never edit/see your code.) They are also more efficient.

    The best speed performance for external users to the front-end is to again, use unbound forms (especially on extremely large datasets over 100,000k). With SQL Server (and putting the front-end on the user's desktop but note you then have to keep track of updating each desktop with new code), you basically eliminate the user connecting to the network drive itself. I did this for record sizes in the millions and user's who had a very slow external connection could return/update records within 1-2 seconds. If external user speed isn't a factor, keep the front-end on the network (and use the vb script.) You're then limited by the user's connection speed to the network drive (and the SQL Server computer.) Note: network loss of connection can be a problem (as with any other programs) - ideal to have a good network person.
    Last edited by pkstormy; 01-28-10 at 22:39.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2010
    Posts
    13
    wow, thank you so much, this is great advice! i just have a few questions to clarify:

    #2. all dbo tables will show in the front end right ? how can i hide them, so users don't just click on the table and can see all the records ?

    #3. I read somewhere that OLEDB vs ODBC where OLEDB is the successor to ODBC so does that mean it's faster ? or.. should i forget bout that one.

    #4. So how come an unbound form vs a bound form, is faster / better ? what i'm currently doing is setting the form's "record source" property to "blank" and when the form opens the sql string in the form open event sets the recordsource property to retrieve last 2 months of records from the tables, so if there are 100,000 u get probably 800 records retrieved. the code in the "Form_Open" event is:

    Code:
    Dim strSQL as string
    strSQL = "select ... From ... Where RDate between ... and ..."
    me.recordsource = strSQL
    is that somewhat an unbound form ? because i'm not setting the form's record source to the table for example tblEmployees ?

    #5 I will definitely check the code bank, thank you very much for this advice

    #6 We currently have a VPN and that's already set up, so it'll be very easy to work that into the project

    #7 does the clone get deleted when it's closed ? and then after that when the database is opened again, it clones it again ? kinda cloning a new one everytime (just in case the front end is updated you wouldn't want users to have old clones)

    I have only 3 modules or i should say forms/tables that will be using 100 k + records for sure. The thing one of the most complicated concepts for me is record locking. This is most likely a common problem with databases. I have a table called Gas Receipts, some columns are:

    Date Vehicle Liters OdometerReading KMPerLiter

    when users enter gas receipts, there's a calculation of KMPerLiter performed for each records that's dependant on the previous record of that vehicle.

    So KMPerLiter = (Current Odometer - Previous Odometer ) / Current Liters of that receipt, now the tricky part is when users change old receipts, then the KMPerLiter for all receipt after the one they changed will be different, so my question is:

    When i create a stored proc on sql server 2008 database that recalculates all KMPerLiter for all receipts/records, what if users on the front end are editing records, how can i prevent problems with record locks ?

    Thanks for the help i really appreciate it

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding your questions...

    2. Yes, all dbo_ tables you link into the front-end will show unless you hide them (like you would an MSAccess table). But double-check this. I usually control access to the data tables themselves only through forms. I never let the user get behind the scenes.

    3. Can't say with OLEDB. I've used ODBC for years without problems.

    4. Unbound forms are faster because you're opening and closing the recordset (it's not left open while the form is open). This is ideal in slow connection environments (ie. the user clicks a save button and you're quickly opening/closing the recordset to save the record and the form's not 'bound' to a data table. But keep in mind that things like expressions or formulas, combobox rowsources, etc....may still be 'bound' to a table (ie. an unbound form with dozens of comboboxes won't be as fast and non-problematic as a form without a dozen comboboxes.) Example: Try adding a dozen comboboxes on a (bound) form and compare the form's opening speed against a form without any comboboxes (since the comboboxes load when the form opens). You can always populate things such as the combobox rowsource in code when it gets the focus but there will be a delay then when the combobox gets the focus - it's a catch ten).

    But a user updating a field on an unbound form doesn't have to constantly communicate to the data table since the fields on the form have no source (are just place-holders so to speak). It's the same philosophy as if you open only 1 record to a bound form versus multiple records to a bound form. The single record returned will perform better and load faster.

    For unbound form design though, you have to consider that it takes 2-3 times longer to develop these (since you have to write code to update each unbound text field to/from the form.) It's much faster to simply create a bound form (or do as you're doing and setting the recordsource) since the form then controls when the fields are updated versus your code. Subforms can be a pain when designing unbound forms and remember, you don't have to make ALL forms unbound. I would often design popup forms to update small data table sizes using bound forms and make the large (5 million) customer form unbound.

    Think of it this way for unbound forms, when a field is updated, there's no communication to the data table until the user clicks a save type button (since the form never get's a recordsource). But on a bound form (regardless if you're setting the recordsource in code, it's still bound), there's constant communication to the data table after every data field is updated. Still though, I rarely use unbound forms (due to the time involved coding it) unless there are problems with external connections or they are slow. You'll have to test this and weigh it against the amount of data you have and your form design (again, good form design is key.) There's also some additional techniques and methods in page 5 or 6 in the code bank that I posted regarding form design. If record sizes were anticipated to get into several 100k or 1+ million, I might consider unbound forms, otherwise I'd concentrate on good form design. Again, using SQL Server really is efficient and helps versus a backend mdb where unbound forms might be more weighed.

    7. No. The clone remains (which is a bonus as it allows you to see when the user last opened it as well if they still have it open.) It's re-created though when the script is run so if a user happens to lock their front-end mdb, (besides it NOT affecting other users), the user won't have problems the next time they try to open the mdb since it's re-created via the script (a new fresh cloned copy which doesn't have all the data buildup you'd get if using temp tables in your design). Note though that you'll want to stress to users to stay out of the folder the clones are being created and use the script (I usually put it in another folder). You definately don't want users avoiding using the script and just opening their cloned copy because then they are not getting the last source mdb updates you copied. Since you can copy a new source frontend mdb at any time (without making users close out of the front-end), all you need to do is copy the code and send an email to users to have them exit and re-open the mdb (using the script) to see the new changes. Since most changes are minor and often cosmetic (unless you stress exiting/reopening in the email), user's can read your email and determine if they need to exit/reopen to see the minor change now or later. It allows them to keep working while your making little changes here and there, copying a new frontend (ideal for environments where it's important user's keep working in the front-end and you don't want to hunt them all down to close the front-end).

    Using SQL Server and linked tables, SQL Server does a very nice job of keeping track of updating records (ie. bound forms) - ex: first in, last out updating. The only time I've seen problems is with poor form design where users are updating the same data table via multiple open forms (as I rule of thumb, I try to make it so only 1 form is allowed open at a time updating the same data table.) I've never really had to worry about record-locking using linked tables and good form design but have seen problems on unstable network connections. The error MSAccess produces (ie. "Locked by another user") is often confused with record-locking when it's actually a corrupt *.ldb file where all users are in the same front-end. I might consider unbound forms if I knew some users where having unsteady connections updating the data, otherwise form re-design if I was getting actual record locks (I would monitor this in SQL Server using traces - this is a great tool!) You should also monitor in Task Manager, the size of the MSAccess process when your front-end mdb is running (testing all forms and code!) If this size continues to grow above 100k or more, you've probably got some coding leaks in your design (ie. not closing recordsets in code) or not re-using recordset names/variables/etc... and this can cause problems for users with limited memory. I once forgot to close a recordset in code on a key form and the MSAccess process kept growing and growing.

    Stored Procedures are nice, but again, more time-consuming to design and troubleshoot. Sometimes a simple function in the front-end mdb takes care of what a complex stored procedure could do. I didn't notice any significant speed difference using stored procedures but it does allow you to standardize specific procedures if developing a multi-complex project where you have several different mdb programs all tying back to the same data tables. If it's a 1 mdb and db project, it's your choice if you feel more comfortable with stored procedures. They are ideal but again, you have to weigh these agaisnt the complexity of the project and timeline to complete it. You also 'separate' your coding using stored procedures and must then look at 2 different places to troubleshoot your code versus in just the front-end.

    Most of the projects I do require a very fast turn-around time (ie. a month or two). For me, as the dba and developer, designing good 'template' type data entry forms that I can re-use them in another project is key (and don't have a year time-frame to develop it). This makes it so users are familiar with the next program with very little learning curve (since it's the same data entry form). I design my forms so I can simply import and re-use them in other mdb projects with little or no tweaking. If you're interested in some of the template type forms I use, I've posted several in the code bank. I especially re-utilize things like the excel type reporting query example on page 6 or 7 which works nicely for allowing users to open/view queries (in a form view) as well as export the data.

    When I was at the Energy Center, I concentrated on standardizing forms/code related to energy values. Where I'm at now, I concentrate on things related to the hospital and medical values. Eventually a company puts more demand on in-house development and it can double/triple your future workloads if you don't think about the company's overall goal and standardizing when developing your projects. Standardizing a good re-usable relational structure is also a key step.
    Last edited by pkstormy; 01-29-10 at 23:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Jan 2010
    Posts
    13
    Thanks again, i really appreciate your input, you've been very helpful and i've learned a lot from you. I will definitely program the three forms using unbound forms, that's a must. Is there a sample database that I can look at that uses unbound forms ? Also, would you be interested in looking at my accdb file that i've worked on so far ?

    When you have to do checks like... Say If you are doing a time sheet entry form, and you want to check if the user is entering hours that cross over, for example the user already entered:

    Date Start Time End Time Employee ID
    1/1/2009 1000 1800 1

    and he's trying to enter:

    Date Start Time End Time EmployeeID
    1/1/2009 1700 2100 1

    so basically I normally use a DLookup here, but i know using DOA is faster, would you recommend ADO ? because of SQL Server 2008 using T-SQL ?

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I personally don't think there's a significant difference between DOA and ADO. It's more of a personal preference. I like ADO because I can simplify the syntax to my understanding but other than that, it's up to you.

    TimeSheets are fun projects to do. I've done a few of them. I can't remember if I posted any but I did post a calendar type form.

    How you setup the structure for your timesheet will be key. You often have to consider 1 user might have multiple projects and certain hours assigned to each project each day. Users don't like to have to add each project each day and this can be challenging.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jan 2010
    Posts
    13
    I've checked out the code bank, and I have a question about connections, if it opens the connection to sql server 2008, do i have to open a connection everytime i wanna do something with a recordset ?

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're working with recordsets in code, yes. You can either take advantage of using linked tables (and write your functions to update data within modules in the mdb which I prefer) or disregard the linked tables and (use a stored procedure and write a function to open/pass values to the stored procedure.) Either way you're making a connection. SQL Server trace is really beneficial for watching what happens with your connections to the Server and when you're connecting/updating.

    You also have to factor in where the mdb frontend is going to live and the user's connection to that location (network drive or user's desktop). An mdb on a slow/problematic network drive is a killer.

    Timesheet type of projects can be time-consuming to develop so you may want to focus on whichever is going to be the quickest and easiest for you to troubleshoot.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Feb 2010
    Posts
    2
    How does one get to the code bank?

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Paul

Posting Permissions

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