Data storage problem

[MaximS]

Hi guys,

Can you advise me what would be better to use Excel or Access in situation described below:

- Report to present Product age on multiple sheets (different aspects).
- More than 65k product IDs but only about 20k active every day.
- Report updated daily from external operational database which only keeps needed data for few days then is getting lost.
- Data updated in report are Date of Purchase and Last Sale thats why I need to keep/update not active products.
- No chance to use Excel 2007.
- I don't want to move everything to Access as updated data is subject to processing and creating more reports.

[Bob Phillips]

What are your thoughts MaximS, it seems to me you have given it much thought and have some views? Why not articulate those and debate them rather than load our prejudices on you?

How are the current updates happening?

Also, what is the relevance of the 2007 statement, is it the 65K?

[MaximS]

Hi xld,

When I created that report it contained about 30k but grown to 65k in 6 months of using it. The biggest problem now is keeping the data of inactive (without stock) product IDs (as most of them will be back).

There is about 20k active IDs within total 70k (and growing) and only them active are subject to further processing.

Update is manualy done once a day from external database (Oracle).
External data base is keeping date of Last Sale only from previous day otherwise is showing default date. That is why update needs to be done daily to avoid any data lose. Daily data to process is only 20k.

Company is using Office 2003 so I cannot go over 65k unless I store the data on multiple sheets or one in repetetive columns.

I've thought that creating little data base with one table in Access might solve the problem. That would keep all active and inactive in one place and then all active could be exported back to Excel.

Unfortunately I haven't done much VBA coding in Access before so probably will need your further assistance.

[Bob Phillips]

I agree, saving the data in a database, either Access or SQL Server, seems the only sensible option.

But you can avoid Access per se, and just the Access store, the mdb. Use Excel as the code engine that loads the data and retrieves the data, and use Excel as the reporting engine. I do that as I far prefer Excel's capabilities to Access'. With ADO it is relatively painless.

[MaximS]

thx xld,

I have made a simple database with one table to hold all IDs with First Intake and Last Picked details.

Below code is cross updating either Excel or Database depending what is needed.


Sub UpdateRecord()
Dim DBName, DBLocation, FilePath As String
Dim DBConnection As ADODB.Connection
Dim DBRecordset As ADODB.Recordset

Dim wb As Workbook
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Long, LRow As Long
Dim Fld As Variant, Val As Variant
Dim Query As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set DBConnection = New ADODB.Connection
DBName = "dbsname.mdb"
DBLocation = "C:\Reports\"
FilePath = DBLocation & DBName
With DBConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open FilePath
End With

Set wb = ThisWorkbook
Set w1 = wb.Worksheets("ReportBase")
Set w2 = wb.Worksheets("RawData")

LRow = w2.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LRow
Query = "SELECT * FROM IAP WHERE SKU =" _
& w2.Range("A" & i).Value

Set DBRecordset = New ADODB.Recordset
DBRecordset.CursorLocation = adUseServer
DBRecordset.Open Source:=Query, ActiveConnection:=DBConnection, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
Options:=adCmdText

With DBRecordset
If .EOF = True Then
Fld = Array("SKU", "First_Intake", "Last_Picked")
Val = Array(w2.Range("A" & i).Value, _
w2.Range("G" & i).Value, w2.Range("H" & i).Value)
.AddNew Fld, Val
Else
If .Fields("First_Intake") > _
w2.Range("G" & i).Value Then
w2.Range("G" & i).Value = _
.Fields("First_Intake")
Else
.Fields("First_Intake") = _
w2.Range("G" & i).Value
End If
If .Fields("Last_Picked") > _
w2.Range("H" & i).Value Then
w2.Range("H" & i).Value = _
.Fields("Last_Picked")
Else
.Fields("Last_Picked") = _
w2.Range("H" & i).Value
End If
.Update
End If
.Update
End With
DBRecordset.Close
Next i

Set DBRecordset = Nothing

DBConnection.Close
Set DBConnection = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub