Access OLEDB Driver & Queries. MS Please help.
Author |
Message |
Russell Sinclai #1 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
I don't know if anyone at Microsoft will read this but I have a very serious issue with ADO and the Jet 3.51 driver. If you are not a MS employee, this may answer a common question for you. I have been attempting to run a stored query in an Access database using the Jet driver and a Command object and I keep getting an error that it is not a valid SQL statement (Required 'INSERT', 'DELETE',...). There is nothing wrong with the query. I contacted MS Tech Support last night and was informed that the Jet OLEDB driver has many serious issues and this is one of them. I was told that the Jet driver does not properly support stored queries and that I should use the ODBC driver. Excuse my rant but how is OLEDB supposed to replace ODBC if we have to use the OLEDB ODBC driver to do anything useful? I am an author for Smart Access and I am currently working on a 5-part introduction to ADO. What I would like to know is this: Is this problem caused by anything else that can be worked around other than by using the ODBC driver, and when is Microsoft planning to fix this (what I consider) very serious issue with the OLEDB driver for Jet 3.51? A response would be very much appreciated. Thanks, Russell Sinclair
|
Tue, 25 Sep 2001 03:00:00 GMT |
|
 |
MM #2 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
Russel you need to upgrade to the JetOLEDB Provider 4 for Access. There is a property in the command object called: "Jet OLEDB:Stored Query", set this value equal to true and it should work. I used this with stored action querys that took parameters, and it worked for me. Now the version of MDAC 2.1 that I used was the one that came with SQL Server 7, microsoft just released a new update the supercedes the one on the SQL Server 7 CD. I have not had a chance to expierment with it so it may or may not work, it should though, unless Microsoft took some properties out. Hope this helps Mike Montoya. BTW I am not a MS employee. Example code: With cmd Set .ActiveConnection = cnEDS .Properties.Item("Jet OLEDB:Stored Query") = True .CommandType = adCmdUnknown ' MS Access embedded query ' Set the name of the command text to the name of the stored procedure .CommandText = strQueryName ' Append values to the parameter collection Set prm = .CreateParameter(sParamName, adVarChar, adParamInput, lParamSize, ParamValue) .Parameters.Append prm ' Get recordset data Set rs = .Execute End With Quote: -----Original Message-----
Newsgroups: microsoft.public.data.ado,microsoft.public.ado Date: Friday, April 09, 1999 7:47 AM Subject: Access OLEDB Driver & Queries. MS Please help. >I don't know if anyone at Microsoft will read this but I have a very serious >issue with ADO and the Jet 3.51 driver. If you are not a MS employee, this >may answer a common question for you. >I have been attempting to run a stored query in an Access database using the >Jet driver and a Command object and I keep getting an error that it is not a >valid SQL statement (Required 'INSERT', 'DELETE',...). There is nothing >wrong with the query. >I contacted MS Tech Support last night and was informed that the Jet OLEDB >driver has many serious issues and this is one of them. I was told that the >Jet driver does not properly support stored queries and that I should use >the ODBC driver. Excuse my rant but how is OLEDB supposed to replace ODBC if >we have to use the OLEDB ODBC driver to do anything useful? >I am an author for Smart Access and I am currently working on a 5-part >introduction to ADO. What I would like to know is this: Is this problem >caused by anything else that can be worked around other than by using the >ODBC driver, and when is Microsoft planning to fix this (what I consider) >very serious issue with the OLEDB driver for Jet 3.51? >A response would be very much appreciated. >Thanks, >Russell Sinclair
|
Tue, 25 Sep 2001 03:00:00 GMT |
|
 |
