Access Access Table Array

[Muhammad Naf'an]

Can some one help me?
i have create database for equipment projection cost, but i have difficult to running query faster. so i created table def to prevent slowly query with array. and the main problem is my table def not solve my problem. because i have 60.000 rows and 92 fields.

please see the script below :

Sub Membuat_Table_Summary_Forecast()
On Error Resume Next
'set priority to above normal
Const ABOVE_NORMAL = 32768
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'MSACCESS.exe'")
For Each objProcess In colProcesses
objProcess.SetPriority (ABOVE_NORMAL)
Next
Dim db As Database
Dim dbr1 As Recordset
Dim dbr2 As Recordset
Dim dbr3 As Recordset
Dim tbldef As TableDef
Dim mysql As String
Dim R As Long
Dim C As Long
Dim scan As Long
Dim scan2 As Long
Dim kolom As String
Dim scankolom1 As Long
Dim scankolom2 As Long
Dim totalkost As Currency
Dim arr As Variant
Dim arr2 As Variant
Dim arr3 As Variant
finddatabase 'Memanggil Database Back End
Set db = OpenDatabase(databasepath)
Set dbr1 = db.OpenRecordset("Select * from forecast")
Set dbr2 = db.OpenRecordset("Select * from dbo_Asset_Master")
dbr1.MoveLast
dbr2.MoveLast
dbr1.MoveFirst
dbr2.MoveFirst
arr = dbr1.GetRows(dbr1.RecordCount)
arr2 = dbr2.GetRows(dbr2.RecordCount)
DoCmd.OpenForm "f_progress"
Form_f_progress.Caption = UBound(arr2, 2)
mysql = UCase("Select forecast.[AssetID], forecast.[Group], forecast.[class],forecast.[Model], forecast.[Category], forecast.[Physical_Location], forecast.[project_Alocation], forecast.[owning], forecast.[status], forecast.[idle_start], forecast.[idle_end], forecast.[disposal_hours], forecast.[disposal_date]" & kolom & " from forecast")
db.TableDefs.delete "Summary_Forecast" 'Delete Table Sebelumnya
Set tbldef = db.CreateTableDef("Summary_Forecast") 'Membuat table baru
tbldef.Fields.Append tbldef.CreateField("AssetID", dbText)
tbldef.Fields.Append tbldef.CreateField("Group", dbText)
tbldef.Fields.Append tbldef.CreateField("Class", dbText)
tbldef.Fields.Append tbldef.CreateField("Model", dbText)
tbldef.Fields.Append tbldef.CreateField("Category", dbText)
tbldef.Fields.Append tbldef.CreateField("Physical_Location", dbText)
tbldef.Fields.Append tbldef.CreateField("Project_Alocation", dbText)
tbldef.Fields.Append tbldef.CreateField("Owning", dbText)
tbldef.Fields.Append tbldef.CreateField("Status", dbText)
tbldef.Fields.Append tbldef.CreateField("Idle_Start", dbDate)
tbldef.Fields.Append tbldef.CreateField("Idle_End", dbDate)
tbldef.Fields.Append tbldef.CreateField("Disposal_Hours", dbDouble)
tbldef.Fields.Append tbldef.CreateField("Disposal_Date", dbDate)

For scan = 46 To dbr1.Fields.Count - 1 'Scanning Array data
kolom = dbr1.Fields(scan).Name
tbldef.Fields.Append tbldef.CreateField(kolom, dbCurrency)
tbldef.Fields(dbr1.Fields(scan).Name).DefaultValue = 0
Next scan
db.TableDefs.Append tbldef
Set dbr3 = db.OpenRecordset("Select * from Summary_Forecast")
scan = 0
For scan2 = 1 To UBound(arr2, 2)
With Form_f_progress
.Caption = "Import data " & scan2 & " of " & UBound(arr2, 2)
.l_bar.Width = (scan2 / UBound(arr2, 2)) * .L_Process.Width
End With
dbr3.AddNew
With dbr3
![AssetID] = arr2(1, scan2)
![Group] = arr2(4, scan2)
![Class] = arr2(5, scan2)
![Model] = arr2(2, scan2)
![Category] = arr2(6, scan2)
![Physical_Location] = arr2(7, scan2)
![project_alocation] = arr2(8, scan2)
![owning] = arr2(9, scan2)
![Status] = arr2(10, scan2)
![Idle_Start] = arr2(20, scan2)
![Idle_End] = arr2(21, scan2)
![Disposal_Hours] = arr2(58, scan2)
![Disposal_Date] = arr2(59, scan2)
.Update
.Bookmark = .LastModified
End With
Next scan2
DoCmd.Close acForm, "f_progress"
dbr3.MoveLast
dbr3.MoveFirst
Dim kolomforecast As Long
kolomforecast = 45
arr3 = dbr3.GetRows(dbr3.RecordCount)
For scankolom1 = 13 To UBound(arr3, 1)
kolomforecast = kolomforecast + 1
For scan = 1 To UBound(arr3, 2)
For scan2 = 1 To UBound(arr, 2)
DoCmd.OpenForm "f_progress", acNormal
With Form_f_progress
.Caption = arr(0, scan2) & "-" & arr(14, scan2) & arr3(0, scan)
End With
If arr3(0, scan) = arr(0, scan2) Then
totalkost = totalkost + arr(kolomforecast, scan2)
arr3(scankolom1, scan) = totalkost
End If
Next scan2
Next scan
Next scankolom1
dbr1.Close
db.Close
End Sub

Thanks for your advise...

Salam,

Muhammad Naf'an

 

[mextex101]

I don't have a reply for you but I am looking for an ACCESS DB template for investments. Anyone know of any?