Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    SQL Backend (2k)

    I have a nice robust access tool that compiles and creates ~200 various reports and graphs based on users' data in Access tables. Recently, one user asked me to transition the backend from Access to SQL. Converting the tables, while not complicated, was tedious. Now that the tables are complete and working, I started running reports. AAAAAAAAAHHHHHHHHHHH! ! ! ! !
    Is there a "simple" or other means of passing parameters gathered on a form into a query? The reports still look marvelous, but the data presented sucks. LOL

    If you can just point me in the right direction, and/or recommend some worthwhile reading, I would be forever indebted!

    Thanks in advance.

    Respectfully,

    Ken

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: SQL Backend (2k)

    It depends on where the processing is being done. If you're doing it in the front end using Jet, you can keep going as you have been. However, SQL Server doesn't know anything about Access forms, so your stored procedures can't reference them as parameters. What exactly do you want to do and what object model are you using, DAO or ADO?
    Charlotte

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

    Re: SQL Backend (2k)

    Are you trying to do this with a MDB front-end or an ADP front-end? The simplest method is to simply use your existing MDB front-end with ODBC linked tables in SQL Server - that usually requires almost no changes in the Access front-end. Unless you have serious performance problems, you can still use your Access queries, forms and reports. As Charlotte points out, there isn't a simple way to pass parameter values from Access to SQL Server stored procedures.
    Wendell

  5. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Charlotte and Hans,

    Thanks for the response.

    RE: and what object model are you using, DAO or ADO?
    Using DAO, and linking to the SQL tables. I took a copy of the original mdb, deleted the Access tables, and then linked to the SQL tables.

    RE:What exactly do you want to do
    Yes, I am using an mdb front end.
    All reports are based on queries, not the actual tables. The front end presents a form to the user, where the user can select a range of dates, and/or department, and/or billing type, etc. These selections are made through combo box and text box controls which are referenced in the queries as criteria,
    (for example date field criteria in the report query is "Between forms!frmReports!txtReportStartDate AND forms!frmReports!txtReportEndDate")

    Once these parameters are defined, the user then selects one or more reports/graphs to create and then previews or prints them.

    When I was not getting the expected results, I copied the SQL statement from Access and pasted it into SQL Enterprise Manager which was kind enough to let me know it didn't like "!". In Enterprise Manager, I can change the form based parameters to values and all works well. So it seems my challenge is to find a method of passing parameters to SQL by some means other than the form controls. (I think).

    Does this help?

    Thanks again for your ideas.

    Ken

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

    Re: SQL Backend (2k)

    If you're using queries, you shouldn't be having problems. If you're trying to use *converted* queries, now stored procedures, you can't do it that way. Access queries should run as they always have. SQL Server stored procedures converted from Access queries have to be done differently. That was my question, which one are you trying to use?
    Charlotte

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

    Re: SQL Backend (2k)

    <hr>When I was not getting the expected results . . . <hr>
    What do you mean by that statement - did you get error messages, did you get results but they didn't match your expectations, or was the performance slower than before?
    Wendell

  8. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Charlotte,

    Sorry about not understanding your question. Let first make certain I have explained what we have done. We used Microsoft Enterprise Manager to import the tables (and only the tables) into an SQL database. I have left the front end, including queries, forms, reports, etc untouched. I didn't convert any queries. I deleted the Access tables from the mdb, and then linked to the SQL tables. The only change I made after linking to the tables was to remove the "dbo_" prefix so the tables would have exactly the same names.

    Let me also further clarify the "using queries" as data source for the reports. I did not convert any queries, only the tables. The queries are in Access AND use selection criteria that are provided from the frmReport form. The following is one example, where I have copied the SQL from Access and pasted it below:
    SELECT tblInstant.strHier1, tblInstant.strHier2, tblInstant.strLocation, tblInstant.strPID, tblInstant.strCaseNo, tblInstIns.strClaimNo, tblInstant.dtmInstDate, tblInstIns.curIMR, tblInstIns.curRMR, tblInstIns.curMC, tblInstIns.logMd, tblInstant.strCompany, ([curMC]+[curInd]+[curLgl]+[curPC]+[curOC]+[curRMR]+[curRIR]+[curRLR]+[curRPR]) AS [Total Cost]
    FROM tblInstant LEFT JOIN tblInstIns ON tblInstant.strCaseNo = tblInstIns.fk_CaseNo
    WHERE (((tblInstant.dtmInstDate) Between [Forms]![frm_Report]![txtReportStartDate] And [Forms]![frm_Report]![txtReportEndDate]) AND ((tblInstant.strCompany)=[Forms]![frm_Report]![cboCompany]) AND ((([curMC]+[curInd]+[curLgl]+[curPC]+[curOC]+[curRMR]+[curRIR]+[curRLR]+[curRPR]))>=[Forms]![frm_Report]![txtMinCost])) OR (((tblInstant.dtmInstDate) Between [Forms]![frm_Report]![txtReportStartDate] And [Forms]![frm_Report]![txtReportEndDate]) AND ((([curMC]+[curInd]+[curLgl]+[curPC]+[curOC]+[curRMR]+[curRIR]+[curRLR]+[curRPR]))>=[Forms]![frm_Report]![txtMinCost]) AND (([Forms]![frm_Report]![cboCompany]) Is Null));

    The WHERE part is what is causing the problem. If for example, the cboCompany control is empty, then the query will return ALL companies. If the total of the sum of various fields is less than "txtMinCost" control then they will not be included in the returned records, etc.

    Have I made it easier or more difficult with the above explanation?

    Based on your post, it looks like I need to begin reading up on "stored procedures".

    Thanks again for your support.

    Ken

  9. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Wendell,
    Running the report, which was a summary report, resulted in WAY too many dollars, and WAY too many instances in the count fields. So when I ran just the query while inside Access, the query ended up with 6-8 records where there should have been but one. I copied the SQL and pasted it into the SQL window in Enterprise Manager (just to see what gives) and that is when it told me that "!" are not allowed.

    I then modifed the SQL statement in Enterprise Manager using hard values (like actual dates, and "true" for logical fields, etc.) and the correct number of records is returned. That is what I meant by un"expected results"

    In case it is not obvioius, I have not worked in SQL Server before. I have used SQL statements in Foxpro and Access, but not worked in SQL Server. My hope, though probably unrealistic, was that I could simply convert the Access tables to SQL tables and use everything else in the mdb as is.

    Thanks for your help.

    Ken

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

    Re: SQL Backend (2k)

    True dates in SQL Server are not the same as Access table dates. Are you converting the dates in a SQL Server view? Otherwise, that's probably why your query is not working. You can't just work with SQL Server date fields from an Access query.
    Charlotte

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

    Re: SQL Backend (2k)

    Your respones to Charlotte and me help considerably. In general, you should be able to do exactly what you describe - that is upsize tables to SQL Server and keep on working with your existing queries. There are differences however in the SQL syntax used between Access and SQL Server. BTW, it would be useful to know what version of SQL Server you are using - is it 2000?

    There is a pretty steep learning curve when switching to using stored procedures, so I wouldn't rush into developing stored procedures to try to solve a problem. It sounds like your issue has to do with when the combo box or text boxes contain nulls. There are some subtle differences in the way that Nulls are treated between SQL Server and Access, but I don't think they apply here. In any event the ODBC driver you are using should do the translation. Another question - are all the fields in the expression ((([curMC]+[curInd]+[curLgl]+[curPC]+[curOC]+[curRMR]+[curRIR]+[curRLR]+[curRPR]))>=[ always filled in - that is could some of them be null? If so, that could cause a problem. If you have things filled in in all the the combo and text boxes, do you get the expected results?
    Wendell

  12. #11
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Wendell,

    Thanks for the follow up. I am sorry I am so late responding, but I am traveling with limited internet access.

    RE: SQL Server Version
    Yes, I am using SQL SErver 2000.

    RE: ((([curMC]+[curInd]+[curLgl]. . . +[curRPR]))>=[ always filled in?
    Yes, nulls are not allowed in these fields, either a value or a zero will be in these fields.

    RE:Stored procedure steep learning curve
    Is there anything in the Realm of Microsoft that does not involve a steep learning curve? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I will continue to plug away and I appreciate your help.

  13. #12
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Charlotte,

    That might be part of the problem. I am rechecking that now, and trying to be more methodical.

  14. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,496
    Thanks
    3
    Thanked 42 Times in 42 Posts

    Re: SQL Backend (2k)

    We still haven't established whether you are getting all the right data when you fill in all the fields on your selection form - if that works correctly, then the issue is the treatment of nulls. On the other hand, if it doesn't work correctly, then the dates may not be properly recognized as Charlotte suggests.

    In general, as noted earlier, the ODBC driver layer should be converting Access SQL statements to SQL Server statements, and you as a user shouldn't have to worry about those issues. The issue with stored procedures is that you really need to learn Transact SQL, which is quite different from most procedural languages, and has very limited branching and looping constructs. It's sort of like going from VB to C#. There is a method of passing parameters to stored procedures, but it involves using Pass Through queries, and redefining the query using QueryDefs each time the query is executed. To use it pretty much requires that you have your own dedicated front-end on your workstation, and that you fully understand the Access Object Model. I would strongly recommend you debug the situation and figure out exactly what is happening with your query before you consider trying to write stored procedures to solve the problem. In my experience, the only time pass-through queries are a necessity is when you can't get the performance you want from Access queries using ODBC.

    One other suggestion - there is the ability to create a detailed trace of the dialog between Access and SQL Server by turning on the tracing option in the ODBC Data Source Administrator (it may have a slightly different name depending on the OS running on the workstation). That will let you see the SQL string being passed from Access to ODBC, and what the ODBC driver creates as a SQL string to SQL Server. Hope this helps - I understand the traveling issue.
    Wendell

  15. #14
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Wendell,

    Thanks for following up. I am out of town this week, and don't have access to the project. I will not be able to answer your question until this weekend. Please be patient with me. I appreciate your efforts and ideas.

    Ken

  16. #15
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SQL Backend (2k)

    Wendell, Charlotte, et al.
    Well you were right. The problem was the way I imported the tables. Got it all fixed now, and I am sort of likin' this SQL Server stuff. Now for what is probably the dumbest question you have ever heard:

    "How does a person get SQL Server 2000 tables deployed from a development computer to the customer's computer?"
    This was done in-house and tested successfully, now eye knead two get the work product onto their SQL server from mine.
    NOTE: There is no broadband internet connection from the pc running SQL Server at this time.

    Any ideas are greatly appreciated.

    TIA,

    Ken

Page 1 of 2 12 LastLast

Posting Permissions

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