Access Front End SQL Server Back End

[rachelkm2]

I am using SQL Server as a back end to Access for the first time, and I am having difficulty understanding how the two work together.

Using Access VBA, I am able to open a connection to SQL Server, and I am able to execute a basic stored procedure (which accepts no parameters, just creates a table as a test).

What I don't understand is how to properly use parameters in SQL Server and pass values from Access to stored procedures in SQL Server.

As a basic example, I would like to have:

- a search form where the user selects a customer from a list (which is generated by a Select statement in a SQL Server stored procedure)

- a detail form, whose recordsource is based on a SQL Server stored procedure, where the input parameter is set to the primary key of the customer selected by the user on the search form.

So I guess I really have two questions:

1. How do you set the recordsource of a form to a stored procedure?
2. How do you pass user-selected values from a form to a parameterized stored procedure?

Any help will be much appreciated - thanks in advance!

 

[bparkinson]

I am using SQL Server as a back end to Access for the first time, and I am having difficulty understanding how the two work together.

Using Access VBA, I am able to open a connection to SQL Server, and I am able to execute a basic stored procedure (which accepts no parameters, just creates a table as a test).

What I don't understand is how to properly use parameters in SQL Server and pass values from Access to stored procedures in SQL Server.

As a basic example, I would like to have:

- a search form where the user selects a customer from a list (which is generated by a Select statement in a SQL Server stored procedure)

- a detail form, whose recordsource is based on a SQL Server stored procedure, where the input parameter is set to the primary key of the customer selected by the user on the search form.

So I guess I really have two questions:

1. How do you set the recordsource of a form to a stored procedure?
2. How do you pass user-selected values from a form to a parameterized stored procedure?

Any help will be much appreciated - thanks in advance!

I didn't see this post or I'd have replied earlier.

No way to bind a form to a stored procedures. You could use a stored procedure to populate a recordset and go fromn there. I'll let the Acccess experts advise you on that.

Here's code I wrote to execute a parameterized stored procedure. This is a method in a class, so iAthleteID and iTmp were populated from the procedure that instantiated the class.

Public Sub InsertAthleteExerciseGroupLink()

Dim p1 As New ADODB.Parameter
Dim p2 As New ADODB.Parameter

Set cmd = New ADODB.Command
InitConnection

cmd.ActiveConnection = cn

p1.Direction = adParamInput
p1.Type = adInteger
p1.value = iAthleteID

p2.Direction = adParamInput
p2.Type = adInteger
p2.value = iTmp


cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spInsertAthleteExerciseGroupLink"
cmd.Parameters.Append p1
cmd.Parameters.Append p2

cmd.Execute

Set cn = Nothing
Set cmd = Nothing
Set p1 = Nothing
Set p2 = Nothing


End Sub

 

[rachelkm2]

Thank you - I'll give this a try!

What I seem to have figured out re: binding a form to a stored procedure is that I can set the recordsource of the form to a pass-through query which executes the stored procedure. Does anyone know if there is a reason not to use this method?

 

[mdlueck]

@rachelkm2: I posted a high level overview of the programming architecture I arrived at developing a Client/Server application with Access 2007 and SQL Server 2008 R2. Post is located here: http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[mdlueck]

Oh, and I posted my Rx of VBA code to execute a Stored Procedure here:
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120