Dave Floo #3 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
MM, I'm also trying to execute Access 97 stored action queries and select qureies, that take parameters, using ADO. I have upgraded to OLEDB Provider for Jet 4.0 and have set the Command object property "Jet OLEDB:Stored Query" and used the adCmdUnknown for the CommandType property, but I'm not sure of what the CommandText property should be. Given: a table Employee of with two fields: EmpNum Text 6 EmpName Text 20 and a Query usp_InsertEmployee that is:
INSERT INTO EMPLOYEE ( EmpNum, EmpName )
I've tried several string settings, including .CommandTest = "usp_InsertEmployee ?, ?" and then appending parameters .CommandText = "usp_InsertEmployee '2345', 'Sam Spade'" What should my CommandText contain? Do you have any suggestions on written material or help on writting stored queries in Access 97? It sounds as though you have already been down this road, I sure could use your help!!! Thanks!! DaveF Quote:
> Russel you need to upgrade to the JetOLEDB Provider 4 for Access. There is > a property in the command object called: "Jet OLEDB:Stored Query", set this > value equal to true and it should work. I used this with stored action > querys that took parameters, and it worked for me. Now the version of MDAC > 2.1 that I used was the one that came with SQL Server 7, microsoft just > released a new update the supercedes the one on the SQL Server 7 CD. I have > not had a chance to expierment with it so it may or may not work, it should > though, unless Microsoft took some properties out. Hope this helps Mike > Montoya. BTW I am not a MS employee. Example code: > With cmd > Set .ActiveConnection = cnEDS > .Properties.Item("Jet OLEDB:Stored Query") = True > .CommandType = adCmdUnknown ' MS Access embedded query > ' Set the name of the command text to the name of the stored > procedure > .CommandText = strQueryName > ' Append values to the parameter collection > Set prm = .CreateParameter(sParamName, adVarChar, adParamInput, > lParamSize, ParamValue) > .Parameters.Append prm > ' Get recordset data > Set rs = .Execute > End With > -----Original Message-----
> Newsgroups: microsoft.public.data.ado,microsoft.public.ado > Date: Friday, April 09, 1999 7:47 AM > Subject: Access OLEDB Driver & Queries. MS Please help. > >I don't know if anyone at Microsoft will read this but I have a very > serious > >issue with ADO and the Jet 3.51 driver. If you are not a MS employee, this > >may answer a common question for you. > >I have been attempting to run a stored query in an Access database using > the > >Jet driver and a Command object and I keep getting an error that it is not > a > >valid SQL statement (Required 'INSERT', 'DELETE',...). There is nothing > >wrong with the query. > >I contacted MS Tech Support last night and was informed that the Jet OLEDB > >driver has many serious issues and this is one of them. I was told that the > >Jet driver does not properly support stored queries and that I should use > >the ODBC driver. Excuse my rant but how is OLEDB supposed to replace ODBC > if > >we have to use the OLEDB ODBC driver to do anything useful? > >I am an author for Smart Access and I am currently working on a 5-part > >introduction to ADO. What I would like to know is this: Is this problem > >caused by anything else that can be worked around other than by using the > >ODBC driver, and when is Microsoft planning to fix this (what I consider) > >very serious issue with the OLEDB driver for Jet 3.51? > >A response would be very much appreciated. > >Thanks, > >Russell Sinclair
|
Tue, 02 Oct 2001 03:00:00 GMT |
|
 |
