Solved

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

Posted on 2014-09-09
12
696 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

717 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