Excel and Access Performance

D

dbarelli

I have a project in excel that imports some data from an acces
database. The problem is that it takes a lot of time. Find attach a
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.Clos
 
T

Tom Ogilvy

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

dbarelli

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