How do you store array values in a table?

10-28-2004, 10:52 AM
We had a tool that previously exported data from a Lotus Notes database to a text file. Once the file was exported, I imported it to an access table (Survey) in order to do calculations using a VB Module. The new tool that we have is a DB2 database and instead of being able to export data to a text file, I have to grab the data directly from the tables. I have created a query to pull the data and then load the data into a multi-dimensional array. I use the array to do my calculations as before using the VB module. Is there a way to read the data from the array and store it back to the access table (Survey) that I spoke about above? The elements in the array are in the same position and format as the fields in the table. I need to be able to do reporting for auditing purposes and combine the information from the old tool and the new tool. Is this possible?

10-28-2004, 12:19 PM
Well, you would use ADO, Connect to the Access database, and issue a series of SQL INSERT commands using the ado connection.execute method.

Essentially, you would loop through the array elements, building and issuing a new insert statement for each pass through the array.

pseudo code:
For N = lbound(YourArray) to UBound(YourArray)
strSQL = "Insert into YourTableName (Field1, Field2, Field3) Values (~Val1~, ~Val2~, ~Val3~)
strSQL = Replace("~Val1~", YourArray(N, 0))
strSQL = Replace("~Val2~", YourArray(N, 1))
strSQL = Replace("~Val3~", YourArray(N, 2))
cn.Execute strSQL
Next N

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum