Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fast and better method to find in mdb (2000)

    What kind of field is COPE? A text field, or a number field, or a date field, or a Yes/No field?

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    A text field...
    sorry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fast and better method to find in mdb (2000)

    Have a look at this web page using automation, from the looks of it, it can easily be adapted to fit your question


    http://www.mvps.org/access/modules/mdl0035.htm
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fast and better method to find in mdb (2000)

    Try this: set a reference to the Microsoft ActiveX Data Objects 2.1 Library. Use code like the following:
    <code>
    Dim strConnection As String
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    ' You know what to fill in here
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=...;"
    cnn.Open strConnection
    rst.Open "anafgrafica1", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    ' Important: use the name of the index here
    rst.Index = "COPE"
    rst.Seek Chr(34) & TEST_RECORDS & Chr(34)
    If rst.EOF Then
    MsgBox "Not found"
    Else
    var_NOME = rst!NOME
    var_SETTORE = rst!SETTORE
    End If
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing</code>

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    Chr(34)="
    ?????????

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    hi Jerry...
    hve se the link but i dont understand the vantage of this for my prob..(?)

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fast and better method to find in mdb (2000)

    Please in VBA for Excel

    I want to use a macro to find a value in mdb with 1.245.876 records.
    In mdb named anagrafica have a table named anagrafica1 in table have filed COPE, NOME, SETTORE
    COPE is indexed and have first primary key
    Now admit have a var named TEST_RECORDS find the value of this var in COPE and if the value is matching retrive value from NOME and SETTORE and store in var_NOME and var_SETTORE

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fast and better method to find in mdb (2000)

    OK, my apologies, it must the translation of Italian to English <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I thought you wanted to transfer data to Excel from Access
    Jerry

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    no prob...;-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fast and better method to find in mdb (2000)

    You must put quotes " before and after a text value. You can use Chr(34) to insert quotes into a string in VBA. See Chr() function and quotes within strings (all).

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    Hi Hans the code work only if i use, rst.Seek COPE_MDB, and not rst.Seek Chr(34) & COPE_MDB & Chr(34), possible causes?
    I have set dim COPE_MDB as string.
    And filed COPE in mdb is Text and idexed

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fast and better method to find in mdb (2000)

    OK, then use

    rst.Seek COPE_MDB

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    ok....

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast and better method to find in mdb (2000)

    Dubt...
    In effect with other excel macro various user call the MDB in reference with the same string of connection, i can have prob if 2 separate appliaction calliing the same MDB?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fast and better method to find in mdb (2000)

    Access is a multi-user application, it can handle several users at the same time. But if you have more than about 15 users retrieving data at the same time, it'll become slow.

Page 1 of 2 12 LastLast

Posting Permissions

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