Add records to table in bulk (not one record at a time) via multidimensional array

[mountainclimber]

I'd like to add 600+ records at a time to a table (or at least a lot) and do this 1200 or so times. Yes, this is a very big table, its okay and the size is not an issue.



The key here is that speed is very important. I DO NOT want to AddNew and Update a recordset 700k times (one for each new record). I want to add 600 records or so 1200 or so times.

I am open to any way of doing this in VBA and/or SQL in MS Access; however, I am thinking of building an array that has 600 rows and 16 columns, then somehow add that to the table all at once. I will size the array down if it causes problems. That isn't the issue. The issue is how to add a 2d array to a table in bulk (all at once).

Here is one VERY ugly way of doing it, but it illustrates my point using a much smaller array:

Sub test()


Dim dbLocal As DAO.Database
Set dbLocal = CurrentDb()
Dim sql As String

'build my array 
Dim x(0 To 1, 0 To 1) As Integer
x(0, 0) = 1
x(0, 1) = 2
x(1, 0) = 3
x(1, 1) = 4

sql = "INSERT INTO tbl1 (fld1, fld2, fld3, fld4) " & _
      "VALUES (" & x(0, 0) & "," & x(0, 1) & "," & x(1, 0) & "," & x(1, 1) & ");"
      
dbLocal.Execute sql, dbFailOnError
dbLocal.Close
Set dbLocal = Nothing

End Sub

That will work, but there has to be a better way...

[ranman256]

normally, you execute a query to bulk add.
why do you have an array to begin with?
data is stored in tables. The query pulls from tables, not arrays.

if you have an array, it MUST be added 1 record at a time.
unless you are updating all records from a 1 record array.
then take your sql and run
docmd.runsql sSql

[June7]

As ranman says, an array won't help no matter what size - still committing one record at a time in a loop structure. And the code you show adds only one record.

Probably the only way to do what you want is with an INSERT SELECT sql action. This requires a table or query that can be used as a 'source template' of data to copy. Say I want to add Payroll records for current pay period for all employees:

CurrentDb.Execute "INSERT INTO Payroll(EmpID, PP) SELECT EmpID, " & Me.tbxPP & " AS PP FROM Employees WHERE EmpStatus='Active'"

[mountainclimber]

okay i'll move away from the array. Some other things were necessitating it, but I get around those. Thanks.

[mountainclimber]

Okay, I think I am headed in the direction you both recommended. I'm querying the data and returning subset/aggregation calculated values for a given column/field in a table:

