Help with stored Procedure execution (1 Viewer)

Dear Experts

PLEA:
Please can I request some help. I have trolled through various posts in this forum and internet and feel lost. I have moderate knowledge of VBA and novice at calling sql server stored procedure from access.

BACKGROUND:
I have Access FE connected to Access BE with about 200 records in table ‘Customer’. Each record have 20 fields of information. I have a form ‘SearchF’ with four unbound fields (UniqueID, Name, Surname, DoB) and a ListBoxA populated by a query to retrieve matching record(s) from access BE. Selecting record in ListBoxA opens form with complete details of a selected customer.

There is a SQL Server with table containing records of thousands of customers each with twenty fields of data (same as access BE table). I have two stored proc on sql server ‘SearchCust’ and ‘SelectCust’. ‘SearchCust’ SP retrieves four fields from all records matching four params on search form and ‘SelectCust’ SP will retrieve one unique record based on UniqueID.

PROBLEM:
I want to do two things which I am struggling with and need help:

1. When ListBoxA does not retrieves any record from BE, I want to call SearchCust SP passing four params from unbound text fields to look for matching customers in sql server and populate ListBoxB on Form ‘sqlCustF’. Then when I select one of these records I want to call ‘SelectCust’ SP retrieving unique record and *INSERT in my Access BE Table as a new record.

2. When ListBoxA retrieves record(s) from BE and when I select one, I want to call SelectCust SP and *UPDATE access BE table with SP retrieved recordset.

I am using Access 2007. I have a working ODBC connection string. Any help / pointer / code or sample will be greatly appreciated.

Many thanks in advance.

http://www.access-programmers.co.uk/forums/showthread.php?t=257434
This is one of the better post that covers SP from several points of view.

Posting your SP TSQL code might be useful.

But, here is something to consider.
If an ODBC Linked table is being used, and your list box uses a SQL Statement (query) with parameters, the ODBC creates an equal SP at the Access / SQL boundry area. Using an SP to retrieve the limited list probably won't improve performance.
I just mention this becase there are stories about how Access queries bring back all the data from SQL and filter it locally on the machine. That simply is not the case.

For speed, it is important to use pure SQL language queries.
"Select * from Table where LastName = " & LastNameVariable
The use of Access functions (e.g. IIF) should not be used if speed is important.

For a single update, there are several options.
Are you locked into a SP because of some company or network security policy? Or is the SP being used to expand your programming experience?

Thanks a lot Rx_ for your reply. SQL server is tightly guarded and hence the SP was created on my request. I donot have the SP code.

However I have managed to workout TSQL and VBA to pass the parameters and am getting close to what I want to do. It takes a while and is very painful but getting things falling in place by trial and error is such a satisfactory experience! Especially for novice like me!

Many thanks again for your reply.

Makmit

Please be sure to post your code solution. There are other novice like yourself who will be searching.
Also, we may be able to review your code and offere additional suggestions.

There are other solutions for efficiently updating a record, adding a new record for a SQL Server Linked table that doesn't involve SP.
If the SQL team isn't good at communicating, it may warrant using other methods so your productivity is increased.
The IT Corporate Culture can sometimes direct your best course of action.

In Theory SP, would be best.
In Theory, Theory and Practice are the same.
In Practice, they are very different.