Excel and Access Performance
 

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

--
dbarell
-----------------------------------------------------------------------
dbarelli's Profile: http://www.excelforum.com/member.php...fo&userid=3127
View this thread: http://www.excelforum.com/showthread.php?threadid=51693

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" wrote in
message ...

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

Excel and Access Performance
 

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

--
dbarell
-----------------------------------------------------------------------
dbarelli's Profile: http://www.excelforum.com/member.php...fo&userid=3127
View this thread: http://www.excelforum.com/showthread.php?threadid=51693

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@excelforu m-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.