+ Reply to Thread
Results 1 to 4 of 4

Excel and Access Performance

  1. #1
    Registered User
    Join Date
    02-07-2006
    Posts
    26

    Excel and Access Performance

    I have a project in excel that imports some data from an access database. The problem is that it takes a lot of time. Find attach an example of code. There are 5 modules like this.
    The question is, someone could help me to make it run faster ?
    Thanks in advance !

    --------------------------------------------------------
    Dim DBS As Database
    Dim RECSET As Recordset
    Dim varcli, varitem, vaux, vcriterio, SQLString As String

    varcli = Range("B55").Value
    varitem = Range("B56").Value
    varitem = Left(varitem, InStr(varitem, "'") - 1) & "''" & Mid(varitem, InStr(varitem, "'"), 2) & Mid(varitem, InStr(varitem, "'") + 2, Len(varitem) - InStr(varitem, "'") - 1)
    vaux = varcli & "_" & varitem

    vcriterio = "[CLITEM] = '" & vaux & "'"
    SQLString = "SELECT * FROM [SM - OUTPUT BOM] WHERE " & vcriterio
    Set DBS = OpenDatabase(ActiveWorkbook.Path & "\Stock Manager.mdb")
    Set RECSET = DBS.OpenRecordset(SQLString, dbOpenDynaset)
    If Not RECSET.NoMatch Then
    Range("B58").Value = RECSET("OD")
    Range("B59").Value = RECSET("PPF")
    Range("B60").Value = RECSET("GRADE")
    Range("B61").Value = RECSET("END")
    Range("B62").Value = RECSET("XDESCR")
    Range("B63").Value = RECSET("CONSVAR")
    Range("B64").Value = RECSET("SUPPLYVAR")
    Range("B65").Value = RECSET("STD/NON")
    Range("B67").Value = RECSET("SAFETY STOCK")
    Range("B68").Value = RECSET("SAFETY STOCK TONS")
    Range("C70").Value = RECSET("RCT") / 30
    Range("B71").Value = RECSET("SHIPMENTS/YEAR")
    Range("B72").Value = RECSET("STOCK TYPE")
    Range("C143").Value = RECSET("SS TONS 1")
    Range("C144").Value = RECSET("SS TONS 2")
    Range("C145").Value = RECSET("SS TONS 3")
    Range("C146").Value = RECSET("SS TONS 4")
    Range("C147").Value = RECSET("SS TONS 5")
    Range("C148").Value = RECSET("SS TONS 6")
    End If
    RECSET.Close
    DBS.Close

  2. #2
    Tom Ogilvy
    Guest

    Re: Excel and Access Performance

    why not link to the tables using Import external data (querytable) or Pivot
    Table, both found under the data menu.

    --
    Regards,
    Tom Ogilvy


    "dbarelli" <dbarelli.23wg5m_1141062901.6648@excelforum-nospam.com> wrote in
    message news:dbarelli.23wg5m_1141062901.6648@excelforum-nospam.com...
    >
    > I have a project in excel that imports some data from an access
    > database. The problem is that it takes a lot of time. Find attach an
    > example of code. There are 5 modules like this.
    > The question is, someone could help me to make it run faster ?
    > Thanks in advance !
    >
    > --------------------------------------------------------
    > Dim DBS As Database
    > Dim RECSET As Recordset
    > Dim varcli, varitem, vaux, vcriterio, SQLString As String
    >
    > varcli = Range("B55").Value
    > varitem = Range("B56").Value
    > varitem = Left(varitem, InStr(varitem, "'") - 1) & "''" & Mid(varitem,
    > InStr(varitem, "'"), 2) & Mid(varitem, InStr(varitem, "'") + 2,
    > Len(varitem) - InStr(varitem, "'") - 1)
    > vaux = varcli & "_" & varitem
    >
    > vcriterio = "[CLITEM] = '" & vaux & "'"
    > SQLString = "SELECT * FROM [SM - OUTPUT BOM] WHERE " & vcriterio
    > Set DBS = OpenDatabase(ActiveWorkbook.Path & "\Stock Manager.mdb")
    > Set RECSET = DBS.OpenRecordset(SQLString, dbOpenDynaset)
    > If Not RECSET.NoMatch Then
    > Range("B58").Value = RECSET("OD")
    > Range("B59").Value = RECSET("PPF")
    > Range("B60").Value = RECSET("GRADE")
    > Range("B61").Value = RECSET("END")
    > Range("B62").Value = RECSET("XDESCR")
    > Range("B63").Value = RECSET("CONSVAR")
    > Range("B64").Value = RECSET("SUPPLYVAR")
    > Range("B65").Value = RECSET("STD/NON")
    > Range("B67").Value = RECSET("SAFETY STOCK")
    > Range("B68").Value = RECSET("SAFETY STOCK TONS")
    > Range("C70").Value = RECSET("RCT") / 30
    > Range("B71").Value = RECSET("SHIPMENTS/YEAR")
    > Range("B72").Value = RECSET("STOCK TYPE")
    > Range("C143").Value = RECSET("SS TONS 1")
    > Range("C144").Value = RECSET("SS TONS 2")
    > Range("C145").Value = RECSET("SS TONS 3")
    > Range("C146").Value = RECSET("SS TONS 4")
    > Range("C147").Value = RECSET("SS TONS 5")
    > Range("C148").Value = RECSET("SS TONS 6")
    > End If
    > RECSET.Close
    > DBS.Close
    >
    >
    > --
    > dbarelli
    > ------------------------------------------------------------------------
    > dbarelli's Profile:

    http://www.excelforum.com/member.php...o&userid=31275
    > View this thread: http://www.excelforum.com/showthread...hreadid=516938
    >




  3. #3
    Registered User
    Join Date
    02-07-2006
    Posts
    26
    Because the database has 26 Tables and 66 Queries, and some queries are too much for Excel SQL Editor...
    Another idea ?
    Thank you anyway.

  4. #4
    keepITcool
    Guest

    Re: Excel and Access Performance

    had a look at CopyFromRecordSet method?

    --
    keepITcool
    | www.XLsupport.com | keepITcool chello nl | amsterdam


    dbarelli wrote in
    <news:<dbarelli.23wi0b_1141065304.2411@excelforum-nospam.com>

    >
    > Because the database has 26 Tables and 66 Queries, and some queries
    > are too much for Excel SQL Editor...
    > Another idea ?
    > Thank you anyway.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1