PASS PARAMTERS FROM MS ACCESS 2003 to SQL SERVER (1 Viewer)

Tallboy

Registered User.
Local time
Today, 00:07
Joined
Oct 13, 2011
Messages
19
Hi Guys,

I am moving to SQL Server and will be using MS Acess 2003 specifically as a Front End to a SQL Server 2008 database.

I have a form called FrmStats and a txt field called SName.

When I click on a button I want to send the txt as a parameter to a Stored Procedure on SLQ Server.

I know the code will look something like this

[FONT=&quot]Dim DB As Database
Dim Q As QueryDef

Set DB = CurrentDb()
Set Q = DB.QueryDefs(" ???? ")

Q.SQL = "exec dbo.ReturnRecord " +"'Forms!FrmStats.SName'"


Also how then do I show the results on another form?

Regards
:D

[/FONT]
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:07
Joined
Jun 23, 2011
Messages
2,631
I am executing SQL Server Stored Procedures via ADO objects. Here is some example code from the Insert method of a table class:


Code:
  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_Insert"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@authid").Value = Me.authid
    .Parameters("@projectid").Value = Me.projectid
    .Parameters("@title").Value = Me.title
    .Parameters("@productnumber").Value = Me.productnumber
    .Parameters("@bomcad").Type = adLongVarChar
    .Parameters("@bomcad").Value = Me.bomcad
    Set adoRS = .Execute()
  End With

  'Retrieve the ID the new record is stored at
  Me.id = Nz(adoRS!id, 0)
Usually executing .Parameters.Refresh has the Access FE know all about the SP's Parameters. The one gottcha with this is for a VARCHAR(MAX) which Access auto detects that Parameter as a VARCHAR(8000). This I had to manually update that Parameter's data type.
 
Last edited:

Tallboy

Registered User.
Local time
Today, 00:07
Joined
Oct 13, 2011
Messages
19
Hi,

Thanks for that, I think I get it but need to put the practice in.

I will no doubt be comming back to you as my project progressess.

kind regards
:D
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:07
Joined
Jun 23, 2011
Messages
2,631
You are welcome.

FYI: I am having quite a fight trying to blank out a date column type field via the Stored Procedure. So, all is NOT simple it seems when dealing with Date data types. Works fine at the Management Studio interface with bare SQL, fighting tooth-n-nail via Accesss / VBA / ADO / Stored Proc.
 

Fear Naught

Kevin
Local time
Today, 00:07
Joined
Mar 2, 2006
Messages
229
I have tried using this code but get an error when compiling the VBA on the ADODB.Command line.

I have added a "reference" to "Microsoft ADO Ext 6.0 for DDL and Security" (the only ADO Reference I can find). What have I done wrong!

Actually what I am trying to do is run a SQL Stored procedure that doesn't have any parameters but does do a large update across the database. I can of course run the procedure from SQL Management Studio but need to make it available from an Access fonrt end.

Help
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:07
Joined
Jun 23, 2011
Messages
2,631
I have added a "reference" to "Microsoft ADO Ext 6.0 for DDL and Security" (the only ADO Reference I can find). What have I done wrong!

I believe the standard ADO objects are provided through "Microsoft ActiveX Data Objects 2.8 Library" (or what ever level of ADO your systems have installed.

The other ADO related reference I have enabled is "Microsoft ADO Ext. 2.8 for DDL and Security" and I believe that was required for some ADOX code not specifically related to executing Stored Procedures.
 

ArtfulOne

New member
Local time
Yesterday, 20:07
Joined
Sep 12, 2011
Messages
1
What I am very unclear on is, "Where do the results go when I invoke a stored procedure from Access?

Suppose I want to use the returned result set as the data source for an Access form or report. Actually, I want to do both. I also want to know how to use a result set returned from a stored procedure or a view as the data source form a combo box or listbox.

Almost forget. If I do bind the result from a stored procedure to a form, is the data read-only? Ideally, I would like it to be read-write, and even be able to insert and delete. For example:

user is looking for a customer in Utah. There is a stored procedure that accepts a State parameter. User chooses Utah from a combo box. I pass that to the Stored Procedure. Form fills with a list of Utah customers. User realizes that the customer doesn't exist. User adds the customer to the list. Alternatively, user realizes that the address returned for an existing customer is old and needs updating.

Thanks in advance for any help you can provide.

Arthu
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:07
Joined
Jun 23, 2011
Messages
2,631
What I am very unclear on is, "Where do the results go when I invoke a stored procedure from Access?

It ends up in the same place a Pass-Through result set would end up. If using ADO.Command / ADO.Parameters objects, then the call to execute returns an ADO.RecordSet.

If in DAO.QueryDef objects, then it is possible to download records to an FE table.

Example posts:

Options to execute Stored Procedures - DAO / ADO - Including an example of SQL SELECT Stored Procedure using ADODB.Command and ADODB.Parameters objects
http://www.access-programmers.co.uk/forums/showthread.php?p=1160494&posted=1#post1160494

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605
 

Users who are viewing this thread

Top Bottom