Results 1 to 6 of 6

Thread: passthru ms access

  1. #1
    Join Date
    Nov 2010
    Posts
    8

    passthru ms access

    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 = NothingEnd Function

  2. #2
    Join Date
    May 2006
    Posts
    407
    It would really help if you would format your question, rather than having the whole thing run together the way it is. You know, give us some paragraphs, format the code so it really is one line of code per line within the question, separate the SQL statement from the other text, etc.

  3. #3
    Join Date
    Nov 2010
    Posts
    8

    locked records

    Sorry, i copied and pasted all that and lost my formatting and did not realize it. I found my answer for that question, 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?

  4. #4
    Join Date
    May 2006
    Posts
    407
    I will assume you have double posted this message also. Stick with one forum, not multiple forums. It is not nice to have more than one person working on your problem in multiple forums. Stick to just one forum and allow whoever is helping you to see any and all other responses to your situation.

  5. #5
    Join Date
    Nov 2010
    Posts
    8
    I didn't get an answer on another forum, which is why I posted it here.

  6. #6
    Join Date
    May 2006
    Posts
    407
    Have you tested to see if the first 20 records are locked when the first user is viewing them? If you don't know how to test that, then create a second user account, go to another PC, sign-on as the second user and run the same procedure to return those 20 records.

Posting Permissions

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