Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2003
    Location
    Charleston, SC
    Posts
    14

    Red face Unanswered: Query Problems... :(

    Ok, I have a form where user enter in information of course. On one of the fields they enter, the database looks into a linked table to find related information. The table it looks into has 855625 records. Finding records at the top is easy. But the Low number or even middle numbers causes the database to crash. Its a simple SQL statement and Dlookup is simple as well. Is there better way to do this?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Are you perhaps using an ODBC connection to make this link? If you are, that might be at least a partial explanation.

    Sam

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are hitting the multi hundred thousand records then probably you need to consider moving to a server data store.

    Do you know why your app crashes, what error message do you get - is it a time out problem?

    Where is the information in the multithousand table stored
    It maybe you are using an inapproprariate means of accessing the data.

    what's the dlookup you are using
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2003
    Location
    Charleston, SC
    Posts
    14
    Well, I guess crash is a bad word. Access just stops responding.

    Here is the coding I used. Its very simple.
    Code:
    Sub GetUnitData()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim sql As String
    
    Set db = CurrentDb()
        
    sql = "SELECT [ASSEMBLY_NUMBERS].[SerialNum], [ASSEMBLY_NUMBERS].[Model], [ASSEMBLY_NUMBERS].[AseemblyNum] FROM ASSEMBLY_NUMBERS WHERE [ASSEMBLY_NUMBERS].[SerialNum] ='" & Me.SerialNo & "';"
        
        Set rst = db.OpenRecordset(sql)
         
        If rst.EOF = False Then
            MsgBox "Referrence Information: " & vbCrLf & _
                    "-----------------------------     " & vbCrLf & _
                    "Serial Number: " & rst("SerialNum") & vbCrLf & _
                    "Model: " & rst("Model") & vbCrLf & _
                    "Assembly Number: " & rst("AseemblyNum"), vbInformation, "Referrence Info"
        Else
            MsgBox "This Serial Number could not be located.", vbInformation, "Referrence Info"
        End If
        rst.Close
        Set rst = Nothing
    
    End Sub
    I also tried making a query and telling it to look for a value near the bottom, access couldn't complete that task either.

    I also tried this:
    Code:
    If me.serialno.value = Dlookup("SerialNum","Assembly_Numbers", "[SerialNum"]='" & me.serialno.value & "'") Then
            MsgBox "Referrence Information: " & vbCrLf & _
                    "-----------------------------     " & vbCrLf & _
                    "Serial Number: " & rst("SerialNum") & vbCrLf & _
                    "Model: " & rst("Model") & vbCrLf & _
                    "Assembly Number: " & rst("AseemblyNum"), vbInformation, "Referrence Info"
        Else
            MsgBox "This Serial Number could not be located.", vbInformation, "Referrence Info"
        End If
    Neither worked.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SerialNumber is a number, or text?

    query version:

    Dim db As Database
    should be
    Dim db As DAO.Database

    instead of
    If rst.EOF = False Then
    i use
    if not (rst.eof and rst.bof) then
    but that is a detail

    dlookup version

    i would be tempted
    a/ to avoid dlookup
    b/ otherwise to use
    If me.serialno.value = nz(dlookup(blahblah),"")


    apart from those trivial details, it looks as tho it should work


    you use interesting words "...the table it looks into..."
    where is this table?
    is it native JET or ???
    how big is the table?

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2003
    Location
    Charleston, SC
    Posts
    14
    Hey,

    "looks Into" is a general term for search I guess. It looks up a serial number in a table. I'm not the best with the terms I guess. Anyway, the table that it looks into is 4 columns wide and 855,625 records deep. I normally don't Dim a db at all. Thats a differnet connection string than I normally use because i'm trying different things to see if I can get it to work. Normally I use this:

    Code:
    Dim conn as object
    Dim SQL as string
    Dim rst as object
    
    Set conn = Application.CurrentProject.Connection
    SQL = "SELECT * TABLE"
    Set rst = CreateObject("ADODB.Recordset")
    
    rst.Open SQL, conn
    But that isn't working either because the query has to search through too much information. This might be a lost cause. I wish I could just through this in our SQL server but IT admin doesn't like the idea. thnx for your help.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This is a tangental complaint of mine (ongoing from my VC++ days) ... I HATE referring to recordset columns by their names ... It is so damned easy for that driver to screw up which column you're wanting to see. I always reference columns by their ordinal numbers ...

    Per Izy's question: Serial # is text. SO now you're searching Access using the most inefficient means possible ... Text Matches. No wonder it's slow ...

    Is the Serial # indexed?
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Dec 2003
    Location
    Charleston, SC
    Posts
    14
    Yes, I'm not a big fan of access. I feel its slow in general. I wish the serial number wasn't alphanumeric because yes it would be a faster search. I did index the column of course, but it did no help whatsoever. Thnx though.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tready29483
    Yes, I'm not a big fan of access. I feel its slow in general. I wish the serial number wasn't alphanumeric because yes it would be a faster search. I did index the column of course, but it did no help whatsoever. Thnx though.
    Here's another thought: Is the serial # STRICTLY numeric? No alpha in it? If that's the case, you could change your comparison to: ... WHERE (Clng([SerialNum])=Clng(" & Me.SerialNo & ") ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Here's another thought: Is the serial # STRICTLY numeric? No alpha in it? If that's the case, you could change your comparison to: ... WHERE (Clng([SerialNum])=Clng(" & Me.SerialNo & ") ...
    Hi Mike
    Long time no post

    I would have thought this would be the worst of both worlds - 800K data conversion functions? Worth a stab though of course. If it is numeric perhaps converting the column would be an option.

    BTW - have you compacted the BE recently? This gets your indexes into line again. I believe that if the table is highly transactable this could result in page splits in the future however.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - is the serial number a PK and\ or a unique index? I believe specifying unique helps JET retrieve the data.

    Also, is it part of a composite index?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2003
    Location
    Charleston, SC
    Posts
    14
    Quote Originally Posted by M Owen
    Here's another thought: Is the serial # STRICTLY numeric? No alpha in it? If that's the case, you could change your comparison to: ... WHERE (Clng([SerialNum])=Clng(" & Me.SerialNo & ") ...
    No, alphanumeric means letter and numbers. If they where numbers my sql wouldn't work. It would give a data type mismatch error.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tready29483
    No, alphanumeric means letter and numbers. If they where numbers my sql wouldn't work. It would give a data type mismatch error.
    I think Mike meant is the content of the text field numeric only in which case your SQL and Mikes would work.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Hi Mike
    Long time no post

    I would have thought this would be the worst of both worlds - 800K data conversion functions? Worth a stab though of course. If it is numeric perhaps converting the column would be an option.

    BTW - have you compacted the BE recently? This gets your indexes into line again. I believe that if the table is highly transactable this could result in page splits in the future however.
    Dan,

    Versus 800K of string comparisons???? I'll take my chances on the numbers ... (As I know you would to)

    Haven't run across any posts that were in need of dire reply ... I was surprised at Ted and Neal being made moderators tho ...
    Back to Access ... ADO is not the way to go for speed ...

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    I was surprised at Ted and Neal being made moderators tho ...
    Dead easy to become one if you fancy making a play for power. Have you checked out Chat the forums and seen SoftWareRevue (aka Dennis)? He is the new moderator - suddenly there is a bustle and hustle in the air. Who's Neal tho? Enquiring minds want to know...

    I have chosen to remain a regular user - Fight the Power
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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