Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    19

    Unanswered: SQL to access passthru

    I want to run this query to see if i have any records come up:
    SQLRecID_Match = "Select RecID, CurrentUser, DateEntered From dbo.tbl_AR_CurrentUsers As CU"
    SQLRecID_Match = SQLRecID_Match & " Inner Join tbl_AR_" & Cmb_ClientName & "_" & strUserID & " As AR"
    SQLRecID_Match = SQLRecID_Match & " ON CU.RecID = AR.RecID"


    If no records come up, then I want to send an insert statement to sql server, otherwise a Select statement to return a recordset to access.

    I can send the code above via passthru...but how do evaluate whether records matched? so i can then create my if in access...

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can open a recordset on that SQL and test for EOF. That seems like the best solution, since you appear to want the results in a recordset anyway. If that recordset is empty (EOF), do your insert.
    Paul

  3. #3
    Join Date
    Sep 2010
    Posts
    19
    at this point I have not created a table, just sending a query to sql server to execute. I don't want to return records yet.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Then what did you mean by "otherwise a Select statement to return a recordset to access". If you just want a count, change the SQL to "SELECT Count(*)..." and get a recordset on that, which will just be the number of records, which could be zero.
    Paul

  5. #5
    Join Date
    Sep 2010
    Posts
    19
    I want to evaluate the result from my passthru select statement...how do i do that without linking an actual table or runing the query from access and not sql?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You don't need a table in Access, and with the proper connection string that query will be running on SQL Server, and just the results passed back. If you want to evaluate the result, something has to be passed from SQL Server back to Access. If that's a passthrough query, you can run a DCount() on it.
    Paul

  7. #7
    Join Date
    Sep 2010
    Posts
    19
    I tried this and my msgbox does not popup and it falls thru on the if. but when I do a watch on rst.RecordCount it says <object variable or With block variable not set>

    Dim db2 As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As Recordset

    'Set dbs = CurrentDb
    Set qdf = db2.CreateQueryDef
    With qdf
    .Name = "RecMatch"
    .Connect = strConnection
    .SQL = SQLRecID_Match
    Set rst = .OpenRecordset()
    rst.MoveLast
    rst.MoveFirst
    MsgBox (rst!RecID)
    If rst.RecordCount <> 0 Then
    MsgBox ("has recs")
    Else
    MsgBox ("No recs")
    End If
    '.ReturnsRecords = (Len(QueryName) > 0)
    'If .ReturnsRecords = False Then
    ' .Execute
    'Else
    ' If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
    ' dbs.QueryDefs.Append qdf
    'End If
    '.Close
    End With

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I personally wouldn't bother with the QueryDef, or I'd re-use an existing one. Just tested this:

    Code:
      Dim rst                     As ADODB.Recordset
    
      EstablishConnection    'public function to set connection object to SQL Server connection string
    
      Set rst = New ADODB.Recordset
      rst.ActiveConnection = objConn    ' this is opened by the function above
      rst.Open "SELECT DriverNum, ArriveDateTime,  [dbo].[funcDriverName](DriverNum) AS DriverName " _
             & "FROM tblFirstUp ORDER BY ArriveDateTime"
    
      If Not rst.EOF Then
        MsgBox rst!DriverNum
      Else
        MsgBox "No records"
      End If
    
      ReleaseConnection    'public function to close connection object
      Set rst = Nothing
    And this is existing working code that uses an existing pass through query:

    Code:
    Public Function CheckCar(varCarNum As Variant) As Boolean
      Dim strSQL        As String
      Dim db            As DAO.Database
      Dim rs            As DAO.Recordset
    
      On Error GoTo ErrorHandler
    
      Set db = CurrentDb()
    
      If Not IsNumeric(varCarNum) Then
        CheckCar = False
        GoTo ExitHandler
      End If
    
      strSQL = "SELECT Count(*) AS HowMany FROM CarMaint.dbo.Car_Records " _
             & "WHERE car_no = " & varCarNum & " AND Company_no = '" & Me.CarComp & "'"
    
      PassThroughFixup "qryCheckCar", strSQL, , True
    
      Set rs = db.OpenRecordset("qryCheckCar", dbOpenDynaset)
      If rs!HowMany > 0 Then
        CheckCar = True
      Else
        CheckCar = False
        MsgBox "Vehicle number not valid for selected company"
      End If
    
    ExitHandler:
      Set rs = Nothing
      Set db = Nothing
      Exit Function
    
    ErrorHandler:
      Select Case Err
        Case Else
          MsgBox Err.Description & " in CheckCar "
          DoCmd.Hourglass False
          Resume ExitHandler
      End Select
    End Function
    PassThroughFixup is just a public function that sets the named query's SQL to the SQL passed to it.
    Paul

  9. #9
    Join Date
    Sep 2010
    Posts
    19

    Locked records

    Thanks this is very helpful. Now I have another question about the passthru. I am using SQL server as my back end and I have multiple users that will be using this app. It is possible that 1 user will query say 20 records and another user could query the same records. I want to lock the records until the first user releases them. How do I do this? and how do I notify the second user that the records are locked by another user?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question new thread please..

    dem's da rules
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •