UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Best Way To Store, Search And Pass Temp Data Without Using A Table, Access 2010    
 
   
Jackal
post Oct 18 2018, 12:44 PM
Post#1



Posts: 8
Joined: 18-October 18



Hi,

I have been using Access 2010, and I wanted to know if there is a better way of passing around semi-dynamic data and searching across modules without using a permanent access table.

Simple scenario:
1. I retrieve multiple rows of text data from an API, e.g. ItemCode, Description and Price.
2. I store the temp data (no tables)
3. I retrieve using a GetPrice or GetDescription function on another module passing in the temp data as a parameter and ItemCode

Question:
What method of storage do you use for this kind of simple scenario? Arrays, Containers or Recordset?

Thanks for any heads up and discussions!


Go to the top of the page
 
cheekybuddha
post Oct 18 2018, 01:26 PM
Post#2


UtterAccess VIP
Posts: 11,524
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Consider a User-Defined Type, or if it's more complex perhaps create a class.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Oct 18 2018, 02:56 PM
Post#3


UtterAccess VIP
Posts: 11,061
Joined: 10-February 04
From: South Charleston, WV


Do you copy and paste?

--------------------
Robert Crouser
Go to the top of the page
 
moke123
post Oct 18 2018, 04:58 PM
Post#4



Posts: 1,365
Joined: 26-December 12
From: Berkshire Mtns.


QUOTE
without using a permanent access table

Another option is to use a temp database. I often create a temp database when the main database is opened and then create and delete temp tables in the temp database as needed.
Go to the top of the page
 
Jackal
post Oct 18 2018, 05:07 PM
Post#5



Posts: 8
Joined: 18-October 18



QUOTE
Another option is to use a temp database. I often create a temp database when the main database is opened and then create and delete temp tables in the temp database as needed.


I did tried that. But I wanted the code to be a little more VBA universal so that it can be used in Excel as well.

Because the data is pretty straight forward, I thought there might be a better way to store and search. Currently, I store the data in a string with delimiters. So retrieving is really fast using Instr function and it has a very low memory footprint. However, it gets cumbersome due to lack of structure.
This post has been edited by Jackal: Oct 18 2018, 05:08 PM
Go to the top of the page
 
cheekybuddha
post Oct 18 2018, 07:58 PM
Post#6


UtterAccess VIP
Posts: 11,524
Joined: 6-December 03
From: Telegraph Hill


I think a small class (or two) can help you:
CODE
Option Compare Database
Option Explicit

' Save as a class module: clsItem

Private m_strItemCode As String
Private m_strDescription As String
Private m_curPrice As Currency

Friend Property Get ItemCode() As String
  ItemCode = m_strItemCode
End Property

Friend Property Let ItemCode(ByVal strItemCode As String)
  m_strItemCode = strItemCode
End Property

Friend Property Get Description() As String
  Description = m_strDescription
End Property

Friend Property Let Description(ByVal strDescription As String)
  m_strDescription = strDescription
End Property

Friend Property Get Price() As Currency
  Price = m_curPrice
End Property

Friend Property Let Price(ByVal curPrice As Currency)
  m_curPrice = curPrice
End Property

CODE
Option Compare Database
Option Explicit

' Save as a class module: clsItems

Private m_colItems As Collection

Private Sub Class_Initialize()
  Set m_colItems = New Collection
End Sub

Friend Function AddItem(strItemCode As String, strDescription As String, curPrice As Currency) As Boolean
  
  Dim itm As New clsItem
  
  With itm
    .ItemCode = strItemCode
    .Description = strDescription
    .Price = curPrice
  End With
  Me.Items.Add itm, strItemCode
  AddItem = (Me.Items.Count > 0)
  
End Function

Friend Function AddList(strItemList As String) As Boolean

  Dim ItemLines() As String, ItemLine() As String, _
      itm As clsItem, i As Integer, blRet As Boolean
  
  If Len(strItemList) Then
    ItemLines = Split(strItemList, vbNewLine)
    For i = LBound(ItemLines) To UBound(ItemLines)
      ItemLine = Split(ItemLines(i), ";")
      blRet = Me.AddItem(ItemLine(0), ItemLine(1), CCur(ItemLine(2)))
    Next i
  End If
  AddList = blRet
      
End Function

Friend Function DeleteItem(strItemCode As String) As Boolean

  Me.Items.Remove strItemCode
  DeleteItem = (Err = 0)
  
End Function

Friend Function DeleteAllItems() As Boolean

  Dim i As Integer
  
  With Me.Items
    For i = 1 To .Count
      .Remove 1
    Next i
    DeleteAllItems = (.Count = 0)
  End With
  
End Function

Friend Property Get Item(ItemCodeOrIndex As Variant) As clsItem
  Set Item = Me.Items(ItemCodeOrIndex)
End Property

Friend Property Get Items() As Collection
  Set Items = m_colItems
End Property

Friend Property Set Items(colItems As Collection)
  Set m_colItems = colItems
End Property

Friend Property Get Count() As Integer
  Count = Me.Items.Count
End Property

Private Sub Class_Terminate()
  Set m_colItems = Nothing
End Sub


Example usage (in a standard module):
CODE
Option Compare Database
Option Explicit

Function ItemTest() As Boolean

  Dim MyItems As clsItems, i As Integer
  
  Const ITEM_LIST As String = _
    "ABC;New Item;100" & vbNewLine & _
    "DEF;Another Item;200" & vbNewLine & _
    "XYZ;Something Else;300.50"
    
  Set MyItems = New clsItems
  With MyItems
    .AddItem "GHI", "Manually added item", 25.34
    .AddItem "JKL", "Another manual item", 50
    .AddItem "MNO", "Third manual item", 75
  End With
  
  Debug.Print "There are " & MyItems.Count & " items"
  
  Debug.Print "The price of ItemCode 'GHI' is £" & MyItems.Item("GHI").Price
  Debug.Print "The description of ItemCode 'JKL' is: " & MyItems.Item("JKL").Description
  Debug.Print "The ItemCode of the third item is: " & MyItems.Item(3).ItemCode
  
  MyItems.DeleteItem "JKL"
  Debug.Print "There are now " & MyItems.Count & " items"
  Debug.Print "They are: "
  With MyItems
    For i = 1 To .Count
      Debug.Print .Item(i).ItemCode, .Item(i).Description, "£" & .Item(i).Price
    Next i
  End With
  
  MyItems.DeleteAllItems
  Debug.Print "There are now " & MyItems.Count & " items"
  
  Debug.Print ITEM_LIST
  MyItems.AddList ITEM_LIST
  Debug.Print "There are now " & MyItems.Count & " items"
  Debug.Print "They are: "
  With MyItems
    For i = 1 To .Count
      Debug.Print .Item(i).ItemCode, .Item(i).Description, "£" & .Item(i).Price
    Next i
  End With

  Set MyItems = Nothing
  ItemTest = (MyItems Is Nothing)
  
End Function


Enter in the Immediate Window (Ctrl+G):
CODE
?ItemTest


The class clsItems has a function to consume the whole result from your API call but you will have to adjust the parsing to fit your needs.

I have just used a constant as an example in the test function.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
JonSmith
post Oct 19 2018, 02:42 AM
Post#7


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



I'd just use a CSV in the Temp folder. Its super easy to write to and read from.

JS
Go to the top of the page
 
Jackal
post Oct 19 2018, 10:17 AM
Post#8



Posts: 8
Joined: 18-October 18



Hi,

Thanks for all your input.

I decided to use an array of array to store the values. (so that I can redim easily) I tried running with 10,000 records and it is pretty fast and is able to handle without much memory and CPU overhead.

Thanks all again!

Go to the top of the page
 
JonSmith
post Oct 19 2018, 10:21 AM
Post#9


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



An array will be very limiting no?

You cannot search it for example, what if you want to cross reference the values.
It will be just as quick to write a CSV file and once thats done you can read it super fast and use it in SQL.
Writing a Schema.ini file it will be a piece of cake too.

JS
Go to the top of the page
 
Jackal
post Oct 19 2018, 10:21 AM
Post#10



Posts: 8
Joined: 18-October 18



Hi CheekyBuddha,

Just saw your code. That's really amazing! I will try that and compare to my Array of Arrays method. Regardless, I think your Class method will be very useful for my other projects!

Regards,

Jackal
Go to the top of the page
 
Jackal
post Oct 19 2018, 10:24 AM
Post#11



Posts: 8
Joined: 18-October 18



QUOTE
An array will be very limiting no?

You cannot search it for example, what if you want to cross reference the values.
It will be just as quick to write a CSV file and once thats done you can read it super fast and use it in SQL.
Writing a Schema.ini file it will be a piece of cake too.

JS


That's what I originally thought too. I do need to loop through the array to find the index. Turns out it is pretty efficient. I was very surprised.
Go to the top of the page
 
Jackal
post Oct 19 2018, 12:24 PM
Post#12



Posts: 8
Joined: 18-October 18



Hi CheekyBuddha,

Just an update.

I tested your method and the performance is even better than my array method for searching the index! Plus it has typing for each items. It turns out to be a perfect solution for me! Thank you so so much!! You're amazing!

Regards,

Jackal

Go to the top of the page
 
cheekybuddha
post Oct 19 2018, 04:42 PM
Post#13


UtterAccess VIP
Posts: 11,524
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Glad you found it useful.

Hopefully it gives you the idea of how to use a class for this.

It would be pretty easy to extend it to add a method to perform the API call from within it as well, or a sorting feature, or querying feature, and to add extra properties to the clsItem class.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Jackal
post Oct 19 2018, 05:04 PM
Post#14



Posts: 8
Joined: 18-October 18



Hi,

Absolutely! I have already modified it slightly so that a reference to a container key that is not found will not throw an error.

Thanks so much! You are really an MVP!

Go to the top of the page
 
cheekybuddha
post Oct 20 2018, 05:00 AM
Post#15


UtterAccess VIP
Posts: 11,524
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 12:48 PM