Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

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

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
Fordraiders
Asked:
Fordraiders
  • 7
  • 5
1 Solution
 
Kelvin SparksCommented:
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
 
FordraidersAuthor Commented:
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
 
Kelvin SparksCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
FordraidersAuthor Commented:
docmd.openquery  rs   ?
0
 
Kelvin SparksCommented:
Set rs = .Execute should fill the recordset with the results of the strSQL

Kelvin
0
 
FordraidersAuthor Commented:
i get a type mismatch..

List2.RowSource = rs
0
 
Kelvin SparksCommented:
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
 
Kelvin SparksCommented:
You might need to run this in the line before the one I gave you

Me.List2.RowSourceType = "Table/List"
0
 
Kelvin SparksCommented:
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
 
FordraidersAuthor Commented:
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
 
Kelvin SparksCommented:
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
 
FordraidersAuthor Commented:
thanks for the help !
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now