Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    OceanaPolynom is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    20

    VBA only finds one record

    Hello
    I wrote some code that goes like this:



    Code:
     
                      .
                      . 
                      .
            BeginTrans
            rstObj1.AddNew
            rstObj1!clcirclespt1 = an$
            rstObj1!clcirclespt2 = bn$
            rstObj1!clcirclespt3 = cn$
            rstObj1!clcirclesceny = fcy
            rstObj1!clcirclescenx = fcx
            rstObj1!clcirclescenz = fcz
            rstObj1!clcirclesr = rf
            rstObj1.Update
            CommitTrans
    
    Next pdf&
    When the loop finishes there are hundreds of row in the db table. Next I need to access the new data in a different subroutine. When I run this subroutine:

    Code:
           .
           .
           .
        Dim y(), x() As Double
        ssql$ = "select * from tblclcircles"
        Set rstObj2 = dbsObj.OpenRecordset(ssql$, dbOpenDynaset)
        amt& = rstObj2.RecordCount
        rstObj2.MoveFirst
        ReDim y(0 To amt&), x(0 To amt&)
        For iii& = 1 To amt&
            y(iii& - 1) = rstObj2!clcirclesceny
            x(iii& - 1) = rstObj2!clcirclescenx
            rstObj2.MoveNext
        Next iii&
        For iii& = 0 To amt& - 1
                       .
                       .
                       .
    The record count is always only 1 record
    Thanks

    John

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    try a movelast before getting your recordcount.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Also, if your record source is a linked table, the record count will always be shown as -1 (with the negative sign) anyway.

    You would need to use DCount instead for linked tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    data add will run many times faster if you use queries, instead of using code to loop thru data adding 1 record at a time.

  5. #5
    OceanaPolynom is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    20

    This seems to work

    Quote Originally Posted by moke123 View Post
    try a movelast before getting your recordcount.
    THis seems to be the answer. Thank you very much.

    John

  6. #6
    OceanaPolynom is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    20
    I don't understand what you mean, but I would really like to know.
    Thank you
    John

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Dim y(), x() As Double
    Does not do what you think it does. This declares y() as VARIANT and x() as Double.
    Should be:
    Code:
    Dim y() As Double, x() As Double




    These variable types have been depreciate. They are there for backward compatibility, but MS could remove them at any time.
    $ String
    % Integer
    & Long
    ! Single
    # Double
    ## _FLOAT

    Use "Dim" statements to declare variables.
    Instead of USE
    MyName$ DIM MyName AS STRING
    Num1% DIM Num1 AS INTEGER
    Num2& DIM Num2 AS LONG
    Amt! DIM Amt AS SINGLE

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by OceanaPolynom View Post
    I don't understand what you mean, but I would really like to know.
    Thank you
    John
    And I don't understand whose answer you are referring to - unless you specify, none of us will answer!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I took it to be ranman256's Post #4.....

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're probably right Steve
    Thanks for the RPs
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    OceanaPolynom is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    20

    Sorry for the misunderstanding

    Quote Originally Posted by ridders52 View Post
    You're probably right Steve
    Thanks for the RPs
    Hello
    When I wrote that I did not understand I was replying to Ranman256's post which said "data add will run many times faster if you use queries, instead of using code to loop thru data adding 1 record at a time." Thanks also for the information about declaring data types. I have been programming basic since 1983 (GW-Basic that is Greg Whitten Basic), so my knowledge is somewhat out of date. I am still trying to understand what was meant by using queries to increase performance.
    Thank you all very much
    John

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ranman's point is that recordset code is performed one record at a time looping through all the records
    Whereas action queries or the SQL equivalent effectively all are done at once.

    If you only have a few records, you won't notice the difference.
    However, for large datasets, the performance improvement will be significant

    Having said all that, this will only work if you are appending, updating or deleting records
    I'm not sure what your recordset code is actually doing as there is no .Edit or .Update shown

    But if you aren't editing or updating records, a recordset may be pointless anyway.
    Depending on what you are doing, there is probably a better way of achieving the same result
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    OceanaPolynom is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    20
    Hello ridders52
    I am using VBA inside AutoCad. The Access db is used to hold a large amount of co-ordinate data. The data is from a laser scanner and is in the form of y,x,z.csv file. The place where I input the data into the db is a bottleneck because there are many millions of points. Presently i am using this code

    Code:
    Do
            
            Input #1, ya$
            Input #1, xa$
            Input #1, za$
            
           
            
            rstObj.AddNew
            
            rstObj!yxdata = Val(ya$) * 1000
            rstObj!xxdata = Val(xa$) * 1000
            rstObj!zxdata = Val(za$) * 1000
            rstObj.Update
       
    Loop Until EOF(1)
    It input ~6,000,000 records in 2 to 3 minutes, which was ok but not really that many points.
    Since I am only creating new records, not appending, updating or deleting records, this idea may not apply. Any ideas or suggestions you have will be greatly appreciated.
    Thanks
    John

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Bear in mind that I've never used VBA in AutoCAD ...

    However:
    It input ~6,000,000 records in 2 to 3 minutes, which was ok but not really that many points.
    More than enough to make it slow down by using a recordset

    Since I am only creating new records, not appending, updating or deleting records, this idea may not apply. Any ideas or suggestions you have will be greatly appreciated.
    APPENDING means creating new records!

    So definitely try replacing all the recordset code with SQL append code.

    Use something like this, replacing MyCSVFileName with the actual file name & tweaking if necessary

    Code:
    CurrentDb.Execute "INSERT INTO tblclcircles ( yxdata, xxdata, zxdata )" & _        
                " SELECT '" & Val(ya$) * 1000 & "', '" & Val(xa$) * 1000 & "', '" & Val(za$) * 1000 & "'" & _
                " FROM MyCSVFileName;", dbFailOnError
    I'm assuming each of the fields are text datatype, If numbers, remove the single quote text delimiters

    Before you try changing it, add these lines before the recordset
    Code:
    Dim Start As Long, Finish As Long
    Start = Timer
    and these two lines afterwards
    Code:
    Finish = Timer
    Debug.Print "Time taken = (Finish - Start) & " seconds"
    Note the time taken (shown in immediate window)

    Try the SQL append code instead and note the time taken again
    Let us know the results
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    OceanaPolynom is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    20
    Hello
    This is very interesting. I will try it out and let you know the results.
    Many thanks
    John

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-22-2015, 05:50 AM
  2. Replies: 7
    Last Post: 04-14-2014, 01:44 PM
  3. Only the first record it finds is being updated
    By jwill in forum Programming
    Replies: 16
    Last Post: 12-20-2013, 04:14 PM
  4. Replies: 2
    Last Post: 07-25-2013, 08:53 AM
  5. Query Can it be Done? Mulitple finds
    By Canadiangal in forum Queries
    Replies: 3
    Last Post: 02-28-2010, 03:45 PM

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