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

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

--
Regards,
Tom Ogilvy


"dbarelli" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

had a look at CopyFromRecordSet method?

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


dbarelli wrote in
<news:<[email protected]>

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