passthru with ms access

[abenitez77]

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"

strConnection = "ODBC;DRIVER={SQL Server};SERVER=" & strSQLServer & ";DATABASE=" & strSQLDatabase & ";TRUSTED_CONNECTION=Yes;" '"Toys_2008_TPR;TRUSTED_CONNECTION=Yes;"

Call SQL_PassThrough(strConnection, SQLRecID_Match, strQueryName)

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

below is my passthru function:
Function SQL_PassThrough(ByVal ConnectionString As String, _
ByVal SQL As String, _
Optional ByVal QueryName As String)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef
With qdf
.Name = QueryName
.Connect = ConnectionString
.SQL = SQL
.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
Set qdf = Nothing
Set dbs = Nothing

End Function

 

[abenitez77]

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

 

[SkipVought]

are CU.RecID & AR.RecID both the same data type?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abenitez77]

they are both the same type, yes. I found my answer, but I have another related question. 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?

 

[SkipVought]

I found my answer

Then please state you answer before we proceed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abenitez77]

i made a change to :

strSQL = "Select cont(*) as HowMany from mytable"
SQL_PassThrough "qryCheckCar", strSQL, , True

Set rs = db.OpenRecordset("qryCheckCar", dbOpenDynaset)
If rs!HowMany > 0 Then
"has recs"
Else
"Does not have recs"
End If

 

[SkipVought]

It would be best if you would post a new question in a new thread. The reason is obvious, for your benefit.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}