Results 1 to 4 of 4
  1. #1
    nkuebelbeck is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    91

    linked mysql database

    Access 2013 x64, mysql 5.5.31



    I've got this vba sub routine. When I call the sub it always returns 1. Any help is much appreciated.

    It's a simple cocktail program.

    Sub Order(recipe_id As Integer, recipe_qty As Integer)
    Dim msg As String
    Dim dbs As Database
    Dim rs As Recordset
    Dim strSQL As String
    Set db = CurrentDb()
    Dim count As Integer

    strSQL = "SELECT * FROM `ingredients` WHERE recipe_id = " & recipe_id & ";"
    Set rs = dbs.OpenRecordset(strSQL)
    count = rs.RecordCount
    MsgBox (count)
    End Sub


    I've never used SQL in VBA before so there is a good chance I'm doing something completely wrong

  2. #2
    nkuebelbeck is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    91
    I actually think it's working. I have to iterate through the records to increase the count....odd

    Found it here

    http://allenbrowne.com/ser-29.html

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Saw a couple of errors in your code.
    You have
    Dim dbs As DAO.Database

    but then you have
    Set db = CurrentDb()

    Do you have "Option Explicit" as one of the first lines of each code module?

    If there are records, the recordcount property is 1 until you move to the last record. Then you get the true record count.

    I also modified your code: (my changes are in Blue)
    Code:
    Option Compare Database  '< should be at the top of the module
    Option Explicit          '< should be at the top of the module
    
    Sub Order(recipe_id As Integer, recipe_qty As Integer)
       Dim dbs As DAO.Database
       Dim rs As DAO.Recordset
    
       Dim strSQL As String
       Dim count As Integer
       Dim msg As String  '< unused
    
       '   Set db = CurrentDb()
       Set dbs = CurrentDb()
    
       ' set default
       count = 0
    
       strSQL = "SELECT * FROM `ingredients` WHERE recipe_id = " & recipe_id & ";"
       Set rs = dbs.OpenRecordset(strSQL)
       If Not rs.BOF And Not rs.EOF Then
          rs.MoveLast
          count = rs.RecordCount
       End If
    
       rs.Close
       Set rs = Nothing
       Set dbs = Nothing
    
       MsgBox (count)
    End Sub

  4. #4
    nkuebelbeck is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    91

    yup

    Quote Originally Posted by ssanfu View Post
    Saw a couple of errors in your code.
    You have
    Dim dbs As DAO.Database

    but then you have
    Set db = CurrentDb()

    Do you have "Option Explicit" as one of the first lines of each code module?

    If there are records, the recordcount property is 1 until you move to the last record. Then you get the true record count.

    I also modified your code: (my changes are in Blue)
    Code:
    Option Compare Database  '< should be at the top of the module
    Option Explicit          '< should be at the top of the module
    
    Sub Order(recipe_id As Integer, recipe_qty As Integer)
       Dim dbs As DAO.Database
       Dim rs As DAO.Recordset
    
       Dim strSQL As String
       Dim count As Integer
       Dim msg As String  '< unused
    
       '   Set db = CurrentDb()
       Set dbs = CurrentDb()
    
       ' set default
       count = 0
    
       strSQL = "SELECT * FROM `ingredients` WHERE recipe_id = " & recipe_id & ";"
       Set rs = dbs.OpenRecordset(strSQL)
       If Not rs.BOF And Not rs.EOF Then
          rs.MoveLast
          count = rs.RecordCount
       End If
    
       rs.Close
       Set rs = Nothing
       Set dbs = Nothing
    
       MsgBox (count)
    End Sub

    I found those errors after, the biggest problem was not understand the RecordCount method, in my experience you didn't have to iterate through them all to get the correct count...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-20-2013, 11:39 AM
  2. Replies: 1
    Last Post: 08-24-2012, 07:11 AM
  3. Replies: 5
    Last Post: 05-16-2012, 12:48 AM
  4. MySQL Linked Tables - Carriage Return
    By warrenk in forum Access
    Replies: 1
    Last Post: 03-28-2010, 01:56 AM
  5. Access database to Mysql
    By fsmikwen in forum Access
    Replies: 4
    Last Post: 11-16-2009, 05:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums