Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: Converting from Access data to SQL Server data

    Here is the background. I'm converting an Access 2002 database which currently has the FE and the BE so the data will be maintained in SQL Server. This is a very stable app in that it has been running for 8 years. I have already done considerable reading and quite a bit of testing for this conversion. I wrote this app for my client starting in 1998 and it was running by mid 1999.

    In my reading, I have seen that SQL Server would help speed up the app by sending over the network only the data records that were needed. For example, this client is a roofing contractor. Each job on the system is a separate roof. Most of the work done within the database is one job (roof) at a time. There are 5 separate forms used to maintain the data for a job. Because this is an Access app, all my forms are bound to the table or query needed to present the data to the user for maintenance.

    Now comes SQL Server and my thinking is that I can write Stored Procedures for each query, or table that is being used as a record source for any of my maintenance forms. The stored procedure was written so only the one needed Job record would be sent over the network, not all the jobs in the system. Well, that does not seem to work as the Stored Procedures (at least that I have written so far) are not updatable recordsets. They all have been read-only.

    Now I'm looking at setting the binding the forms to the Stored Procedures, but not binding any of the fields on the form. The idea of binding the Stored Procedure is that I need to create a list somewhere of fields to be read and loaded into the fields on the form. What better way than to have a stored procedure do that for me. It will have the data already in it, and also have the list of fields to work with. I can loop through the Fields collection, and load the data from the Stored Procedure Recordset into any field on the form that matches the name of the field in the Stored Procedure. If there are any non-updatable fields on the form, they could be bound to the SP Recordset. For example, if I were to display City, State, and Zip in one field, and they each come from their own field in the recordset, I could set the Control Source to =[City] & ", " & [State] & " " & [Zip]. Then either on the user pressing an Update button (which they don't have to do now), or just when the user leaves the record (De-Activate event), tabbing out of the last field on the form (normally would go to next record), clicking a different tab on the tab control to pull up a different form, or ???? I would fire the routine to update the form data into the recordset identified with the stored procedure used as the Record Source for this form.

    Well, before starting down this path, I would really like some input from those who have dealt with this type of thing before. I have been an Access junky all these years, and this is basically my first venture into SQL Server.

    I'd say thanks in advance, but George V won't let me. So, the thanks will be coming later.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If your forms are bound to the tables in Access, why not link to the SQL Server tables, and leave them bound?

    My personal preference (FWIW) is to use only unbound forms, and update SQL Server with ADO.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    haha, touche'

    Anyways, I would very strongly recommend that you consider re-writing the FE portion of your project as an Access Data Project (adp). ADP's are specifically tailored to leverage MS SQL Server as a native backend platform, allowing you all of the benefits of MS SQL Server's superior features without sacrificing Access' rapid development capabilities.

    In general you're on the right track with abstracting user interaction with the database through views and stored procedures.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't think I agree with a re-write into ADP... Simply because the system has worked fine for 8 years or so - it sounds like re-inventing the wheel
    I can understand the benefits, but I think with what Vic has already produced (sprocs etc) we should be helping him down that route for now. (in future, he will have more of an oppurtunity to do so - please disagree with me if you think I'm wrong!

    Picking up on something Mark said: linked tables with ADO - sounds like a good idea. Updatable recordsets are the way to go imo.

    If you want to use your sprocs then I suggest looking down that route.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "sprocs etc" tells me he's already redesigning his data layer. That's the only major difference from a development standpoint between working with an adp or an mdb, assuming you're only working with a single datasource (not using multiple linked servers etc). That said, I don't see it being any more work to party directly with SQL Server using tsql syntax (which is the far superior imho) than it would to hand code ADO.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Now my curiousity is piqued...
    Nothing to do with Vic's question, really

    In an application that is not used over the internet, is ADP still preferrable to an MDB? I've never researched ADP , so I know next to nothing about it's uses.
    Inspiration Through Fermentation

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I can't speak from personal experience, as I've never seen the advantages of ADP's thus haven't used them, but I know of at least 3 MS Access MVP's that recommend against them. I use SQL Server most of the time, and have never had performance problems with MDB's.
    Paul

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pbaldy
    I can't speak from personal experience, as I've never seen the advantages of ADP's thus haven't used them, but I know of at least 3 MS Access MVP's that recommend against them. I use SQL Server most of the time, and have never had performance problems with MDB's.
    No kidding? Now I have to go rooting around, I very much prefer adp's. I'm curious as to why they recommend against them.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    2 quick quotes I turned up, from 2 different MVP's:

    the .adp is being phased out. I would recommend not creating new applications in the .adp format. Unlike the .adp, Access .mdb's can be used just as effectively as front ends to ANY back end database for which there is an ODBC driver. The limitations of the .adp are too restrictive and SQL Server centric. The format has not been widely adoped and so there is no big push to enhance the feature. The .adp will still be supported in Access 12 when it is released but no enhancements have been made. In fact, although existing .adp's will be able to use SQL Server 2005, they will not be able to create new databases through the Access GUI as they can with older versions of SQL Server.

    You can still use all features and functionality of Access in your FE if you link your tables instead of using an ADP. ADPs lack many Access features.
    Paul

  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thank you! All four of you! I have a few statements and questions which I feel will sum up where I am at this point in the decision making process of how to proceed. Your input has been very valuable.

    I have tested/played with ADPs a couple of years ago and really did not like the way they functioned. So I have not even considered them for this conversion. Then when Paul said he knows of at least 3 MVPs that suggest not using ADPs, that sort of made up my mind for sure.

    It really looks like there are two main ways I could go with the data retrieval/data update methodology. Either link the SQL tables to the database and proceed much like the application always has been. –OR– Using sprocs and views, filter the data before the data is sent over the network. Then bind the forms to a sproc recordset. Do NOT bind the individual fields. That will be handled by a routine to read the sproc and load any data in the sproc recordset to the individual fields on the form. Still working on update methodology.

    Quick list of questions/requests:
    • I would like an overview/outline of how to get the updated/changed data back into SQL Server. ADO and Access queries/VBA built queries, spoc that would receive parameters of table name, key field/s, key data, field name, and new value, then write the new value to the reqeuested table, field, and record.
    • Is there any way to get an updatable recordset from SQL Server that is only part of the table?
    • Does the idea of binding the form to a sproc, but not the fields sound like a good idea? Why, why not?
    • For tables with identity columns. How to update the data in this type of table? There is a keyword that must be used, but how to incorporate that keyword in an Update Query? Is that done with ADO, and if so, how?

    Again, THANKS, THANKS, THANKS! This has been a really neat exchange of ideas so far, and I’m looking forward to some more on this topic.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I had similar experiences playing with ADP's, so never used one in a production db. However, Teddy is a strong user that I respect, so they can't be all bad.

    My rule of thumb is to use bound forms and ODBC linked tables when an application will only be used on the local network (most of my apps), and unbound/unlinked tables when an application will be used remotely via VPN (a couple). There's nothing scientific behind that, it's just what felt right to me.

    For the unbound apps, I use sproc's that take the field values as parameters, called via ADO command or recordset. In an edit situation, that would mean a recordset opened on the specific record (sproc takes key field as a parameter), then copy values to the form. Upon a "save" command, an update sproc called with the field values passed to it.

    For bound apps, I don't think I ever have a form open with all records. They're either opened in data entry mode, where no records will be pulled down, or with a wherecondition that limits them to the record to be viewed/edited. Simpler reports I just use Access queries for, more complicated ones (read slow) sprocs and pass through queries.

    And now I have a user whining about something, so gotta go!
    Paul

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    For bound apps, I don't think I ever have a form open with all records. They're either opened in data entry mode, where no records will be pulled down, or with a where condition that limits them to the record to be viewed/edited.
    It was my understanding that with a bound form, a WHERE condition to specify only one, or just a few records is done in Access, therefore the whole table is still sent accross the network. That is what I'm trying to avoid. Do you know if the whole table is sent accross the network or not in that situation?

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by GolferGuy
    Then when Paul said he knows of at least 3 MVPs that suggest not using ADPs, that sort of made up my mind for sure.
    That took me a little off guard but they make valid points. I guess I view the tight integration with sql server as a plus, but there are a good many scenarios where this is a very, very bad thing.

    I would like an overview/outline of how to get the updated/changed data back into SQL Server. ADO and Access queries/VBA built queries, spoc that would receive parameters of table name, key field/s, key data, field name, and new value, then write the new value to the reqeuested table, field, and record.
    Generally speaking, if you're abstracting data access through sprocs and views, you're not going to be passing in table and field names to your stored procedure. One of the benefits of using sprocs is your application does not need to know anything about the underlying structure in order to get data where it needs to go. Take a basic example where you want to insert a user into a typical user table:

    Code:
    CREATE PROCEDURE usp_AppendUser
            @FNAME VARCHAR(50),
            @LNAME VARCHAR(50)
    AS
    
    INSERT INTO tbl_Users (fname, lname)
    VALUES (@FNAME, @LNAME)
    In the above example, tbl_Users could have any format, live on any server and have no access rights to anyone other than dbo. Your application would be none the wiser.

    Is there any way to get an updatable recordset from SQL Server that is only part of the table?
    Yup, views can be updatable if they meet certain criteria.

    Does the idea of binding the form to a sproc, but not the fields sound like a good idea? Why, why not?
    Sure it is. Sprocs return a recordset that forms know how to deal with, however the recordset returned may or may not have any direct correlation to your actual data structure. The recordset you got from a sproc could contain derived values, input from external sources, transformed data or any number of nondeterministic sources that would make it impossible to two-way bind.

    For tables with identity columns. How to update the data in this type of table? There is a keyword that must be used, but how to incorporate that keyword in an Update Query? Is that done with ADO, and if so, how?
    [/LIST]
    Same way as tables without identity columns? Unless you're talking about directly updating the value in an identity column itself, in which case you can temporarily toggle SET IDENTITY_INSERT for your target table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by GolferGuy
    It was my understanding that with a bound form, a WHERE condition to specify only one, or just a few records is done in Access, therefore the whole table is still sent accross the network. That is what I'm trying to avoid. Do you know if the whole table is sent accross the network or not in that situation?
    I won't pretend to be an expert on the technical aspect of this, but I have forms bound to tables with over a million records, and they come up instantly. If I open the table directly, it takes several seconds before it will bring up the last record. That implies that it is NOT bringing the whole table down when using a wherecondition or query with criteria.

    My understanding, which is supported by experiences such as above, is that Access will try to have SQL Server do the filtering and only send the results, and it usually will. If you were to use a function that SQL Server didn't understand (like IIf), then it would have to send all data to the client for processing. There's a decent article here that seems to back that up:

    http://support.microsoft.com/?id=208858
    Paul

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Yup, views can be updatable if they meet certain criteria.
    Where do I find the certain criteria that needs to be meet?
    The recordset you got from a sproc could contain derived values, input from external sources, transformed data or any number of nondeterministic sources that would make it impossible to two-way bind.
    I'm writting the sproc, so it's going to be data from a table. But I do understand about the two-way binding, and that it is not going to work. My idea of binding the sproc to the form is to give me a list of the fields to load in the form from the sproc. And a list of the fields from the form to use to send to another sproc to update this data in the SQL db.
    Same way as tables without identity columns? Unless you're talking about directly updating the value in an identity column itself, in which case you can temporarily toggle SET IDENTITY_INSERT for your target table.
    I have tried to do an update query, written dynamically in VBA, that did not touch the identity column, but had an error message stating that I had to set IDENTITY_INSERT on before I could execute the query successfully. But I don't know how to set IDENTITY_INSERT on from Access. Do you know how from access using the keyword that Access requires? If yes, could you explain it.
    Thanks Teddy!

Posting Permissions

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