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

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

I am executing SQL Server Stored Procedures via ADO objects. Here is some example code from the Insert method of a table class:



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.

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

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.

FYI: I have resolved the trouble storing dates to SQL Server via ADO objects and Stored Procedures:

http://www.access-programmers.co.uk/forums/showthread.php?p=1104450#post1104450

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

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.

Many thanks. That has worked.

Another good link is:

http://www.sqlservercentral.com/Forums/Topic657473-131-1.aspx where an example database is available.

That has worked.

I am glad to hear your problem is now resolved.