0

I am trying to change my code from storing data to excel spreadsheets to send to access database. This is the code

If qty.Value <> "" Then
        PatientDetails(1, 1) = LastRow - 1
        zh.Cells(LastRow, 1).Resize(, 22) = PatientDetails
        zh.Cells(LastRow, 23) = servees.Value
        zh.Cells(LastRow, 25) = qty.Value
        zh.Cells(LastRow, 27) = cost.Value
        zh.Cells(LastRow, 29) = ThisWorkbook.Sheets("Support").Range("AM1").Value
        zh.Cells(LastRow, 30) = [Text(Now(), "DD-MM-YY HH:MM:SS")]
        
        LastRow = LastRow + 1
    End If
'Next

im only posting the code that relates to the help i need, zh is the sheet,LastRow is supposed to find the next empty row, this section of code runs in a loop, NOW "PatientDetails" is an array with data used to populate column1 to column22.

In changing this code to be able to send to access, since i used zh.Cells(LastRow, 1).Resize(, 22) = PatientDetails to populate the first 22 columns of my spreadsheet. If i want to send "PatientDetails" to access, how do i populate access fields/columns with "PatientDetails"

This is the array

Private Function PatientDetails() As Variant
Dim ar(1 To 1, 1 To 22)

With PrivGNI
    ar(1, 1) = 0
    ar(1, 2) = "GNI HMO (PRIVATE)"
    ar(1, 3) = .Txt_EnrolleeNoGNI.Value
    ar(1, 4) = "Nil"
    ar(1, 5) = Private_PreEntryForm1.cmb_ReferFromPef2.Value
    ar(1, 6) = .Txt_EnrolleeNameGNI.Value
    ar(1, 7) = .Txt_AuthorizationCode.Value
    ar(1, 8) = Private_PreEntryForm1.Txt_AuthorisingOfficerPEF.Value
    If Private_PreEntryForm1.Opt_OutpatientPef = True Then
    ar(1, 9) = "OUTPATIENT"
    Else
    ar(1, 9) = "Nil"
    End If
    If Private_PreEntryForm1.Opt_InpatientPef2 = True Then
    ar(1, 9) = "INPATIENT"
    Else
    ar(1, 9) = "Nil"
    End If
    ar(1, 10) = Private_PreEntryForm1.Cmb_PatientPlan.Value
    ar(1, 11) = "PRIVATE HMO"
    ar(1, 12) = .Txt_HmoCodeGNI.Value
    ar(1, 13) = .txt_DateOfserv2.Value
    ar(1, 14) = .Txt_DateOfAdmGNI.Value
    ar(1, 15) = .Txt_DateOfDisGNI.Value
    ar(1, 16) = Private_PreEntryForm1.cmd_Genderpef2.Value
    ar(1, 17) = .Txt_AgeGNI.Value
    ar(1, 18) = .Txt_SexGNI
    ar(1, 19) = .txt_drsNameGNI
    ar(1, 20) = "Nil"
    ar(1, 21) = .Txt_DiagnosisGNI.Value
    ar(1, 22) = .txt_PhoneGNI.Value

End With

PatientDetails = ar
End Function

if i may add, does it make any sense to say "Recordset.Fields(1,2,3,4,5,6 to 22).Value = PatientDetails"

Its only this line of code i need help on. The ADO property or method that can refer to multiple columns/fields alongside the "fields" object to Send the array to access

Israel
  • 21
  • 5
  • No, cannot reference recordset fields that way. Why use Excel at all? Excel VBA can open a recordset of Access table and add new record. I really don't see need for passing an array to Access. – June7 Jan 31 '22 at 09:49
  • Does this answer your question? [Using Excel VBA to export data to MS Access table](https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table) – June7 Jan 31 '22 at 09:50
  • Not quite, This Means i'll be writing to each field in the recordset singularly! – Israel Jan 31 '22 at 10:31
  • 2
    You are already writing into the array singularly, so just write directly to the recordset instead. – CDP1802 Jan 31 '22 at 11:16

0 Answers0