Conor Cunningha #4 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
If you want to use that property, just set the command text to be the name of the stored query. don't pass parameters in-line - pass them as the argument to .execute alternatively, you could execute a sql statement "exec procname paramvalue, paramvalue" Conor
Quote: > MM, > I'm also trying to execute Access 97 stored action queries and select qureies, > that take parameters, using ADO. I have upgraded to OLEDB Provider for Jet 4.0 > and have set the Command object property "Jet OLEDB:Stored Query" and used the > adCmdUnknown for the CommandType property, but I'm not sure of what the > CommandText property should be. > Given: > a table Employee of with two fields: > EmpNum Text 6 > EmpName Text 20 > and a Query usp_InsertEmployee that is:
> INSERT INTO EMPLOYEE ( EmpNum, EmpName )
> I've tried several string settings, including > .CommandTest = "usp_InsertEmployee ?, ?" and then appending > parameters > .CommandText = "usp_InsertEmployee '2345', 'Sam Spade'" > What should my CommandText contain? > Do you have any suggestions on written material or help on writting stored > queries in Access 97? > It sounds as though you have already been down this road, I sure could use your > help!!! > Thanks!! > DaveF
> > Russel you need to upgrade to the JetOLEDB Provider 4 for Access. There is > > a property in the command object called: "Jet OLEDB:Stored Query", set this > > value equal to true and it should work. I used this with stored action > > querys that took parameters, and it worked for me. Now the version of MDAC > > 2.1 that I used was the one that came with SQL Server 7, microsoft just > > released a new update the supercedes the one on the SQL Server 7 CD. I have > > not had a chance to expierment with it so it may or may not work, it should > > though, unless Microsoft took some properties out. Hope this helps Mike > > Montoya. BTW I am not a MS employee. Example code: > > With cmd > > Set .ActiveConnection = cnEDS > > .Properties.Item("Jet OLEDB:Stored Query") = True > > .CommandType = adCmdUnknown ' MS Access embedded query > > ' Set the name of the command text to the name of the stored > > procedure > > .CommandText = strQueryName > > ' Append values to the parameter collection > > Set prm = .CreateParameter(sParamName, adVarChar, adParamInput, > > lParamSize, ParamValue) > > .Parameters.Append prm > > ' Get recordset data > > Set rs = .Execute > > End With > > -----Original Message-----
> > Newsgroups: microsoft.public.data.ado,microsoft.public.ado > > Date: Friday, April 09, 1999 7:47 AM > > Subject: Access OLEDB Driver & Queries. MS Please help. > > >I don't know if anyone at Microsoft will read this but I have a very > > serious > > >issue with ADO and the Jet 3.51 driver. If you are not a MS employee, this > > >may answer a common question for you. > > >I have been attempting to run a stored query in an Access database using > > the > > >Jet driver and a Command object and I keep getting an error that it is not > > a > > >valid SQL statement (Required 'INSERT', 'DELETE',...). There is nothing > > >wrong with the query. > > >I contacted MS Tech Support last night and was informed that the Jet OLEDB > > >driver has many serious issues and this is one of them. I was told that the > > >Jet driver does not properly support stored queries and that I should use > > >the ODBC driver. Excuse my rant but how is OLEDB supposed to replace ODBC > > if > > >we have to use the OLEDB ODBC driver to do anything useful? > > >I am an author for Smart Access and I am currently working on a 5-part > > >introduction to ADO. What I would like to know is this: Is this problem > > >caused by anything else that can be worked around other than by using the > > >ODBC driver, and when is Microsoft planning to fix this (what I consider) > > >very serious issue with the OLEDB driver for Jet 3.51? > > >A response would be very much appreciated. > > >Thanks, > > >Russell Sinclair
|
Tue, 02 Oct 2001 03:00:00 GMT |
|
 |
