MS Access Report - Certificate report and how to link to SSMS

Hi Experts,

I have a report in MS Access I select the textbox option in put this into the control source:
[Date]
[Number]
[Name]
[Cat1]
[etc..]


The SP is in ssms so:
In the VBA
Strsqr() -- I know this is not correct please correct


also in VBA don't I need some parameters? like pmrcat1?  so I will need help with that as well.
I know that the user is going to select category (Cat1)

The SP name\pamr is stored in a Access Table(s)

so in ssms:
Select
[Date]
,[Number]
,[Name]
,[Cat1]
,[etc..]
From Tables involved TI.
Where TI.Cat1 = @Cat1

I think I am correct but may need more help?

Please help and thanks
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harisrashidCommented:
In VBA use the DoCmd.RunSQL Method.
It is explained in detail in Microsoft article at https://msdn.microsoft.com/en-us/library/office/Ff194626.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
But doesn't that explanation mean to use the query in access?

I actually have to use the SP in SQL Server SSMS, so that is why I have the SP named in a Access table, when using strsql().  Plus I have to follow policy.

With that mentioned am I following correctly on how to create a report using SQL Server?

Thinking about it I don't have to send a parameter to the report because SP takes care of that.
Where TI.Cat1 = @Cat1

Please help and thanks
0
PatHartmanCommented:
If the query doesn't need arguments, then create a pass-through query to call the sp.  Bind the report to the pass-through query. The query would look like:

EXEC spGetClientData

If you need to pass in arguments (and it sounds like you do even though you don't think so), it's a little more complex.  In the click event of the button that opens a report, I create a temp querydef which the report is bound to.  The connection string is stored in a table so it is easy to change.  The DLookup() gets it.  I also included the error trap since you will get an error if the temp qd already exists.  So the error code deletes it and then resumes.
    Set db = CurrentDb
    strConn = DLookup("SQLConnectionString", "UsysConnectionString", "LinkedID = 1")
    
    strSQL = "EXEC spGetClientData @ClientID = " & Forms!frmClients!txtClientID
    Set qd = db.CreateQueryDef("qdTemp")      'temp name
        qd.Connect = strConn
        qd.SQL = strSQL
        qd.ReturnsRecords = True
        
    DoCmd.OpenReport "rptClientServiceHistory", acViewPreview      
Exit_Proc:
   Exit Sub

Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Next
        Case 3012   'qdTemp already exists
            db.QueryDefs.Delete "qdTemp"
            Resume

        Case Else
            MsgBox Err.Number & "--" & Err.Description
            MsgBox "There was an error opening the Care Plan.  Please report previous msg number to Pat.", vbOKOnly
            Resume Exit_Proc
            Resume
    End Select

Open in new window

0
Amour22015Author Commented:
Ok, thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.