How do I refer to a SQL query like I would a table?

• • Reply

  13 years ago

  by jodco

  jodco

  , Australia

  Joined 13 years ago

  How do I use a select query in MS Access's VB 6 to find a value, and refer to it in VB code like a table?

  I've gotten around the problem by creating a report that has a query as it's record source, but that only allows me to use the Access Queries, not VB's SQL. Not only does it not let me use SQL SELECT in VB code, it makes for a very messy Access database.

  Function Reset__Visitor_Number()
     
      Dim SQL As String
      Dim VisitorMin As Integer 'smallest call number allowed
      Dim VisitorMax As Integer 'largest call number allowed
      
      'I need to use SQL SELECT WHERE for the next two lines, but how?
      VisitorMin = Table![ClassCodes]![RangeFrom] '(where Code = Visitor)
      VisitorMax = Table![ClassCodes]![RangeTo] '(where Code = Visitor)
     
      DoCmd.SetWarnings False
         
          If DMax("[Visitors]![Call Number]", "[Visitors]") + 1 < (VisitorMin - 0.1) Then
              'if adding 1 to the visitor call number will be under the minimum then set it to the minumum and stop
              SQL = "UPDATE [Visitors] SET [Visitors].[Call number] = " & VisitorMin
              DoCmd.RunSQL SQL
             
              DoCmd.SetWarnings True
              Exit Function
          End If
         
          If DMax("[Visitors]![Call Number]", "[Visitors]") + 1 < (VisitorMax + 0.1) Then
              'if adding 1 to the visitor call number will be under the maximum, then add 1
              SQL = "UPDATE Visitors SET Visitors.[Call number] = [Visitors]![Call number] + 1"
              DoCmd.RunSQL SQL
                  Else
                  'otherwise, if the call number + 1 is over the maximum, set it to the minimum
                  SQL = "UPDATE [Visitors] SET [Visitors].[Call number] = " & VisitorMin
                  DoCmd.RunSQL SQL
          End If
         
      DoCmd.SetWarnings True

  End Function

  Report abuse
• • Reply

  13 years ago

  by TimL

  Tim Leung

  Berkshire, United Kingdom

  Joined 13 years ago

  Access actually uses VBA and not VB6.  

  I would tend to use DAO to do this type of thing. If you choose this method, make sure that you've added a reference to  the DAO object library in Tools > References. Heres an example of some code:

  http://msdn2.microsoft.com/en-us/library/bb243786.aspx 

   

  Report abuse
• • Reply

  13 years ago

  by joe90

  J Alan

  , United Kingdom

  Joined 15 years ago

  I realise that this comment may be waaaaaayyyyy to late, but if its still an outstanding problem, could you just type in an english statement of what you're trying to acheive here - without code. Sometimes, code can make the problem you are trying to solve 'more' complicated.

  Joe 

  Report abuse
• • Reply

  13 years ago

  by jodco

  jodco

  , Australia

  Joined 13 years ago

  No problem. I have found a way around it, but it is very, very slow. I use it when cross-referancing two tables, by using an ID from one table (student info) to make a SQL Select Where for another table (print options for the name tag, etc, based on the ID).

   If I use SQL Select Where, and refer to a unique key, I get one record. How do I transfer one column from that query to a variable in VB? For now, I've found a way around it, but I know there has to be a better way (I just haven't found it!).

   If I use SQL Insert, Select Where, I can add only the record I want into an empty table. If I refer to that table on a report (or form), I can take the data off the report and put it into a variable.

  Report abuse
• • Reply

  13 years ago

  by joe90

  J Alan

  , United Kingdom

  Joined 15 years ago

  If I understand you correctly, you have a select statement that returns a single row. In that row you want to access a particular columns value and you want to be able to do that in Access as a function call. Yes?

  Joe 

  Something like this will work:

  Function GetLastName(ByVal EmployeeID As String) As String
  
      Dim sql As String
     
      If EmployeeID = "" Or Not IsNumeric(EmployeeID) Then
          EmployeeID = "0"
      End If
     
      sql = "SELECT Employees.FirstName, Employees.LastName "
      sql = sql + "FROM Employees "
      sql = sql + "WHERE (((Employees.EmployeeID)=" & EmployeeID + "));"
     
      Dim rs As Recordset
      Set rs = CurrentDb.OpenRecordset(sql)
     
      If Not rs.EOF Then
          GetLastName = rs("LastName").Value
          rs.Close
      Else
          GetLastName = "unknown"
      End If
  
  End Function
   

  Report abuse
• • Reply

  13 years ago

  by jodco

  jodco

  , Australia

  Joined 13 years ago

  Thank you! I've been looking for this for months. As a note, the +'s should be &'s for Access.
  

  Report abuse