Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to speed up conn with mdb... (2000 sr-1)

    Wizard Hans.....
    First i am very happy to see the forum on line, and very happy to see you!
    I have this sheet with a form. This sheet have into form code a conn to MDB and other module of this sheet have other conn to MDB.
    I have see if a user use open the wbook and work with the userform the time of reception of info is very very slow... existis a method to speed the time of "reaction"

    http://www.mytempdir.com/644114

    Note: the dir and mdb have all permission about "all user"

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    Network speed and the size of the database are important factors, but in general retrieving data from a closed database is slow. Do you really need to use a database for this? Why not put the usernames in a hidden worksheet, or if you want to store them outside the workbook, in a text file? Reading from a text file is much faster.

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    HUMMMMM "Reading from a text file is much faster."...
    Dubt:

    1) but is possible to use a txt file similar a sheet to make a lookup?
    2) is much faster also if the txt in on a dir server?

    tsk

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    1) If it is a tab-delimited text file, you can open it in Excel and use Find or VLookup in code.
    2) It will certainly be faster than retrieving information from a database.

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    WOW.....
    Attached is the txt file(the first line is header of title)
    Attached is a sheet with the column A filled with a value for index.

    Assuming i would want to retrive the result from each value into column A into column B of sheet?

    If you make a sample code, this is the very big GIFT...!!!!!

    Assuming the txt file is in:
    Global Const PATH = "GCD01F4500DATIPUBBLICAINPSCARTEL1.TXT"

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    Here you go:

    Global Const PATH = "GCD01F4500DATIPUBBLICAINPSCARTEL1.TXT"

    Sub RetrieveNames()
    Dim wbkCartel As Workbook
    Dim wbkCurrent As Workbook
    Dim wshCartel As Worksheet
    Dim wshCurrent As Worksheet
    Dim rngCartel As Range
    Dim lngRow As Long
    Dim lngMaxRow As Long

    On Error GoTo ErrHandler

    ' The current workbook
    Set wbkCurrent = ActiveWorkbook
    Set wshCurrent = wbkCurrent.Worksheets("Foglio1")

    ' Open the text file
    Workbooks.OpenText Filename:=PATH, Origin:=xlWindows, _
    DataType:=xlDelimited, Tab:=True
    Set wbkCartel = Workbooks("Cartel1.txt")
    Set wshCartel = wbkCartel.Worksheets(1)
    ' The lookup range
    Set rngCartel = wshCartel.Range("A1").CurrentRegion

    ' Loop through column A
    lngMaxRow = wshCurrent.Range("A65536").End(xlUp).Row
    For lngRow = 2 To lngMaxRow
    ' Use VLookup to fill column B
    wshCurrent.Range("B" & lngRow) = Application.VLookup _
    (wshCurrent.Range("A" & lngRow), rngCartel, 2, False)
    Next lngRow

    ExitHandler:
    On Error Resume Next
    ' Close the text file
    wbkCartel.Close SaveChanges:=False
    ' Set object variables to Nothing
    Set rngCartel = Nothing
    Set wshCartel = Nothing
    Set wbkCartel = Nothing
    Set wshCurrent = Nothing
    Set wbkCurrent = Nothing
    Exit Sub

    ErrHandler:
    ' Report error
    MsgBox Err.Description, vbExclamation
    ' Go to exit handler section.
    Resume ExitHandler
    End Sub

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    GREAT!!!!!!!!

    but is possible to use this macro similar a "function"....

    Assuming i have this part of code:
    =================
    D = Cells(RIGA, "AB").Value
    MATRICOLA = D
    Me.TextBox5.Value = RetrieveNames(MATRICOLA)
    E = Me.TextBox5.Value
    ===================

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    Yes, but keep in mind that each time you use this function, the text file is opened and closed. If you use it a lot, it would be much more efficient to put the list in a (hidden) worksheet in the workbook itself.

    Function RetrieveNames(Matricola As String)
    Dim wbkCartel As Workbook
    Dim wshCartel As Worksheet
    Dim rngCartel As Range

    On Error GoTo ErrHandler

    ' Open the text file
    Workbooks.OpenText Filename:=Path, Origin:=xlWindows, _
    DataType:=xlDelimited, Tab:=True
    Set wbkCartel = Workbooks("Cartel1.txt")
    Set wshCartel = wbkCartel.Worksheets(1)
    ' The lookup range
    Set rngCartel = wshCartel.Range("A1").CurrentRegion
    ' Use VLookup to retrieve name
    RetrieveNames = Application.VLookup(Matricola, rngCartel, 2, False)

    ExitHandler:
    On Error Resume Next
    ' Close the text file
    wbkCartel.Close SaveChanges:=False
    ' Set object variables to Nothing
    Set rngCartel = Nothing
    Set wshCartel = Nothing
    Set wbkCartel = Nothing
    Exit Function

    ErrHandler:
    ' Report error
    MsgBox Err.Description, vbExclamation
    ' Go to exit handler section.
    Resume ExitHandler
    End Function

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    ... but my friend of Milan send me this code not is must simple to arrange that for function?:
    Option Explicit
    Sub test()
    Dim intCounter As Integer
    Dim intNumLines As Integer
    Dim rng As Range
    Dim rngall As Range
    Dim strLine As String
    Dim strMatr() As String
    Dim strNominatavo() As String
    Dim wks1 As Worksheet

    On Error GoTo Test_Err

    Set wks1 = ThisWorkbook.Worksheets("Foglio1")
    Set rngall = wks1.Range("A2:A" & wks1.Range("A2").End(xlDown).Row)
    Open ThisWorkbook.Path & "Cartel1.txt" For Input As #1

    intCounter = 0
    Do While Not EOF(1)
    Input #1, strLine
    ReDim Preserve strMatr(intCounter)
    strMatr(intCounter) = Left(strLine, 7)
    ReDim Preserve strNominatavo(intCounter)
    strNominatavo(intCounter) = Right(strLine, Len(strLine) - 8)
    intCounter = intCounter + 1
    Loop

    Close #1

    intCounter = 0
    intNumLines = UBound(strMatr)
    For Each rng In rngall
    For intCounter = 0 To intNumLines - 1
    If StrComp(rng.Value, strMatr(intCounter), vbTextCompare) = 0 Then
    rng.Offset(0, 1).Value = strMatr(intCounter)
    rng.Offset(0, 2).Value = strNominatavo(intCounter)
    Exit For
    End If
    Next intCounter
    Next rng

    Test_Exit:
    Close #1
    Exit Sub

    Test_Err:
    MsgBox Err.Number & ", " & Err.Description
    Resume Test_Exit

    End Sub

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

    Re: how to speed up conn with mdb... (2000 sr-1)

    Why don't you ask your friend in Milano?

Posting Permissions

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