Excel and Access Performance

  • Thread starter Thread starter dbarelli
  • Start date Start date
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
 
why not link to the tables using Import external data (querytable) or Pivot
Table, both found under the data menu.
 
Because the database has 26 Tables and 66 Queries, and some queries ar
too much for Excel SQL Editor...
Another idea ?
Thank you anyway
 
Back
Top