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 |
|
| |
|
