Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel sub-par performance | Excel Discussion (Misc queries) | |||
Excel Performance Problems | Excel Programming | |||
Optimizing performance of functions that access a database | Excel Programming | |||
Performance in excel 97 is poor | Excel Discussion (Misc queries) | |||
Excel Add-In performance problem | Excel Programming |