(I'm not sure this code is useful to the question, but posting for others, clarification, and suggestions for improvement.)

Sub test2()Dim x
Set x = GetOrOpenAndGetExcel 'I do this here so I don't have to open and close excel for each calc


Dim rst As DAO.Recordset
Dim sSql As String
Dim q As String
q = VBA.Chr(34)
sSql = "SELECT " & _
            "DCalcForQueries(" & q & "NaPct" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS NaPct ," & _
            "DCalcForQueries(" & q & "Mean" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Mean ," & _
            "DCalcForQueries(" & q & "Sd" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Sd ," & _
            "DCalcForQueries(" & q & "Low" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Low ," & _
            "DCalcForQueries(" & q & "Q1" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Q1 ," & _
            "DCalcForQueries(" & q & "Median" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Median ," & _
            "DCalcForQueries(" & q & "Q3" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Q3 ," & _
            "DCalcForQueries(" & q & "High" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS High ," & _
            "DCalcForQueries(" & q & "IQR" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS IQR ," & _
            "DCalcForQueries(" & q & "Kurt" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Kurt ," & _
            "DCalcForQueries(" & q & "Skew" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Skew ," & _
            "DCalcForQueries(" & q & "Obs" & q & ", " & q & "tbl_DatedModel_2015_0702_0" & q & ", " & q & "Rk-IU Mkt Cap" & q & ", " & q & "[Rk-IU Mkt Cap] IS NOT NULL AND [GICS Sector] = 'Consumer Discretionary'" & q & ") AS Obs " & _
            "FROM tbl_DatedModel_2015_0702_0;"
Debug.Print sSql
Set rst = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
rst.MoveFirst


Debug.Print rst.RecordCount
Debug.Print rst.Fields("NaPct")
Debug.Print rst.Fields("Mean")
Debug.Print rst.Fields("Sd")
Debug.Print rst.Fields("Low")
Debug.Print rst.Fields("Q1")
Debug.Print rst.Fields("Median")
Debug.Print rst.Fields("Q3")
Debug.Print rst.Fields("High")
Debug.Print rst.Fields("IQR")
Debug.Print rst.Fields("Kurt")
Debug.Print rst.Fields("Skew")
Debug.Print rst.Fields("Obs")


End Sub

Public Function DCalcForQueries(sCalc As String, Optional sTbl As String = "", Optional sMainFld As String = "", Optional sWhereClause As String = "", Optional k As Double) As Variant

Dim dblData() As Double
Dim oxl As Object
On Error Resume Next
Set oxl = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    MsgBox "Excel object must be opened by the calling sub of DCalcForQueries so it isn't opened over and over, which is very slow"
    GoTo cleanup
End If


Dim x As Integer


Dim aV() As Variant
Dim tmp
Dim lObsCnt As Long
Dim lNaCnt As Long
Dim i As Long
Dim vTmp As Variant
Dim lTtl As Long
Dim bDoCalc As Boolean


aV = a2dvGetSubsetFromQuery(sTbl, sMainFld, sWhereClause, "Numeric")
If aV(0, 0) = "Not Numeric" Then
    MsgBox "Data returned by query was not numeric. Press OK to Stop and debug."
    Stop
End If


If sCalc = "Percentile" Or sCalc = "Q1" Or sCalc = "Q2" Or sCalc = "Q3" Or sCalc = "Q4" Then
    DCalcForQueries = oxl.WorksheetFunction.Percentile_Exc(aV, k)
ElseIf sCalc = "Median" Then
    DCalcForQueries = oxl.WorksheetFunction.Median(aV)
ElseIf sCalc = "Kurt" Or sCalc = "Kurt" Then
    DCalcForQueries = oxl.WorksheetFunction.Kurt(aV)
ElseIf sCalc = "Minimum" Or sCalc = "Low" Then
    DCalcForQueries = oxl.WorksheetFunction.Min(aV)
ElseIf sCalc = "Maximum" Or sCalc = "High" Then
    DCalcForQueries = oxl.WorksheetFunction.Max(aV)
ElseIf sCalc = "IQR" Then
    DCalcForQueries = oxl.WorksheetFunction.Quartile_Exc(aV, 3) - oxl.WorksheetFunction.Quartile_Exc(aV, 1)
ElseIf sCalc = "Obs" Then
    lObsCnt = 0
    For Each tmp In aV
        If Not IsNull(tmp) Then
            lObsCnt = lObsCnt + 1
        End If
    Next
    DCalcForQueries = lObsCnt
ElseIf sCalc = "%NA" Or sCalc = "PctNa" Or sCalc = "NaPct" Or sCalc = "%Null" Or sCalc = "PctNull" Then
    lNaCnt = 0
    lTtl = UBound(aV, 2) + 1
    For Each tmp In aV
        If IsNull(tmp) Then
            lNaCnt = lNaCnt + 1
        End If
    Next
    DCalcForQueries = (lNaCnt / lTtl) * 100
ElseIf sCalc = "Skewness" Or sCalc = "Skew" Then
    DCalcForQueries = oxl.WorksheetFunction.Skew(aV)
ElseIf sCalc = "StDev" Or sCalc = "Sd" Then
    DCalcForQueries = oxl.WorksheetFunction.StDev_S(aV)
ElseIf sCalc = "Mean" Then
    DCalcForQueries = oxl.WorksheetFunction.Average(aV)
Else
    MsgBox "sCalc parameter not recognized: " & sCalc
End If


cleanup:

End Function

Public Function GetOrOpenAndGetExcel() As Object
'if excel is open it will return the excel object
'if excel is not open it will open excel and return the excel object
On Error GoTo 0
On Error Resume Next
Set GetOrOpenAndGetExcel = GetObject(, "Excel.Application")


If Err.Number <> 0 Then
    Set GetOrOpenAndGetExcel = CreateObject("Excel.Application")
End If

On Error GoTo 0
End Function

I plan to loop through all of the fields in table tbl_DatedModel_2015_0702_0 and get the 15 calculated values for each field. I did it for one field (Rk-IU Mkt Cap) above. I'll also be changing the subset ([GICS Sector] = 'Consumer Discretionary') that I am using to calculate. As before I'll be doing this a lot so speed is important.

Question: how do I get from here (getting one record of calcs in a recordset) to an efficient way to add them to another table? I can't do an INSERT INTO for each field and subset/aggregation that I have created a record for. That would be too slow. I'd like to accumulate a lot of records containing the 15 calcs and add a bunch of records at once.

Thanks for your help!

[June7]

INSERT SELECT as shown in example of earlier post.

Don't understand 'loop through all fields' - why would you need to loop?

Build SELECT query and use it as source for INSERT action.

[mountainclimber]

INSERT SELECT as shown in example of earlier post.

Don't understand 'loop through all fields' - why would you need to loop?

Build SELECT query and use it as source for INSERT action.

Good point on the loop. Didn't even think of that. I guess I need to modify my where statement passed to DCalcForQueries()?

[June7]

I don't know. What modification do you think is needed? What is purpose of the function?

I would not have a MsgBox in a function called by a query.

[mountainclimber]

It works much like DAvg() except it can calculate Mean, Median, Kurt., Skew., Sd, etc. Give it one aggregation (ex. [GICS Sector] = 'Consumer Discretionary') and one field to do the calculation on and it will produce one number.

Now I need to figure out how to give it one field to aggregate on [GICS Sector] and have it produce one value per [GICS Sector] per field in a table or just list out every field I want to calc on in the select statement. Problem is, I asked a similar question on this forum and I was given a "loop" answer:
https://www.accessforums.net/queries/sql-aggregate-25-percentile-value-subsets-ms-53125.html

Not sure I know how to get around a loop, but I am looking into it.

[mountainclimber]

I asked the question much better here:
http://stackoverflow.com/questions/3...ypes-and-calcs

[June7]

Got a glimmer of what you are doing. Custom aggregate statistical functions. Way over my head.

Why are you trying to save calculated data anyway?

[mountainclimber]

Got a glimmer of what you are doing. Custom aggregate statistical functions. Way over my head.

Why are you trying to save calculated data anyway?

We are building a time series of the statistics that are calculated. The raw data that I am using is daily stock data that we plug into proprietary calculations and create custom factors that we use to make stock buy and sell decisions. This is all Excel based. I built it bolt-on style over the last 3+ years for the hedge fund I work with. It is a "quant model" if you are familiar with the term. I am now taking that data into MS Access and calculating aggregate data for a time series so we can make macro economic, sector, etc. decisions and calls. In other words, if we know more of the larger picture quantitatively, we can understand underlying stock movements better and make better decisions in our stock picks. The statistics I am calculating will also serve has a data error alert system for when we get bad data, which happens occasionally.

Thanks for your help!

[June7]

Still not sure why you need to save records of the calculated results as opposed to running them when needed.

What do you mean by 'building a time series'? Don't know what 'quant model' is.

[mountainclimber]

Still not sure why you need to save records of the calculated results as opposed to running them when needed.

Humm... I guess I assumed it would not be possible or practical. Maybe you have more insight. Say someone requests 10 years of median calcs on GICS Sectors, of which there are 10. Since I have 1305 possible fields that have 2000+ ticker associated with each field and one set of 2000+ tickers per day and there is one calculation per day that needs to be made, how do you propose I store all this data?

For example if I have one table per field/factor I'd have 1305 tables with dates as records and tickers as fields. If I have one table per ticker I'd have about 2000 tables, one per ticker. Either way, it seems like too much to me.

However, If I get rid of the ticker dimension and store the aggregate data only, its much more manageable.

What do you think?

What do you mean by 'building a time series'? Don't know what 'quant model' is.

Build a table that is a time series table:
https://www.youtube.com/watch?v=aaaILjNPHSs

Quant model should be discussed here:
https://en.wikipedia.org/wiki/Mathematical_finance

[June7]

I don't understand the data structure well enough - 1305 fields involved in this calc - WOW! A table for each field/factor????

I've never had to deal with this volume of data. I expect you will quickly outgrow Access.