Solved

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

Posted on 2014-09-09
12
692 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

713 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