Solved

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

Posted on 2014-09-09
12
689 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

895 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

15 Experts available now in Live!

Get 1:1 Help Now