Dave Floo #5 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
Conor: Thanks for the suggestions! I've given them a try and still cannot get the Jet Provider to process the calls as stored queries. If this is working for you and others, I'm clearly doing something wrong and would appreciate any further help you can give. Here is what I understood and tried from your earlier response: Dim vParams As Variant vParams = Array("2345", "Sam Spade") With CommandObject .CommandType = adCmdUnknown .CommandText = "usp_InsertEmployee" .Execute , vParams End With The alternative suggestion... With CommandObject .CommandType = adCmdUnknown .CommandText = "usp_InsertEmployee ('2345', 'Sam Spade')" .Execute End With I also tried other variations for the command text... .CommandText = "{usp_InsertEmployee ('2345', 'Sam Spade')}" .CommandText = "call usp_InsertEmployee ('2345', 'Sam Spade')" .CommandText = "{call usp_InsertEmployee ('2345', 'Sam Spade')}" .CommandText = "exec usp_InsertEmployee ('2345', 'Sam Spade')" all without success. The error message in each case stated that a SQL statement was expected such as 'INSERT', UPDATE,... I can call the stored queries successfully using the ODBC Provider using the following: With CommandObject .CommandType = adCmdUnknown .CommandText = "usp_InsertEmployee ('2345', 'Sam Spade')" .Execute End With and... Dim vParams As Variant vParams = Array("2345", "Sam Spade") With CommandObject .CommandType = adCmdUnknown .CommandText = "usp_InsertEmployee" .Execute , vParams End With I know that I am connecting to the Access 97 database and I have the recently released MDAC2.1 with the Jet4.0 Provider. If you have any other suggestions I'd be glad to hear them. Thanks!! DaveF Quote:
> If you want to use that property, just set the command text to be the name > of the > stored query. > don't pass parameters in-line - pass them as the argument to .execute > alternatively, you could execute a sql statement "exec procname paramvalue, > paramvalue" > Conor
> > MM, > > I'm also trying to execute Access 97 stored action queries and select > qureies, > > that take parameters, using ADO. I have upgraded to OLEDB Provider for > Jet 4.0 > > and have set the Command object property "Jet OLEDB:Stored Query" and used > the > > adCmdUnknown for the CommandType property, but I'm not sure of what the > > CommandText property should be. > > Given: > > a table Employee of with two fields: > > EmpNum Text 6 > > EmpName Text 20 > > and a Query usp_InsertEmployee that is:
> > INSERT INTO EMPLOYEE ( EmpNum, EmpName )
> > I've tried several string settings, including > > .CommandTest = "usp_InsertEmployee ?, ?" and then appending > > parameters > > .CommandText = "usp_InsertEmployee '2345', 'Sam Spade'" > > What should my CommandText contain? > > Do you have any suggestions on written material or help on writting stored > > queries in Access 97? > > It sounds as though you have already been down this road, I sure could use > your > > help!!! > > Thanks!! > > DaveF
> > > Russel you need to upgrade to the JetOLEDB Provider 4 for Access. There > is > > > a property in the command object called: "Jet OLEDB:Stored Query", set > this > > > value equal to true and it should work. I used this with stored action > > > querys that took parameters, and it worked for me. Now the version of > MDAC > > > 2.1 that I used was the one that came with SQL Server 7, microsoft just > > > released a new update the supercedes the one on the SQL Server 7 CD. I > have > > > not had a chance to expierment with it so it may or may not work, it > should > > > though, unless Microsoft took some properties out. Hope this helps Mike > > > Montoya. BTW I am not a MS employee. Example code: > > > With cmd > > > Set .ActiveConnection = cnEDS > > > .Properties.Item("Jet OLEDB:Stored Query") = True > > > .CommandType = adCmdUnknown ' MS Access embedded query > > > ' Set the name of the command text to the name of the stored > > > procedure > > > .CommandText = strQueryName > > > ' Append values to the parameter collection > > > Set prm = .CreateParameter(sParamName, adVarChar, > adParamInput, > > > lParamSize, ParamValue) > > > .Parameters.Append prm > > > ' Get recordset data > > > Set rs = .Execute > > > End With > > > -----Original Message-----
> > > Newsgroups: microsoft.public.data.ado,microsoft.public.ado > > > Date: Friday, April 09, 1999 7:47 AM > > > Subject: Access OLEDB Driver & Queries. MS Please help. > > > >I don't know if anyone at Microsoft will read this but I have a very > > > serious > > > >issue with ADO and the Jet 3.51 driver. If you are not a MS employee, > this > > > >may answer a common question for you. > > > >I have been attempting to run a stored query in an Access database > using > > > the > > > >Jet driver and a Command object and I keep getting an error that it is > not > > > a > > > >valid SQL statement (Required 'INSERT', 'DELETE',...). There is nothing > > > >wrong with the query. > > > >I contacted MS Tech Support last night and was informed that the Jet > OLEDB > > > >driver has many serious issues and this is one of them. I was told that > the > > > >Jet driver does not properly support stored queries and that I should > use > > > >the ODBC driver. Excuse my rant but how is OLEDB supposed to replace > ODBC > > > if > > > >we have to use the OLEDB ODBC driver to do anything useful? > > > >I am an author for Smart Access and I am currently working on a 5-part > > > >introduction to ADO. What I would like to know is this: Is this problem > > > >caused by anything else that can be worked around other than by using > the > > > >ODBC driver, and when is Microsoft planning to fix this (what I > consider) > > > >very serious issue with the OLEDB driver for Jet 3.51? > > > >A response would be very much appreciated. > > > >Thanks, > > > >Russell Sinclair
|
Fri, 05 Oct 2001 03:00:00 GMT |
|
 |
