asked on

Access 2003 adp and SQL Server 2000 connection issues

I have a well established Clent based recoding system using Access 2003 adp fronthend with SQL Server 2000 database.
All is working absolutely fine. Some users us terminal services, and some have stand alone PC.. Totally irrelevant for this problem..
Sometimes, and in particular every morning (and I mean every morning) a report using a stored procedure will fail on what looks to be a timeout.
The 1st person in in a morning always runs a report. Which always fails at least once before working. If they happen to be the 2nd person in, it appears to work OK.

The report is run via an access form of selection criteria, which opens an access report which uses a stored procedure (with parameters) as its record source. (Something I do a lot)

I do not program the connection to the data base, but as part of a user logging on they access tables to verify user names and security etc so I though that would be enough to establish a connection.

Does any one know why the reports seem to timeout. AS I said it always happen for ther 1st person logging on, but it can happen sporadically to anyone.

Anyone have any ideas
Here's my recordsource code

strRecordSource = "Exec [Sp_Case_Status] '" & _
[Forms]![frm_rep_Case_Status]![str_from_lender] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_to_lender] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_from_plan] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_to_plan] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_from_loan] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_to_loan] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_from_date] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_to_Date] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_status] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_from_proc] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_to_proc] & "', '" & _
[Forms]![frm_rep_Case_Status]![str_sort] & "'"

adraughn

Instead of changing the recordsource in the code like this, why don't you create a pass thru query with a valid connection string, then when they want to run the report, you can dynamically change the sql of the pass thru query to include the parameters from the form. Then you can make the recordsource the pass thru query. It works quite well. If you would like to try that route, let me know and we will get the syntax together.

-a

Eric Harris

ASKER

Yes please
I would be very interested in the outcome. Pass thru query is a term I've heard of (I may even be already using them) but it's not one I understand immediately.
This problem can be very frustrating especially when you can't explan it to your users.
Thanks
look forward to hearing from you.

adraughn

i have a meeting this afternoon but will post back this evening when i get little chloe to bed. how's your vba? (trying to figure out how much explanation will be needed with the code)

ASKER CERTIFIED SOLUTION

adraughn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adraughn

By the way - I went ahead and put the connection string in there as #4 (which is why there are two #4's.... :) Just ignore me, it's late here on the east coast....

-a

adraughn

p.s. This is what I use for strMessage and strReport:

Dim strMessage As String
Dim strSubject As String

On Error Resume Next

strMessage = "If you cannot open the attached file, then you need to install " _
& "the SnapShot viewer utility. It can also be downloaded from Microsoft at: " _
& vbCrLf & "http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b175274" _
& vbCrLf & vbCrLf & "Make a note of the folder that you save it to.After the " _
& "download is complete, use Windows Explorer to open this folder, and install the " _
& "viewer by double-clicking on the Snapvw.exe file."
strSubject = Forms!FormName.FieldWhereUserSelectsReport

Eric Harris

ASKER

That my friend is both excellent and easy to understand.
I've tried it on a trial basis and it works fine.
I am going to apply it to all my reports the first chance I get (although it may take me some time).
In the mean time I will close the call and award you the points.
Wish I could award you more becasue that is pone of the most comprehensive answers I have ever been given.

Eric Harris

ASKER

One of the most comprehensive and accurate answers I've ever received. Absolutely brilliant.

adraughn

I'm glad it worked for you. I remember how long it took me to figure all of this out, I wish I had someone to explain it..... :)

Let me know if you run into any issues.

-adria