Stored procudure problem
I have the following stored procedure
CREATE PROCEDURE [MONA].[spCarrierInfo]
@CarrierCode nvarchar
AS Select *
from tblCarrierEquipment
where CC = @Carriercode
GO
Then I call it from within MS Access with a Pass Through query with the DSN, which does work because I can do regular queries and get the results.
well I call the SP like this:
exec [MONA].[spCarrierInfo] @CarrierCode= 'OTITEN'
I get no errors but neither do I get any result sets. Any ideas?
CREATE PROCEDURE [MONA].[spCarrierInfo]
@CarrierCode nvarchar
AS Select *
from tblCarrierEquipment
where CC = @Carriercode
GO
Then I call it from within MS Access with a Pass Through query with the DSN, which does work because I can do regular queries and get the results.
well I call the SP like this:
exec [MONA].[spCarrierInfo] @CarrierCode= 'OTITEN'
I get no errors but neither do I get any result sets. Any ideas?
Scott McDaniel (EE MVE )
Can you provide the full code segment where you call this? The SP returns a recordset, so you'd have to have a variable available to catch that.
Hi,
looks OK for me. Only some little things: You should add the schema to the table name in your SP to make sure that you always get the right table. You should also always enter the length of a datatype in the declaration, not only "nvarchar" which then automatically is 50. You also should ALWAYS use an exact field list in SPs and NEVER the *, because SQL Server "compiles and forgets" your SP in the moment where you execute the "CREATE/ALTER PROCEDURE" command. What happens is that SQL Server sees the "*", loads all known fields and compiles the command with these fields. If you later change/add/remove a column in the table this change will NOT affect such queries. You must open it again and execute the "ALTER PROCEDURE" command again so that the current list of columns is used. So there is a big risk that you think "everything is OK with my query" but in fact it isn't. It is also a form of documentation that you list all the needed fields (and only the needed, never add fields to your SELECT which you don't need in the result). I recommend to use "SQL Search" from redgate in SQL Server Management Studio which is a free tool to later search for all views/SPs/UDFs and so on where you used a column to change it instead of the "*" which produces errors.
You can get the tool here (SQL Search from Redgate)
Then it depends which is the way you execute the SP in Access. Maybe you has defined a parameter and pass the parameter in such a way that the result ist '' (two single quotes) instead of the value you passed. Can you show the code you used to execute the query?
The first thing you should always try in these cases is to execute the query in SQL Server Management Studio to see if it works and to see if the parameter you thought you have sent to the query really gives the result you want.
Next method is to start the SQL Server Profiler (which is part of the full version of SQL Server Management Studio, not included in SQL Server Express) which shows you exactly what happens when you execute the query in Access so you can see in detail what command(s) Access sends to the server and what's the response. As you did not get an error I guess it is something with the parameter where the value you want don't reaches the server.
Keep in mind that schemas in SQL Server belongs to a specific user, this was separated beginning with SQL Server 2005. So maybe here's also a problem of access rights but in this case you normally get an error.
Cheers,
Christian
looks OK for me. Only some little things: You should add the schema to the table name in your SP to make sure that you always get the right table. You should also always enter the length of a datatype in the declaration, not only "nvarchar" which then automatically is 50. You also should ALWAYS use an exact field list in SPs and NEVER the *, because SQL Server "compiles and forgets" your SP in the moment where you execute the "CREATE/ALTER PROCEDURE" command. What happens is that SQL Server sees the "*", loads all known fields and compiles the command with these fields. If you later change/add/remove a column in the table this change will NOT affect such queries. You must open it again and execute the "ALTER PROCEDURE" command again so that the current list of columns is used. So there is a big risk that you think "everything is OK with my query" but in fact it isn't. It is also a form of documentation that you list all the needed fields (and only the needed, never add fields to your SELECT which you don't need in the result). I recommend to use "SQL Search" from redgate in SQL Server Management Studio which is a free tool to later search for all views/SPs/UDFs and so on where you used a column to change it instead of the "*" which produces errors.
You can get the tool here (SQL Search from Redgate)
Then it depends which is the way you execute the SP in Access. Maybe you has defined a parameter and pass the parameter in such a way that the result ist '' (two single quotes) instead of the value you passed. Can you show the code you used to execute the query?
The first thing you should always try in these cases is to execute the query in SQL Server Management Studio to see if it works and to see if the parameter you thought you have sent to the query really gives the result you want.
Next method is to start the SQL Server Profiler (which is part of the full version of SQL Server Management Studio, not included in SQL Server Express) which shows you exactly what happens when you execute the query in Access so you can see in detail what command(s) Access sends to the server and what's the response. As you did not get an error I guess it is something with the parameter where the value you want don't reaches the server.
Keep in mind that schemas in SQL Server belongs to a specific user, this was separated beginning with SQL Server 2005. So maybe here's also a problem of access rights but in this case you normally get an error.
Cheers,
Christian
Make sure the Query object property named "Returns Records" is set to Yes. The property can be found on the Properties dialog when viewing the Query object in SQL View.
In addition to the Bitsqueezer's above comments...
You're missing the parentheses marks around the parameters in your SP
The SP may be correctly returning zero rows, which means you'll want to think about what it should do in this case, maybe return a single string that says 'There are no rows...'
Also, in Access, you'll need to pass single quotes around your string. In VBA..
sSQL = "exec [MONA].[spCarrierInfo] 'OTITEN'"
You're missing the parentheses marks around the parameters in your SP
CREATE PROCEDURE [MONA].[spCarrierInfo]
(@CarrierCode nvarchar)
AS Select *
from tblCarrierEquipment
where CC = @Carriercode
GOThe SP may be correctly returning zero rows, which means you'll want to think about what it should do in this case, maybe return a single string that says 'There are no rows...'
Also, in Access, you'll need to pass single quotes around your string. In VBA..
sSQL = "exec [MONA].[spCarrierInfo] 'OTITEN'"
Hi,
@jimhorn: that's not correct, you don't need the parantheses around the parameters list. This is only cosmetic but not needed as you can see when you create a new stored procedure using the SQL Server Management Studio with "New Stored Procedure". The template also always creates an SP without them.
Cheers,
Christian
@jimhorn: that's not correct, you don't need the parantheses around the parameters list. This is only cosmetic but not needed as you can see when you create a new stored procedure using the SQL Server Management Studio with "New Stored Procedure". The template also always creates an SP without them.
Cheers,
Christian
LSMConsulting ...
"The SP returns a recordset, so you'd have to have a variable available to catch that. "
Maybe I am not following you, but the SP does not need a variable to return a row set. Nor does the Access pass through Query object. The Access Query object just needs to have Returns Records set to yes so the Access UI can receive the rowset returned.
"The SP returns a recordset, so you'd have to have a variable available to catch that. "
Maybe I am not following you, but the SP does not need a variable to return a row set. Nor does the Access pass through Query object. The Access Query object just needs to have Returns Records set to yes so the Access UI can receive the rowset returned.
ASKER
DatAdrenaline.......
The Return Records is set to yes. I use that pass through query to return a record set on this query:
Select CC from tblCarrierEquipment where CC = 'OTITEN'
It returns the record correctly.
The reason I am trying to do the SP is because I read and heard that if I do as much on the BE the better. Am I correct?
The Return Records is set to yes. I use that pass through query to return a record set on this query:
Select CC from tblCarrierEquipment where CC = 'OTITEN'
It returns the record correctly.
The reason I am trying to do the SP is because I read and heard that if I do as much on the BE the better. Am I correct?
ASKER
LMSConsulting....
I do not have a procedure. I am in the development stage right now so am just trying to get my pass through query to return the records. When I get that done I will work on a procedure to write the query, using query defs.
I do not have a procedure. I am in the development stage right now so am just trying to get my pass through query to return the records. When I get that done I will work on a procedure to write the query, using query defs.
"The reason I am trying to do the SP is because I read and heard that if I do as much on the BE the better. Am I correct? "
In general, the more you can get the SQL Server to do for you the better off you are. However, with this scenario, the simpleness of what you are seeking does not warrant an SP object. You can simply create a Passthrough Query object with the SELECT statement ...
SQL View of the Passthru Query object:
SELECT * FROM tblCarrierEquipment WHERE CC = 'OTITEN'
You see with a "Passthrough" query object the Jet/ACE database engine passes the SQL statement directly to the server that will be processing the command/query (thus the name 'Passthrough'). That is why the SQL dialect for a PT Query object must be the dialect that the back end understands.
You can use VBA to modify the SQL View of the PT Query object fairly simply with VBA in order to 'parameterize' the query you pass to the back end. A core part of that code would set the .SQL property of the Query object ..
---
Hope that helps, if you have more questions, please don't hesitate to post!
In general, the more you can get the SQL Server to do for you the better off you are. However, with this scenario, the simpleness of what you are seeking does not warrant an SP object. You can simply create a Passthrough Query object with the SELECT statement ...
SQL View of the Passthru Query object:
SELECT * FROM tblCarrierEquipment WHERE CC = 'OTITEN'
You see with a "Passthrough" query object the Jet/ACE database engine passes the SQL statement directly to the server that will be processing the command/query (thus the name 'Passthrough'). That is why the SQL dialect for a PT Query object must be the dialect that the back end understands.
You can use VBA to modify the SQL View of the PT Query object fairly simply with VBA in order to 'parameterize' the query you pass to the back end. A core part of that code would set the .SQL property of the Query object ..
CurrentDb.QueryDefs("nameOfPTQuery").SQL = strSQL---
Hope that helps, if you have more questions, please don't hesitate to post!
ASKER
Christian,
How do I test the procedure in SQL Server Enterprise Manager? In 2000 it only has a button to check the syntax and when I do that it says it is OK.
How do I test the procedure in SQL Server Enterprise Manager? In 2000 it only has a button to check the syntax and when I do that it says it is OK.
ASKER
dat......
What would one use a Stored Procedure for if I can just create a passthrough to return the records I want?
What would one use a Stored Procedure for if I can just create a passthrough to return the records I want?
I may have missed something in the thread of this but if you declare a varchar with specifying a length does the length default to 1 character?
In which case shouldnt the code look a little more like this?
In which case shouldnt the code look a little more like this?
CREATE PROCEDURE [MONA].[spCarrierInfo]
@CarrierCode nvarchar(255)
AS Select *
from tblCarrierEquipment
where CC = @Carriercode
GO
"How do I test the procedure in SQL Server Enterprise Manager?"
I am not Bitsqueezer, but in SSMS you can do a right click on the SP object, then choose "Execute Stored Procedure" then a dialog will pop up that asks for values for your parameters.
Another option is to open a New Query and write the same command you were using for the PT Query object in the Access UI ...
EXEC yourSPName @parm1=someValue
Then hit F5 (equivalent to the !Execute button) which will execute the T-SQL in the query window and post the resultset in the bottom of the window.
I am not Bitsqueezer, but in SSMS you can do a right click on the SP object, then choose "Execute Stored Procedure" then a dialog will pop up that asks for values for your parameters.
Another option is to open a New Query and write the same command you were using for the PT Query object in the Access UI ...
EXEC yourSPName @parm1=someValue
Then hit F5 (equivalent to the !Execute button) which will execute the T-SQL in the query window and post the resultset in the bottom of the window.
Meant to say without specifying a length in my previous post.... sorry.
"I may have missed something in the thread of this but if you declare a varchar with specifying a length does the length default to 1 character?"
Good catch! I always get confused because the default length depends on the context. I think the default length is 30 when used in CAST and CONVERT, and 1 (as you state) with declarations.
Good catch! I always get confused because the default length depends on the context. I think the default length is 30 when used in CAST and CONVERT, and 1 (as you state) with declarations.
"What would one use a Stored Procedure for if I can just create a passthrough to return the records I want?"
Remember a Stored Procedure and be a huge block of T-SQL code that does all kinds of things to your database. Its analogous, in a way, to a User Defined Procedure in VBA. So you may have perform operations on your data before you can return a row set. In those cases, you will want an SP in the back end, and issue the EXEC spName in an Access PT Query object. But if you are just filtering your data, and you don't need the return set of data editable, then it is my opinion that there is no need for an SP --- just issue the SQL statement to the backend db engine using a PT Query object. In addition, I often use PT Query objects for DELETE and INSERT statements as well.
If you need the data editable, then I would suggest creating a Linked Table object, then using that linked table as your Record Source. Jet/ACE and ODBC get along very well and if your filters can be converted to native backend dialect by the ODBC driver, then you can get editable data with about the same speed as a PT Query object (which returns read only data).
Querying against Linked Table objects get time consuming if your criteria has to be evaluated on the client side due to the use of an VBA function or something like that.
Remember a Stored Procedure and be a huge block of T-SQL code that does all kinds of things to your database. Its analogous, in a way, to a User Defined Procedure in VBA. So you may have perform operations on your data before you can return a row set. In those cases, you will want an SP in the back end, and issue the EXEC spName in an Access PT Query object. But if you are just filtering your data, and you don't need the return set of data editable, then it is my opinion that there is no need for an SP --- just issue the SQL statement to the backend db engine using a PT Query object. In addition, I often use PT Query objects for DELETE and INSERT statements as well.
If you need the data editable, then I would suggest creating a Linked Table object, then using that linked table as your Record Source. Jet/ACE and ODBC get along very well and if your filters can be converted to native backend dialect by the ODBC driver, then you can get editable data with about the same speed as a PT Query object (which returns read only data).
Querying against Linked Table objects get time consuming if your criteria has to be evaluated on the client side due to the use of an VBA function or something like that.
ASKER
I want to thank everybody here for your help. I have learned quite a bit from all of your input. EvilPostIt gave the solution. I specified the length and it works now. But I really appreciate all your help as well. So for EE etiquette should I award all the points to EvilPostIt?
ASKER
dat............
I am going to have FE in remote locations so I am trying to minimize network traffic and therefore make the FE faster. If I link the tables on the BE will that bring all of the table records over at once thus creating a large amount of network traffic? I was just going to use the PT for insert, update and delete queries. Thoughts?
I am going to have FE in remote locations so I am trying to minimize network traffic and therefore make the FE faster. If I link the tables on the BE will that bring all of the table records over at once thus creating a large amount of network traffic? I was just going to use the PT for insert, update and delete queries. Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What would one use a Stored Procedure for if I can just create a passthrough to return the records I want?
Once you begin to understand what your BE can do, you can get a feel for what you'd like to use a Stored Procedure for. In general, you use Stored Procedures to execute complex T-SQL language commands. In my case, I have a report that collates, sums and totals a lot of data on a given time interval. In Access I would have to code a lot of temp tables, VBA to pull a lot of data into them, and more VBA to join and compare them. All that data would have to come across the network and then be processed by VBA.
In the stored procedure, I can declare table variables, fill them, index them, join them and return only the results to MS Access. It is much faster.
For what you have as an example, there will be negligible performance between a straight-up Access query of a linked table, an Access query of a linked view, a passthrough Select query or a passthrough stored procedure request.
ASKER
You are correct. you did state that at the beginning. I did not know what that meant until all the further education you gave as the thread grew. Thank you so very much for all your help. It seems as though I have quite a bit to learn about MSSQL.
"As far as I know the default length is 50, not 30 or 1, but in case of SQL Server 2000 it maybe is another length - but even because of such problems it should always be explicitly set."
I agree that it should be explicitly set. But as an FYI, the default length for SQL Server 2012 is 30 and 1 as I had suggested.
http://msdn.microsoft.com/en-us/library/ms176089.aspx
"Pass-Through-Query: Please keep in mind that this will return a recordset that is NOT updatable."
True, but remember you can use an unbound form, populate controls with the results of a PT Query object, then use PT queries for INSERT/UPDATE/DELETE. I will often manipulate the SQL statement of one or two PT query objects with VBA so the PT query object is not really bound to a particular table or view, I use it more for its connection. I often have one that returns records and one that doesn't (for action queries).
"If you do not want to use a linked table and Access SQL (which would be possible here as this query is very simple) then you must create an ADO recordset in the Form_Load event and assign the result from the server (the recordset) to the Recordset property of the form (which can work with either DAO or ADO recordsets)."
Partially agree -- I don't like to use the absolute of 'must use ADO'. Remember you don't need linked table objects or ADO to have an editable recordset from a SQL Server back end. As a matter of fact you can use a "fully qualified" Jet/ACE syntax and get editability as well as speed.
SELECT * FROM [ODBC;DRIVER=SQL Server ... etc ...].[dbo.SomeTable] WHERE ...etc ...
I use that syntax a lot in the RecordSource property of a Form object in order to "semi-protect" my raw tables in the back end (in some environments at least).
"Changing the SQL code of a stored query in Access with the "SQL" property of the QueryDef is not recommended as it destroys the only advantage of a stored query: The compilation of the SQL command. That doesn't matter in case of a Pass-Through-Query as it is always executed as dynamic SQL but on the other hand a PT-Query has no advantage if you always must change the SQL command, in this case it is very much easier to use an ADO recordset and send the SQL command directly to the server - and it is updatable."
I disagree with you there. Modifying the SQL of a QueryDef is completely legit and indeed recommended. There are many scenarios in which the schema of the BE can not be modified so Views or SP's can not be added or modified, so to get server level queryability and speed (without creating a companion db to put custom objects in), PT's are GREAT! -- it does not take SQL Server long to compile a dynamic SQL and in the case as presented, PT query and modifying the SQL property is a valid option in the development process. Also, keep in mind that OLE DB support is being dropped, so ODBC is king with SQL Server, so using the ODBC provider with ADO will have to be done ... To me it often just makes sense to use the PT query object for simplicity and a reduction of code to manage. In addition, Access Form objects REALLY like DAO!
"The fact that PT-Queries doesn't support parameter assignment and that they are not updatable makes them very useless."
As you may have guessed --- I disagree! :) --- Agreeably, of course, but PT query objects are very useful -- especially with Reporting from Access!
"Access doesn't simply download all records, it uses some Microsoft specific stored procedures in the background to handle such tasks so it is an optimized query"
Indeed. Check out the following old discussion on UtterAccess regarding the efficiency of Linked Table objects.
http://www.utteraccess.com/forum/index.php?s=&showtopic=1492849&view=findpost&p=1492977
"(BTW: You should always use at least views and never link Acces directly to a SQL Server table, especially in case of MDB/ACCDB with the linked table design, but that's another story.)"
I whole heartedly disagree with that! Each scenario is different and indeed that statement may apply for one scenario, but not an overall Access/SQL Server application development strategy.
I agree that it should be explicitly set. But as an FYI, the default length for SQL Server 2012 is 30 and 1 as I had suggested.
http://msdn.microsoft.com/en-us/library/ms176089.aspx
"Pass-Through-Query: Please keep in mind that this will return a recordset that is NOT updatable."
True, but remember you can use an unbound form, populate controls with the results of a PT Query object, then use PT queries for INSERT/UPDATE/DELETE. I will often manipulate the SQL statement of one or two PT query objects with VBA so the PT query object is not really bound to a particular table or view, I use it more for its connection. I often have one that returns records and one that doesn't (for action queries).
"If you do not want to use a linked table and Access SQL (which would be possible here as this query is very simple) then you must create an ADO recordset in the Form_Load event and assign the result from the server (the recordset) to the Recordset property of the form (which can work with either DAO or ADO recordsets)."
Partially agree -- I don't like to use the absolute of 'must use ADO'. Remember you don't need linked table objects or ADO to have an editable recordset from a SQL Server back end. As a matter of fact you can use a "fully qualified" Jet/ACE syntax and get editability as well as speed.
SELECT * FROM [ODBC;DRIVER=SQL Server ... etc ...].[dbo.SomeTable] WHERE ...etc ...
I use that syntax a lot in the RecordSource property of a Form object in order to "semi-protect" my raw tables in the back end (in some environments at least).
"Changing the SQL code of a stored query in Access with the "SQL" property of the QueryDef is not recommended as it destroys the only advantage of a stored query: The compilation of the SQL command. That doesn't matter in case of a Pass-Through-Query as it is always executed as dynamic SQL but on the other hand a PT-Query has no advantage if you always must change the SQL command, in this case it is very much easier to use an ADO recordset and send the SQL command directly to the server - and it is updatable."
I disagree with you there. Modifying the SQL of a QueryDef is completely legit and indeed recommended. There are many scenarios in which the schema of the BE can not be modified so Views or SP's can not be added or modified, so to get server level queryability and speed (without creating a companion db to put custom objects in), PT's are GREAT! -- it does not take SQL Server long to compile a dynamic SQL and in the case as presented, PT query and modifying the SQL property is a valid option in the development process. Also, keep in mind that OLE DB support is being dropped, so ODBC is king with SQL Server, so using the ODBC provider with ADO will have to be done ... To me it often just makes sense to use the PT query object for simplicity and a reduction of code to manage. In addition, Access Form objects REALLY like DAO!
"The fact that PT-Queries doesn't support parameter assignment and that they are not updatable makes them very useless."
As you may have guessed --- I disagree! :) --- Agreeably, of course, but PT query objects are very useful -- especially with Reporting from Access!
"Access doesn't simply download all records, it uses some Microsoft specific stored procedures in the background to handle such tasks so it is an optimized query"
Indeed. Check out the following old discussion on UtterAccess regarding the efficiency of Linked Table objects.
http://www.utteraccess.com/forum/index.php?s=&showtopic=1492849&view=findpost&p=1492977
"(BTW: You should always use at least views and never link Acces directly to a SQL Server table, especially in case of MDB/ACCDB with the linked table design, but that's another story.)"
I whole heartedly disagree with that! Each scenario is different and indeed that statement may apply for one scenario, but not an overall Access/SQL Server application development strategy.
Hi datAdrenaline,
I must say that I mainly work with SQL Server 2005 so yes, in 2012 that could be the fact. In 2005 it defaults to 50 (as far as I remember, I don't use declarations without specifying length in my daily work...:-) ). But that doesn't matter, the point was not the real default length, only that SQL Server chooses one on it's own if you specify none.
PT-Queries and unbound forms: Yes, could be an alternative - but I personally prefer to use as much of the out of the box features of Access instead building everything as new what Access can do itself in a bound form. I really don't know why Microsoft did not allow updatable PT-Queries and on the top: conversion of Access formed parameters to parameters for SPs for example. The joke is that in ADPs (which I normally use for development) exactly this is possible: You can assign an SP as RecordSource and then you have an additional "Input Parameters" property (only exists in ADPs) in the form which you can use to assign SP parameters simply in the form of "@MyParameter = Forms!MyForm!MyField" for example (I don't use "Forms" links, just to show that the assignment of SP parameters to Access links is no problem there). So why didn't they offer this possibility for PT queries, it's all in the code...?
If someone needs unbound forms (maybe in case of temporary offline work) then I would use ADO for that. ADO supports a kind of batch mode similar to ADO.NET with their Datasets and so you can load a recordset into ADO, work completely offline by setting the connection object to nothing and then you can go on inserting, deleting and updating any data offline. When you're back in the network then the batch mode does all the change with the backend and in case of conflicts, offers methods to react. So indeed you don't need to code any INSERT, UPDATE, DELETE manually.
"Must use": Maybe a little bit misunderstanding. I did not want to say that this is the only alternative. I want to say: If you want an alternative for using a PT query (in other words: another method to directly send a T-SQL-command to SQL Server) the ADO is the only alternative. You can of course use many other methods like DAO and linked tables/views or your trick with the direct ODBC command. But there's no other method to send real T-SQL commands (with all their complexity like CTEs or so) to SQL Server as to use ADO instead of PT queries - that was what I wanted to say.
I personally hate the DAO method to access SQL Server in general so I try to avoid it wherever possible - that's one of the reasons why I use ADPs only which works perfectly with SQL Server (but unfortunately only up to A2010/SQL Server 2008 because MS thinks, Access should better be a "Office program" for typical Office users and not a professional development system anymore - see the silly Macros).
DAO has the fat disadvantage that everything must be converted to the datatypes of DAO, one of the reasons why you need to add a timestamp column to each and any table which should work with DAO to be sure that you don't get the error "another user has changed...". There are a lot other issues which you don't have with ADO, the only advantage is that you can directly create queries through a lot of different databases - but that is no real advantage because you can do the same trick you presented for DAO ODBC form queries with ADO also.
Moreover the SELECT FROM ODBC trick is no advantage as that's exactly the same as linking the table in the TableDefs and query that linked table. Because any of the linked tables have their own ODBC connection string (you can with a little bit patience indeed link stored procedures by manipulating the ODBC string). The difference is only that you must change the TableDefs links by VBA if you need another target - in a form's SELECT you would do it there, so doesn't make a difference.
Changing the "SQL" property: What I meant was: Changing the SQL property in normal queries is not recommended because they are compiled locally in a similar way as SPs/Views in SQL Server will be compiled. And I said that for PT queries it makes no difference if you send an ADO SQL String or change a PT query: In both cases you have dynamic SQL and that always means: No use of execution plan in the backend (for our luck SQL Server is more intelligent than Access and caches SQL commands/execution plans from the same connection and reuses execution plans which are not too old).
But yes, reusing of PT queries is on this light the same as changing the SQL String in an ADO connection. But that's what I meant: As it has no advantage it has no real use.
And Access forms like ADO the same way because of the existance of ADPs. I only don't know if that is also the case anymore in A2013 where ADPs are dropped so maybe Microsoft will drop the support for ADO also in some time...who knows. Access is getting more and more useless, if >A2013 will be like that I think that's the point for me to leave Access and go on to .NET or other frontends.
OLEDB will be dropped: Yes, why not, but in ADO i usually use SQLNCLI which will not be dropped (but don't say that too loud, maybe Microsoft changes it's mind tomorrow...hehe... I read a funny roadmap article from MS these days from 2011 I think where they stated that you should not use DAO anymore, the version 3.6 is the last one and there will be no version in 64 bit Windows versions... OK, they call the library not "DAO" anymore in the reference list, but in fact it's DAO, you know that. So we see what we can believe in things Microsoft says about the future...). The only small disadvantage of SQLNCLI is that you must install the driver on the target computer.
PT reporting: Why should that be better than any other method (even DAO) to create reports?
SP in the background: Yes, that's what I meant. Only a small correction: You said there that "ODBC did create the SPs" which is not the case. These "prepare" and so on SPs are initial part of any SQL Server installation, are always available (you can use it on your own if you want, but has no advantages for personal use). But maybe I got you wrong with this statement, I didn't read the whole thread.
Views instead of tables: It is only a recommendation of me, not a must, not a design necessity. The big advantage is that views are commonly faster in access than tables (mostly because it's not the complete table and a view is a compiled query with execution plan, moreover a view can have it's own indices and so on). But the main design advantage beside that is that you restrict the direct access to the tables and you can completely hide ALL tables and the table structure against any user. That's not only a security advantage, it also offers the possibility to keep the frontend working even if you change for example a table column name. You only need to add an alias with the old name to the view and the frontend can go on working. That allows you to prepare changes to the backend before you roll out a new frontend. You can even exchange the complete table by a set of others as long as the view works in the same way as before (means: is updatable like before, has the same columns and so on).
Indeed my current new project works completely without direct table and view access, I created a system of SPs which always checks if the user is allowed to do something and if his current login has not timed out. That gives a really big advantage in case of security: The frontend logs in with a standard SQL Server login which has no rights other than executing SP. If someone would hack the name and password he cannot do anything with it because if he would try to show the tables using a direct Excel connection for example or a full version of Access he would see simply nothing, not any SQL Server object.
That's what I meant: If you link the user directly to a table you allow him indirect to use the table in any other ODBC scenario. This would be even easier if Windows authentication is used - he could create a simple ODBC connection and Excel and has full access to all tables without any code of the frontend which stops him - and of course nothing in the backend, maybe trigger code, that's all.
So that is the reason why I in general don't recommend to use direct table links (also not in Access local tables for similar reasons).
But thanks for the discussion, I always like to discuss other opinions. I never would say that may way is the only one or the only right one, hope that was not the impression you got from my posting above.
Cheers,
Christian
I must say that I mainly work with SQL Server 2005 so yes, in 2012 that could be the fact. In 2005 it defaults to 50 (as far as I remember, I don't use declarations without specifying length in my daily work...:-) ). But that doesn't matter, the point was not the real default length, only that SQL Server chooses one on it's own if you specify none.
PT-Queries and unbound forms: Yes, could be an alternative - but I personally prefer to use as much of the out of the box features of Access instead building everything as new what Access can do itself in a bound form. I really don't know why Microsoft did not allow updatable PT-Queries and on the top: conversion of Access formed parameters to parameters for SPs for example. The joke is that in ADPs (which I normally use for development) exactly this is possible: You can assign an SP as RecordSource and then you have an additional "Input Parameters" property (only exists in ADPs) in the form which you can use to assign SP parameters simply in the form of "@MyParameter = Forms!MyForm!MyField" for example (I don't use "Forms" links, just to show that the assignment of SP parameters to Access links is no problem there). So why didn't they offer this possibility for PT queries, it's all in the code...?
If someone needs unbound forms (maybe in case of temporary offline work) then I would use ADO for that. ADO supports a kind of batch mode similar to ADO.NET with their Datasets and so you can load a recordset into ADO, work completely offline by setting the connection object to nothing and then you can go on inserting, deleting and updating any data offline. When you're back in the network then the batch mode does all the change with the backend and in case of conflicts, offers methods to react. So indeed you don't need to code any INSERT, UPDATE, DELETE manually.
"Must use": Maybe a little bit misunderstanding. I did not want to say that this is the only alternative. I want to say: If you want an alternative for using a PT query (in other words: another method to directly send a T-SQL-command to SQL Server) the ADO is the only alternative. You can of course use many other methods like DAO and linked tables/views or your trick with the direct ODBC command. But there's no other method to send real T-SQL commands (with all their complexity like CTEs or so) to SQL Server as to use ADO instead of PT queries - that was what I wanted to say.
I personally hate the DAO method to access SQL Server in general so I try to avoid it wherever possible - that's one of the reasons why I use ADPs only which works perfectly with SQL Server (but unfortunately only up to A2010/SQL Server 2008 because MS thinks, Access should better be a "Office program" for typical Office users and not a professional development system anymore - see the silly Macros).
DAO has the fat disadvantage that everything must be converted to the datatypes of DAO, one of the reasons why you need to add a timestamp column to each and any table which should work with DAO to be sure that you don't get the error "another user has changed...". There are a lot other issues which you don't have with ADO, the only advantage is that you can directly create queries through a lot of different databases - but that is no real advantage because you can do the same trick you presented for DAO ODBC form queries with ADO also.
Moreover the SELECT FROM ODBC trick is no advantage as that's exactly the same as linking the table in the TableDefs and query that linked table. Because any of the linked tables have their own ODBC connection string (you can with a little bit patience indeed link stored procedures by manipulating the ODBC string). The difference is only that you must change the TableDefs links by VBA if you need another target - in a form's SELECT you would do it there, so doesn't make a difference.
Changing the "SQL" property: What I meant was: Changing the SQL property in normal queries is not recommended because they are compiled locally in a similar way as SPs/Views in SQL Server will be compiled. And I said that for PT queries it makes no difference if you send an ADO SQL String or change a PT query: In both cases you have dynamic SQL and that always means: No use of execution plan in the backend (for our luck SQL Server is more intelligent than Access and caches SQL commands/execution plans from the same connection and reuses execution plans which are not too old).
But yes, reusing of PT queries is on this light the same as changing the SQL String in an ADO connection. But that's what I meant: As it has no advantage it has no real use.
And Access forms like ADO the same way because of the existance of ADPs. I only don't know if that is also the case anymore in A2013 where ADPs are dropped so maybe Microsoft will drop the support for ADO also in some time...who knows. Access is getting more and more useless, if >A2013 will be like that I think that's the point for me to leave Access and go on to .NET or other frontends.
OLEDB will be dropped: Yes, why not, but in ADO i usually use SQLNCLI which will not be dropped (but don't say that too loud, maybe Microsoft changes it's mind tomorrow...hehe... I read a funny roadmap article from MS these days from 2011 I think where they stated that you should not use DAO anymore, the version 3.6 is the last one and there will be no version in 64 bit Windows versions... OK, they call the library not "DAO" anymore in the reference list, but in fact it's DAO, you know that. So we see what we can believe in things Microsoft says about the future...). The only small disadvantage of SQLNCLI is that you must install the driver on the target computer.
PT reporting: Why should that be better than any other method (even DAO) to create reports?
SP in the background: Yes, that's what I meant. Only a small correction: You said there that "ODBC did create the SPs" which is not the case. These "prepare" and so on SPs are initial part of any SQL Server installation, are always available (you can use it on your own if you want, but has no advantages for personal use). But maybe I got you wrong with this statement, I didn't read the whole thread.
Views instead of tables: It is only a recommendation of me, not a must, not a design necessity. The big advantage is that views are commonly faster in access than tables (mostly because it's not the complete table and a view is a compiled query with execution plan, moreover a view can have it's own indices and so on). But the main design advantage beside that is that you restrict the direct access to the tables and you can completely hide ALL tables and the table structure against any user. That's not only a security advantage, it also offers the possibility to keep the frontend working even if you change for example a table column name. You only need to add an alias with the old name to the view and the frontend can go on working. That allows you to prepare changes to the backend before you roll out a new frontend. You can even exchange the complete table by a set of others as long as the view works in the same way as before (means: is updatable like before, has the same columns and so on).
Indeed my current new project works completely without direct table and view access, I created a system of SPs which always checks if the user is allowed to do something and if his current login has not timed out. That gives a really big advantage in case of security: The frontend logs in with a standard SQL Server login which has no rights other than executing SP. If someone would hack the name and password he cannot do anything with it because if he would try to show the tables using a direct Excel connection for example or a full version of Access he would see simply nothing, not any SQL Server object.
That's what I meant: If you link the user directly to a table you allow him indirect to use the table in any other ODBC scenario. This would be even easier if Windows authentication is used - he could create a simple ODBC connection and Excel and has full access to all tables without any code of the frontend which stops him - and of course nothing in the backend, maybe trigger code, that's all.
So that is the reason why I in general don't recommend to use direct table links (also not in Access local tables for similar reasons).
But thanks for the discussion, I always like to discuss other opinions. I never would say that may way is the only one or the only right one, hope that was not the impression you got from my posting above.
Cheers,
Christian
@bitsqueezer, @datAdrenaline
JDettman throws up a General Access discussion question for these kinds of things.
If you wish, it is here
https://www.experts-exchange.com/questions/28141204/Access-Experts-General-discussion-thread-for-Access-Zone-28-May-2013.html
Nick67
JDettman throws up a General Access discussion question for these kinds of things.
If you wish, it is here
https://www.experts-exchange.com/questions/28141204/Access-Experts-General-discussion-thread-for-Access-Zone-28-May-2013.html
Nick67
"But thanks for the discussion, I always like to discuss other opinions. I never would say that may way is the only one or the only right one, hope that was not the impression you got from my posting above."
I too enjoy the discussion ... your points in your last post leave me smiling in agreement and nodding my head in the familiar "affirmative" nod when people are chatting over a drink of choice. It seems we could likely go on and on with virtues of DAO, ADO, PT Query objects, the unfortunate demise of ADP's, and slight correction of each others "speaking freely" LOL!
I too enjoy the discussion ... your points in your last post leave me smiling in agreement and nodding my head in the familiar "affirmative" nod when people are chatting over a drink of choice. It seems we could likely go on and on with virtues of DAO, ADO, PT Query objects, the unfortunate demise of ADP's, and slight correction of each others "speaking freely" LOL!
As far as I know the default length is 50, not 30 or 1
Sorry but you are wrong. The default length when not specified at a declaration is 1 in all versions of SQL Server. Please see the following article which applies to SQL Server 2012 and also for SQL Server 2005 as per the lower link.
http://msdn.microsoft.com/en-us/library/ms176089.aspx
http://msdn.microsoft.com/en-us/library/ms176089(v=sql.90).aspx
When n is not specified in a data definition or variable declaration statement, the default length is 1.
I also feel the points should be split really as it wouldn't have been picked up was it not for my comment.