Conor Cunningha #6 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
The array needs to be specified as an argument to execute, not as part of the query string. something more like .commandtext = "exec sqname;" .execute ,, Array(param1, param2) (I don't have an ADO installation handy to try the syntax)
Quote: > Conor: > Thanks for the suggestions! I've given them a try and still cannot get the Jet > Provider to process the calls as stored queries. If this is working for you and > others, I'm clearly doing something wrong and would appreciate any further help > you can give. > Here is what I understood and tried from your earlier response: > Dim vParams As Variant > vParams = Array("2345", "Sam Spade") > With CommandObject > .CommandType = adCmdUnknown > .CommandText = "usp_InsertEmployee" > .Execute , vParams > End With > The alternative suggestion... > With CommandObject > .CommandType = adCmdUnknown > .CommandText = "usp_InsertEmployee ('2345', 'Sam Spade')" > .Execute > End With > I also tried other variations for the command text... > .CommandText = "{usp_InsertEmployee ('2345', 'Sam Spade')}" > .CommandText = "call usp_InsertEmployee ('2345', 'Sam Spade')" > .CommandText = "{call usp_InsertEmployee ('2345', 'Sam Spade')}" > .CommandText = "exec usp_InsertEmployee ('2345', 'Sam Spade')" > all without success. The error message in each case stated that a SQL statement > was expected such as 'INSERT', UPDATE,... > I can call the stored queries successfully using the ODBC Provider using the > following: > With CommandObject > .CommandType = adCmdUnknown > .CommandText = "usp_InsertEmployee ('2345', 'Sam Spade')" > .Execute > End With > and... > Dim vParams As Variant > vParams = Array("2345", "Sam Spade") > With CommandObject > .CommandType = adCmdUnknown > .CommandText = "usp_InsertEmployee" > .Execute , vParams > End With > I know that I am connecting to the Access 97 database and I have the recently > released MDAC2.1 with the Jet4.0 Provider. > If you have any other suggestions I'd be glad to hear them. > Thanks!! > DaveF
> > If you want to use that property, just set the command text to be the name > > of the > > stored query. > > don't pass parameters in-line - pass them as the argument to .execute > > alternatively, you could execute a sql statement "exec procname paramvalue, > > paramvalue" > > Conor
> > > MM, > > > I'm also trying to execute Access 97 stored action queries and select > > qureies, > > > that take parameters, using ADO. I have upgraded to OLEDB Provider for > > Jet 4.0 > > > and have set the Command object property "Jet OLEDB:Stored Query" and used > > the > > > adCmdUnknown for the CommandType property, but I'm not sure of what the > > > CommandText property should be. > > > Given: > > > a table Employee of with two fields: > > > EmpNum Text 6 > > > EmpName Text 20 > > > and a Query usp_InsertEmployee that is:
> > > INSERT INTO EMPLOYEE ( EmpNum, EmpName )
> > > I've tried several string settings, including > > > .CommandTest = "usp_InsertEmployee ?, ?" and then appending > > > parameters > > > .CommandText = "usp_InsertEmployee '2345', 'Sam Spade'" > > > What should my CommandText contain? > > > Do you have any suggestions on written material or help on writting stored > > > queries in Access 97? > > > It sounds as though you have already been down this road, I sure could use > > your > > > help!!! > > > Thanks!! > > > DaveF
> > > > Russel you need to upgrade to the JetOLEDB Provider 4 for Access. There > > is > > > > a property in the command object called: "Jet OLEDB:Stored Query", set > > this > > > > value equal to true and it should work. I used this with stored action > > > > querys that took parameters, and it worked for me. Now the version of > > MDAC > > > > 2.1 that I used was the one that came with SQL Server 7, microsoft just > > > > released a new update the supercedes the one on the SQL Server 7 CD. I > > have > > > > not had a chance to expierment with it so it may or may not work, it > > should > > > > though, unless Microsoft took some properties out. Hope this helps Mike > > > > Montoya. BTW I am not a MS employee. Example code: > > > > With cmd > > > > Set .ActiveConnection = cnEDS > > > > .Properties.Item("Jet OLEDB:Stored Query") = True > > > > .CommandType = adCmdUnknown ' MS Access embedded query > > > > ' Set the name of the command text to the name of the stored > > > > procedure > > > > .CommandText = strQueryName > > > > ' Append values to the parameter collection > > > > Set prm = .CreateParameter(sParamName, adVarChar, > > adParamInput, > > > > lParamSize, ParamValue) > > > > .Parameters.Append prm > > > > ' Get recordset data > > > > Set rs = .Execute > > > > End With > > > > -----Original Message-----
> > > > Newsgroups: microsoft.public.data.ado,microsoft.public.ado > > > > Date: Friday, April 09, 1999 7:47 AM > > > > Subject: Access OLEDB Driver & Queries. MS Please help. > > > > >I don't know if anyone at Microsoft will read this but I have a very > > > > serious > > > > >issue with ADO and the Jet 3.51 driver. If you are not a MS employee, > > this > > > > >may answer a common question for you. > > > > >I have been attempting to run a stored query in an Access database > > using > > > > the > > > > >Jet driver and a Command object and I keep getting an error that it is > > not > > > > a > > > > >valid SQL statement (Required 'INSERT', 'DELETE',...). There is nothing > > > > >wrong with the query. > > > > >I contacted MS Tech Support last night and was informed that the Jet > > OLEDB > > > > >driver has many serious issues and this is one of them. I was told that > > the > > > > >Jet driver does not properly support stored queries and that I should > > use > > > > >the ODBC driver. Excuse my rant but how is OLEDB supposed to replace > > ODBC > > > > if > > > > >we have to use the OLEDB ODBC driver to do anything useful? > > > > >I am an author for Smart Access and I am currently working on a 5-part > > > > >introduction to ADO. What I would like to know is this: Is this problem > > > > >caused by anything else that can be worked around other than by using > > the > > > > >ODBC driver, and when is Microsoft planning to fix this (what I > > consider) > > > > >very serious issue with the OLEDB driver for Jet 3.51? > > > > >A response would be very much appreciated. > > > > >Thanks, > > > > >Russell Sinclair
|
Fri, 05 Oct 2001 03:00:00 GMT |
|
 |
AnimalCh #7 / 7
|
 Access OLEDB Driver & Queries. MS Please help.
Dave, I was tearing my hair out over this very same thing for about a week but I finally got it to work. Here is what you must do: 1. Make sure you have the latest MDAC 2.1 installed. (BTW this works with Access 97' you won't have to upgrade to Access 2000) 2. Make sure you changed to provider in your connect string to "Microsoft.Jet.OLEDB.4.0" 3. Make sure you have included MS ADO 2.1 libraries ...etc under Project References. 4. After you set up your initial connection try something like the following code: Dim myCommand as New ADODB.Command Dim myParameter as New ADODB.Parameter Dim myRecordset as New ADODB.Recordset /**initialize your command object***/ myCommand.CommandText = [YOUR STORED PROCEDURE NAME HERE] ....w/o paramaters. myCommand.CommandTimeout = 180 myCommand.CommandType = adCmdStoredProc myCommand.ActiveConnection = [YOUR CONNECTION OBJECT HERE] /**add any parameters as needed***/ Set myParameter = myCommand.CreateParameter("ParameterName", adChar, , Len(myParameterValue), myParameterValue) myCommand.Parameters.Append myParameter /**Call the Stored Query returning data***/ myRecordset .Open myCommand, , adOpenStatic, adLockReadOnly Your sample code looked like it should've worked but right now i'm not trusting much of the documentation. I'm just kinda working on my own and getting whatever input i can from this newsgroup. I think we're on our own with this OLE DB stuff. Try this out and lemme know if it works or not. Oh yeah....one other thing....get ready for this one! /**Opening an Access 97 table this way....****/ Recordset.Open [TABLE NAME HERE], db, adOpenDynamic, adLockPessimistic, adCmdTable /**Opening same Access 97 this way...****/ db.LockType = adLockPessimistic 'lock record upon editing. db.CursorType = adOpenDynamic Set Recordset = db.Execute([TABLE NAME HERE], , adCmdTable) ....The second method does not return all records in the table. ??? The same phenomenon happens if you replace [TABLE NAME] with [Select * from TABLE NAME]. ok but get this...it only happens on some tables and not others. Now if I try the same methods on the same table in and Access 2000 format it friggin' works!!!! I've been kinda hesitant to post this cuz i didn't think anybody would believe me. If any body else gets this please lemme know. and if anybody has anything on how to get .Seek and .Index features to work ..I need help! ugh. Thanks AnimalChi Quote:
>The array needs to be specified as an argument to execute, not as part of >the query string. >something more like >.commandtext = "exec sqname;" >.execute ,, Array(param1, param2) >(I don't have an ADO installation handy to try the syntax)
>> Conor: >> Thanks for the suggestions! I've given them a try and still cannot get >the Jet >> Provider to process the calls as stored queries. If this is working for >you and >> others, I'm clearly doing something wrong and would appreciate any further >help >> you can give. >> Here is what I understood and tried from your earlier response: >> Dim vParams As Variant >> vParams = Array("2345", "Sam Spade") >> With CommandObject >> .CommandType = adCmdUnknown >> .CommandText = "usp_InsertEmployee" >> .Execute , vParams >> End With >> The alternative suggestion... >> With CommandObject >> .CommandType = adCmdUnknown >> .CommandText = "usp_InsertEmployee ('2345', 'Sam Spade')" >> .Execute >> End With >> I also tried other variations for the command text... >> .CommandText = "{usp_InsertEmployee ('2345', 'Sam Spade')}" >> .CommandText = "call usp_InsertEmployee ('2345', 'Sam Spade')" >> .CommandText = "{call usp_InsertEmployee ('2345', 'Sam Spade')}" >> .CommandText = "exec usp_InsertEmployee ('2345', 'Sam Spade')" >> all without success. The error message in each case stated that a SQL >statement >> was expected such as 'INSERT', UPDATE,... >> I can call the stored queries successfully using the ODBC Provider using >the >> following: >> With CommandObject >> .CommandType = adCmdUnknown >> .CommandText = "usp_InsertEmployee ('2345', 'Sam Spade')" >> .Execute >> End With >> and... >> Dim vParams As Variant >> vParams = Array("2345", "Sam Spade") >> With CommandObject >> .CommandType = adCmdUnknown >> .CommandText = "usp_InsertEmployee" >> .Execute , vParams >> End With >> I know that I am connecting to the Access 97 database and I have the >recently >> released MDAC2.1 with the Jet4.0 Provider. >> If you have any other suggestions I'd be glad to hear them. >> Thanks!! >> DaveF
>> > If you want to use that property, just set the command text to be the >name >> > of the >> > stored query. >> > don't pass parameters in-line - pass them as the argument to .execute >> > alternatively, you could execute a sql statement "exec procname >paramvalue, >> > paramvalue" >> > Conor
>> > > MM, >> > > I'm also trying to execute Access 97 stored action queries and select >> > qureies, >> > > that take parameters, using ADO. I have upgraded to OLEDB Provider >for >> > Jet 4.0 >> > > and have set the Command object property "Jet OLEDB:Stored Query" and >used >> > the >> > > adCmdUnknown for the CommandType property, but I'm not sure of what >the >> > > CommandText property should be. >> > > Given: >> > > a table Employee of with two fields: >> > > EmpNum Text 6 >> > > EmpName Text 20 >> > > and a Query usp_InsertEmployee that is:
>> > > INSERT INTO EMPLOYEE ( EmpNum, EmpName )
>> > > I've tried several string settings, including >> > > .CommandTest = "usp_InsertEmployee ?, ?" and then >appending >> > > parameters >> > > .CommandText = "usp_InsertEmployee '2345', 'Sam Spade'" >> > > What should my CommandText contain? >> > > Do you have any suggestions on written material or help on writting >stored >> > > queries in Access 97? >> > > It sounds as though you have already been down this road, I sure could >use >> > your >> > > help!!! >> > > Thanks!! >> > > DaveF
>> > > > Russel you need to upgrade to the JetOLEDB Provider 4 for Access. >There >> > is >> > > > a property in the command object called: "Jet OLEDB:Stored Query", >set >> > this >> > > > value equal to true and it should work. I used this with stored >action >> > > > querys that took parameters, and it worked for me. Now the version >of >> > MDAC >> > > > 2.1 that I used was the one that came with SQL Server 7, microsoft >just >> > > > released a new update the supercedes the one on the SQL Server 7 CD. >I >> > have >> > > > not had a chance to expierment with it so it may or may not work, it >> > should >> > > > though, unless Microsoft took some properties out. Hope this helps >Mike >> > > > Montoya. BTW I am not a MS employee. Example code: >> > > > With cmd >> > > > Set .ActiveConnection = cnEDS >> > > > .Properties.Item("Jet OLEDB:Stored Query") = True >> > > > .CommandType = adCmdUnknown ' MS Access embedded query >> > > > ' Set the name of the command text to the name of the stored >> > > > procedure >> > > > .CommandText = strQueryName >> > > > ' Append values to the parameter collection >> > > > Set prm = .CreateParameter(sParamName, adVarChar, >> > adParamInput, >> > > > lParamSize, ParamValue) >> > > > .Parameters.Append prm >> > > > ' Get recordset data >> > > > Set rs = .Execute >> > > > End With >> > > > -----Original Message-----
>> > > > Newsgroups: microsoft.public.data.ado,microsoft.public.ado >> > > > Date: Friday, April 09, 1999 7:47 AM >> > > > Subject: Access OLEDB Driver & Queries. MS Please help. >> > > > >I don't know if anyone at Microsoft will read this but I have a >very >> > > > serious >> > > > >issue with ADO and the Jet 3.51 driver. If you are not a MS >employee, >> > this >> > > > >may answer a common question for you. >> > > > >I have been attempting to run a stored query in an Access database >> > using >> > > > the >> > > > >Jet driver and a Command object and I keep getting an error that it >is >> > not >> > > > a >> > > > >valid SQL statement (Required 'INSERT', 'DELETE',...). There is >nothing >> > > > >wrong with the query. >> > > > >I contacted MS Tech Support last night and was informed that the >Jet >> > OLEDB >> > > > >driver has many serious issues and this is one of them. I was told >that >> > the >> > > > >Jet driver does not properly support stored queries and that I >should >> > use >> > > > >the ODBC driver. Excuse my rant but how is OLEDB supposed to >replace >> > ODBC >> > > > if >> > > > >we have to use the OLEDB ODBC driver to do anything useful? >> > > > >I am an author for Smart Access and I am currently working on a >5-part >> > > > >introduction to ADO. What I would like to know is this: Is this >problem >> > > > >caused by anything else that can be worked around other than by >using >> > the >> > > > >ODBC driver, and when is Microsoft planning to fix this
... read more »
|
Sun, 07 Oct 2001 03:00:00 GMT |
|
|
|