SQL Backend (2k)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Charlotte,

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

Reply | Quote

Charlotte,

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

[indent]

---

When I was not getting the expected results . . .

---

[/indent]
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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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?

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Well, there are at least a couple of ways. One is to do a backup to a file from SQL Server, then take that file and copy it to a CD-ROM, take the CD-ROM to the customer PC, copy the file to their hard drive, and then do a SQL Server Restore. That way you end up with a new SQL Server database on the customer server. If you want to embed it into an existing database, it gets a bit more complicated. In that situation you would need to export each table, probably to a delimited text format, store the individual files on an appropriate medium, transport them, and then import into the selected SQL Server database. In the first situation, you will need to read up on the SQL Server Backup (also called Dump) and Restore commands. In the second case, the Enterprise Manager and the Import/Export tool can be used. Hope this helps.

Reply | Quote

If you have Access loaded on the target SQL Server machine, you can do it from within Access, using the Tools – Database Utilities backup and restore commands. For someone new to SQL Server, this can be easier. You’ll need sa level access on the target (restore) machine. This method also avoids some of the bugs other methods exhibit with extended properties and doesn’t require the two machines to be running under a domain account, like DTS.

Keith

Reply | Quote

If you have Access loaded on the target SQL Server machine, you can do it from within Access, using the Tools – Database Utilities backup and restore commands. For someone new to SQL Server, this can be easier. You’ll need sa level access on the target (restore) machine. This method also avoids some of the bugs other methods exhibit with extended properties and doesn’t require the two machines to be running under a domain account, like DTS.

Keith

Reply | Quote

Wendell,

Option 1 worked great! THANKS for all your help.

Thanks to Charlotte and kastorff as well!

Ken

Reply | Quote

Wendell,

Option 1 worked great! THANKS for all your help.

Thanks to Charlotte and kastorff as well!

Ken

Reply | Quote

Well, there are at least a couple of ways. One is to do a backup to a file from SQL Server, then take that file and copy it to a CD-ROM, take the CD-ROM to the customer PC, copy the file to their hard drive, and then do a SQL Server Restore. That way you end up with a new SQL Server database on the customer server. If you want to embed it into an existing database, it gets a bit more complicated. In that situation you would need to export each table, probably to a delimited text format, store the individual files on an appropriate medium, transport them, and then import into the selected SQL Server database. In the first situation, you will need to read up on the SQL Server Backup (also called Dump) and Restore commands. In the second case, the Enterprise Manager and the Import/Export tool can be used. Hope this helps.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

[indent]

---

When I was not getting the expected results . . .

---

[/indent]
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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote