Stored procedure ?

[stnkyminky]

I have created a query in access 97 that inserts data into a table. When I initiate the query in access the data is inserted. When I attempt to initiate the query in vb, no rows are inserted? Can anyone help. Thanks in advance.

Dim cmdUpdate As ADODB.Command
Set cmdUpdate = New ADODB.Command

connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=Y:\foo.mdb"

Project_My_Conn.ConnectionString = connstring
Project_My_Conn.ConnectionTimeout = 30
Project_My_Conn.Open

Set cmdUpdate.ActiveConnection = Project_My_Conn
cmdUpdate.CommandText = "foo"
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.Execute Scott
Programmer Analyst

 

[BigDaddy]

Scott,
I don't believe that you can call Querys as if they were Stored Procedures. You may wish to try one of three methods:

1) call the access application object and docmd.openquery for the query you wish to run.

--OR--

2) declare a recordset and openrecordset(&quot;<query name>&quot;, conn). Not exactly sure of this logic, I know it works for table names.

--OR--

3) Read the SQLString in the query object and run this as your commandtext. Of course this will require the access application object to be created. If you do this, you might as well use the query object.

Note: Access 2000 projects have stored procedures that can be run by the ADO.Command object methods, Access 97 does not have this functionality.

 

[GAORR]

What you are attempting can be done with both Access 2000 or 97.

I don't see where you set up the parameters to be passed to the stored procedure.

Below is an example of code that will create a rec in a simple data base - last name, first name and student number. This uses ODBC and DSN as the provider but will work no matter how the connection is made.

Code starts:

Option Explicit
Private adoConnection As New ADODB.Connection

'' the following code goes in the routine to create the rec

Dim ConnectString As String

' use the ODBC and DSN as the provider
ConnectString = &quot;Provider=MSDASQL.1;DSN=AdoAssn&quot;

' establish the connection
adoConnection.ConnectionString = ConnectString
adoConnection.Open

' code to add a record to the data base

Dim adoCommand As New ADODB.Command
Dim adoParam As New ADODB.Parameter

' initialize ADO Command Object and Recordset
adoCommand.ActiveConnection = adoConnection
adoCommand.CommandType = adCmdStoredProc

' set the command to the required Stored Procedure
adoCommand.CommandText = &quot;AddRecord&quot;

' set the applicable parameters for the Stored Procedure
Set adoParam = adoCommand.CreateParameter(&quot;LName&quot;, adBSTR, adParamInput, Len(txtLastName), 0)
adoCommand.Parameters.Append adoParam
Set adoParam = adoCommand.CreateParameter(&quot;FName&quot;, adBSTR, adParamInput, Len(txtFirstName), 0)
adoCommand.Parameters.Append adoParam
Set adoParam = adoCommand.CreateParameter(&quot;SNumber&quot;, adInteger, adParamInput, 0, 0)
adoCommand.Parameters.Append adoParam

adoCommand.Parameters(&quot;LName&quot = StrConv(CStr(txtLastName)
adoCommand.Parameters(&quot;FName&quot = StrConv(CStr(txtFirstName)
adoCommand.Parameters(&quot;SNumber&quot = CInt(txtStudentNumber)

' update the database
adoCommand.Execute

' release the objects
Set adoParam = Nothing
Set adoCommand = Nothing

end of code.

Have a look - if still confused, I can try to explain further.

 

[BigDaddy]

GAORR,
A couple of questions:
1) What is the AddRecord? Is it an Access Query object or a compiled Stored Procedure?
2) If a Query object then how are the parameters passed to the query? Is it only by the order that the query object needs the parameter or is there a named parameter collection you can access via the Query Grid?

Scott was trying to access a query as a stored procedure within access. It looks as if you have a SQL database (connected via ODBC) that access will call the stored proc that is residing on the SQL box. Is my interpretation of your environment right?

 

[stnkyminky]

I've since embedded the SQL in my code versus the . Thanks for the replies. Scott
Programmer Analyst

 

[GAORR]

BigDaddy

Sorry, after I replied, I realized I could have been more specific. I am using an Access (97 or 2000) database with the connection being made using ODBC and DSN.

In the example I gave, the table name is StudentGrades with 3 fields - Lastname, FirstName and StudentGrades.

The 'AddRecord' is a query (stored procedure) in Access in the following format -
INSERT INTO STUDENTGRADES ( LASTNAME, FIRSTNAME, STUDENTNUMBER)
SELECT [@LNAME], [@fNAME], [@SNUMBER];

The parameters are passed withe following code -
' set the applicable parameters for the Stored Procedure
Set adoParam = adoCommand.CreateParameter(&quot;LName&quot;, adBSTR, adParamInput, Len(txtLastName), 0)
adoCommand.Parameters.Append adoParam
Set adoParam = adoCommand.CreateParameter(&quot;FName&quot;, adBSTR, adParamInput, Len(txtFirstName), 0)
adoCommand.Parameters.Append adoParam
Set adoParam = adoCommand.CreateParameter(&quot;SNumber&quot;, adInteger, adParamInput, 0, 0)
adoCommand.Parameters.Append adoParam

adoCommand.Parameters(&quot;LName&quot = StrConv(CStr(txtLastName)
adoCommand.Parameters(&quot;FName&quot = StrConv(CStr(txtFirstName)
adoCommand.Parameters(&quot;SNumber&quot = CInt(txtStudentNumber)

If still not clear, let me know. I would be happy to provide you with the complete app - let me know what your email is. I have recently been learning vb (I'm an old mainframe guy) and this was one of the assignments.
GAORR

 

[BigDaddy]

Thanks, that clears it up. I wanted to clarify the structure of the environment. Thanks for the help.