Array to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating reports in Excel. From those reports I am generating a summary
array that I would like to output to Access. How do I import that array?
Does anyone have sample code?
 
Hi Candyman,

In one breath you say you want to "output" an array from Excel to
Access, and in the next you want to "import" the array. And when you say
"array", do you mean a VBA Array variable, or an array (i.e. range) of
cells on a worksheet, or something else?

Can you be a bit clearer about what you're trying to achieve?
 
Glad to. . I currently store a high level summary of my reports in another
Excel spreadsheet for all of our reports by transferring a simple array.

(TallyCnt=Public Number of records held in the array)
Workbooks.Open TallyWB
Worksheets(TallyTab).Select
SearchRng = "a1:z10"
TallyRow = FdString("Date", SearchRng, "Rw") + 1
Worksheets(TallyTab).Rows(TallyRow).Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromRightOrBelow

'''Loop through the counts and add to the chart
Cells(TallyRow, 1).Value = Date
For iCnt = 1 To TallyCnt
With Range(SearchRng)
‘Find the right column for the Line of Business or Add a new column
Set c = .Find(myTally(1, iCnt), LookIn:=xlValues,
LookAt:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
TallyCol = c.Column
Else
'''Add new LOB if not present
TallyCol = Cells(TallyRow - 1, 1).End(xlToRight).Column
+ 1
Cells(TallyRow - 1, TallyCol).Value = myTally(1, iCnt)
End If
End With
Cells(TallyRow, TallyCol).Value = myTally(2, iCnt)
Next iCnt

The trending requirements have now expanded to demanded more information
than could be economically tracked in Excel for every line of business.

I want to build an array myTally(2,x) in Excel to track the results from my
reports. The first field would be the value NAME and the second field is the
VALUE for as many fields as necessary.
I'd like to take that array and export it to an Access Database temp table
then run a query in Access to disseminate the temp table results to a
permanent table. That query would be looking at the field NAME to determine
its position in the permanent log table.

My problem is getting myTally(2,x) into Access. Does that help clarify the
issue?
Candyman
 
This should get you started. Should run in Excel VBA if you set a
reference to the DAO 3.6 library; alternatively use late binding.

Sub DBTest342()

Dim oJet As DAO.DBEngine
Dim oDB As DAO.Database
Dim strSQL As String
Dim j As Long

'Start of SQL single record INSERT INTO statement
'Substitute correct table and field names!
Const SQL1 = "INSERT INTO MyTable (fNAME, fVALUE) VALUES ("

'Demo array
Dim MyTally(2, 3) As String
MyTally(0, 0) = "N0"
MyTally(1, 0) = "V0"
MyTally(0, 1) = "N1"
MyTally(1, 1) = "V1"
MyTally(0, 2) = "N2"
MyTally(1, 2) = "V2"

'Open database
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.Workspaces(0).OpenDatabase("C:\TEMP\Jay\db1.mdb")

'Iterate through array, building and executing an
'INSERT INTO statement each time.
'Don't use apostrophes round value for a numeric field;
'substitute quotes if the data may contain apostrophes
For j = 0 To UBound(MyTally)
strSQL = SQL1 & "'" & MyTally(0, j) & "' , '" _
& MyTally(1, j) & "');"
oDB.Execute strSQL, dbFailOnError
Next

oDB.Close

End Sub
 
Back
Top