Solved

execute sql server stored procedure to open a query in access and passing 2 parameters

Posted on 2014-09-09
12
688 Views
Last Modified: 2014-09-09
this code does not seem to be working. all parameters correct...?
 
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
   
    Dim Wave As Integer
    Dim Lane As String
   
    Dim strConnection As String
    Dim s1 As String
    Dim s2 As String
   
   
    strConnection = "DSN=SQLServer_CRS;UID=xxbr1dxp;PWD=xxsqlc$2e;DATABASE=Xref_Qa"
 
   
    'Create a new ADO Connection object
    Set cn = New ADODB.Connection
   
    cn.Connectionstring = strConnection
    cn.Open
   
    s1 = "09/9/2014"
    s2 = "BR2GCK"
   
    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "XRef_Emp_Hourly_Report '" & s1 & "', '" & s2 & "'"
    cmd.ActiveConnection = cn
    Set rs = cmd.Execute
 
    'Set the form's Recordset property to the ADO recordset
   ' Set Me.Recordset = rs
    DoCmd.OpenQuery rs
    Set rs = Nothing
    Set cn = Nothing



Getting an error:
sql server driver syntax error ?

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 7
  • 5
12 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40313150
I'd normally expect the connection string to be more like

"ODBC;Driver={SQL Server};Server=servername;Database=Xref_Qa;xxbr1dxp;PWD=xxsqlc$2e"

Kelvin
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40313228
ok this is working but cant get it so i can view it ?

Dim s1 As String
    Dim s2 As String
   
       
    s1 = "9/9/2014"
    s2 = "BR2GCK"
   
   
''''''''''''''''''''''''''''''''''
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
   
    Dim strSQL As String
   
    Dim strConnection As String
   
    strSQL = "EXEC XRef_Emp_Hourly_Terms_Report '" & s1 & "', '" & s2 & "'"
    strConnection = "DSN=SQLServer_CRS;UID=xxxbr1dxp;PWD=xxxsqlc$2e;DATABASE=XRef_QA"
   
' Create a new ADO Connection object
    Set cn = New ADODB.Connection
    cn.Connectionstring = strConnection
    cn.Open
   
    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .Open Source:=strSQL, _
        ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic
        If .RecordCount = 0 Then
            MsgBox "No Records Found"
        Else
            Set Recordset = rs
        End If
        .Close
    End With
   
   
   
   
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40313331
The way I have done these before is to use a command object

Dim cn As ADODB.Connection
Dim cmd as  New ADODB.Command
 Dim rs As New ADODB.Recordset

 Dim strConnection As String
     
     strSQL = "EXEC XRef_Emp_Hourly_Terms_Report '" & s1 & "', '" & s2 & "'"
     strConnection = "DSN=SQLServer_CRS;UID=xxxbr1dxp;PWD=xxxsqlc$2e;DATABASE=XRef_QA"
     
 ' Create a new ADO Connection object
     Set cn = New ADODB.Connection
     cn.Connectionstring = strConnection
     cn.Open

SET cmd = New Command
With cmd
       .ActiveConnection = cn
       .CommandText = strSQL
       .Prepared = True
      Set rs = .Execute

.........Whatever you need from here


Kelvin
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40313537
docmd.openquery  rs   ?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40313578
Set rs = .Execute should fill the recordset with the results of the strSQL

Kelvin
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40313599
i get a type mismatch..

List2.RowSource = rs
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40313612
OK, that is a new issue.

Have you checked that the recordset actually has data?

If it has try

Set Me.List2.Recordset = rs

Kelvin
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40313614
You might need to run this in the line before the one I gave you

Me.List2.RowSourceType = "Table/List"
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40313616
And once populated don't forget to close recordset and connection

rs.Close
cn.close

And to be very tidy
then use

Set rs = Nothing
Set cmd = Nothing
Set cnn = Nothing


Kelvin
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40313661
kevin,
a different approach, but it works.
Dim s1 As Date
    Dim s2 As String
    Dim t As Integer
        Dim qdfNew As DAO.QueryDef
    s1 = "9/9/2014"
    s2 = "BR2GCK"
   
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
CurrentDb.QueryDefs.Delete ("qryLog")
Set qdf = CurrentDb.CreateQueryDef("qryLog")
qdf.Connect = CurrentDb.TableDefs("dbo_iData_Desc_Search_Log").Connect
qdf.SQL = "EXEC XRef_Emp_Hourly_Terms_Report '" & s1 & "', '" & s2 & "'"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
'Debug.Print rst(2).Value
rst.MoveFirst
rst.MoveLast

DoCmd.OpenQuery "qryLog"
rst.Close
Set rst = Nothing
Set qdf = Nothing
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 40313665
Great when something works. I suspect you were getting cross issues between DAO and ADODB. Was actually contemplating that very issue as your post above came in.

Glad that you could find your way through it.

Kelvin
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40313667
thanks for the help !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now