DAO beginer having problems with access front end and SQL back end

[TomDuCrosbie]

I have an application with an Access project front end and a Microsoft SQL server (both are 2000 versions) backend. The backend was recently converted to SQL using the upsizing wizard. I had this DAO code that worked prior to the upsize, now it doesn't work.

Dim qdef As QueryDef
Dim StrSQL As String
Dim StrParam As String
Dim db As DAO.Database

Set db = CurrentDb
Set qdef = db.QueryDefs("ReferralResultsMailReport&quot

StrParam = Forms!frmReferralResults!txtReferralID
StrSQL = "Exec ReferralResultsMailReport" & StrParam
qdef.SQL = StrSQL



I get the error "Runtime Error "91" Object Variable or with block variable not set. The error happens on the Set qdef line. In the Locals window the db adn qdef are both set to nothing. I believe this is where the problem is.

Does the variable db need to be set to the SQL backend? and if so how? I've only ever set db to CurrentDb and all the DAO programing is what I've learned from various snippits of web sites.

Thanks in advance for any help.

Tom

 

[cmmrfrds]

Is ReferralResultsMailReport as stored procedure in sql server? You need to modify the stored procedure through that interface in Access 2000 or use Query Analyzer or some other tool. Show the code where you are executing the stored procedure. What did the wizard generate?

 

[TomDuCrosbie]

I believe that ReferralResultsMailReport is a passthrough querry on Access to a stored procedure on SQL (I'm just learning about this hole upsize thing)

This is the querry:
Alter Procedure ReferralResultsMailReport As
SELECT Customer."First Name", Customer."Last Name", Referral_Type.Name, Customer.Sold, Customer.Product, Customer.Notes, Customer.ReferalID
FROM Referral_Type INNER JOIN Customer ON
(Referral_Type.ReferralTypeID=Customer."Referred to&quot

How does the stored procedure need to be modified?

 

[cmmrfrds]

Basically the stored procedure needs a parameter. Something like.

Alter Procedure ReferralResultsMailReport
@ReferralID varchar(15)
As
SELECT Customer."First Name", Customer."Last Name", Referral_Type.Name, Customer.Sold, Customer.Product, Customer.Notes, Customer.ReferalID
FROM Referral_Type INNER JOIN Customer ON
(Referral_Type.ReferralTypeID= Customer."Referred to&quot
AND Referral_Type.ReferralTypeID = @ReferralID
Return

Not sure what this is Customer."Referred to"?

Check out your stored procedure through the stored procedure object on the Database Window under Tables.

Before upsizing you should make sure there are no Spaces or Special characters in field or table names, or any object name for that matter.

 

[TomDuCrosbie]

I put the paramaer in the Stored procedure, and it still is comming up with the same error on the same line. I should have said also that the stored procedure is the record source for a report that is emailed to the name in Customer."Referred to".

 

[cmmrfrds]

Check on the data tab for the report.

Record Source is dbo.ReferralResultsMailReport
Input Parameter is @ReferralID=YourID

 

[Onyxpurr]

Isn't DAO and ADO not available in Access 2000? Isn't it ADODB? I have this problem to and I'm trying to find out the proper way to change the parameter value in my pass-thru query EXEC code.

 

[Onyxpurr]

Oops. Refernces. Got it. Sorry.