Thank you for your suggestions. I did not have Option 1 Base in the
declarations section (thanks for pointing out - I had not checked that). I
made sure VBA was before Array. Still gives me the same error messages.
This should be a simple evolution but ????
Again, thanks.
Norma
"George Nicholson" wrote:
> Guess #1:
>
> I bet you have "Option Base 1" at the top of your module.
>
> From the VB help entry on Array function:
> "...The lower bound of an array created using the Array function is
> determined by the lower bound specified with the Option Base statement,
> unless Array is qualified with the name of the type library (for example
> VBA.Array). If qualified with the type-library name, Array is unaffected by
> Option Base...."
>
> and since the default lower boundary for an array is 0, that means that your
> Field name array (VBA.Array(..)) has lbound of 0 but your Value array (just
> Array(..)) has lbound of 1. (i.e., there's a lbound mismatch)
>
> *If* ADO puts the 2 arrays together by position, the first field (ID) won't
> have a corresponding Value even though the arrays contain the same number of
> elements.
>
> Example: I'm thinking ADO may *internally* handle the assignments like this:
> For i = lbound(varFields) to ubound(varFields)
> ' If lbound(varFields) = 0 and lbound(varValues) = 1,
> ' an out-of bound error occurs when i = 0 (the 1st pass).
> ' The error becomes a Null assignment.
> rs.Fields(varfields(i)) = varValues(i)
> Next i
>
> Guess #2
> Since the error message mentions "column ID", I would assume that
> VBA.Array("ID", "Fname", "Lname") works.
> Try prefacing Array(vaData(i, 1), vaData(i, 2), vaData(i, 3)) with VBA.
> too.
>
> Lots of guessing here. I'm very curious if that's the answer. Post back
> (either way) if you can.
>
> HTH,
>
>
>
> "NormaD" <(E-Mail Removed)> wrote in message
> news:48C73A9A-B969-4D88-B84D-(E-Mail Removed)...
> >I am having trouble with the Array portion of the attached code - as I step
> > through it works fine until it hits where I am loading the Excel
> > information
> > into the variable.
> > .Update VBA.Array("ID", "Fname", "Lname"), Array(vaData(i, 1),
> > vaData(i, 2), vaData(i, 3)) when I hover over vaData it will show me the
> > information from the cells but on the Next step I get an error Message
> > "Cannot insert the Value NULL into column ID, table
> > GPReports.dbo.My_Employees; column does not allow nulls. Insert fails. If
> > I
> > were to remove restrictions from the table it post NULL in the row.
> >
> > Here is the full code:
> > Sub Export_Data_Excel()
> >
> > Dim cnt As ADODB.connection
> > Dim rst As ADODB.recordset
> > Dim xlCalc As XlCalculation
> > Dim rnData As Range, rnCell As Range
> > Dim stDB As String, stConn As String
> > Dim vaData As Variant
> >
> > Dim i As Long
> >
> > 'Change settings in order to increase the performance.
> > With Application
> > xlCalc = .Calculation
> > .Calculation = xlCalculationManual
> > .EnableEvents = False
> > .ScreenUpdating = False
> > End With
> >
> > Set rnData = ActiveSheet.Range("J21:l23")
> >
> > 'Instantiate the ADO COM's objects.
> > Set cnt = New ADODB.connection
> > Set rst = New ADODB.recordset
> >
> > stDB = ThisWorkbook.Path & "\" & "FirstTry"
> >
> > 'Create the connectionstring
> > stConn =
> > "Provider=SQLOLEDB;Server=CorpDyndb;Trusted_Connection=Yes;Initial
> > Catalog=GPReports;UID=;"
> >
> > 'Populate the array with data from the range.
> > vaData = rnData.Value
> >
> > 'If the data is stored in rows instead of columns then the
> > 'solution would be the following:
> > Set rnData = ActiveSheet.Range("j21:l23")
> > 'vaData = Application.Transpose(rnData.Value)
> >
> > cnt.Open stConn
> >
> > 'Open the recordset.
> > rst.Open "My_Employees", cnt, adOpenKeyset, adLockOptimistic,
> > adCmdTableDirect
> >
> > 'Read data, add new data and update the recordset.
> >
> > For i = 1 To UBound(vaData)
> > With rst
> > .AddNew
> > .Update VBA.Array("ID", "Fname", "Lname"), Array(vaData(i, 1),
> > vaData(i, 2), vaData(i, 3))
> > End With
> > Next i
> > MsgBox "Successfully updated the table!", vbInformation
> >
> > 'Close recordset and connection.
> > rst.Close
> > cnt.Close
> >
> > 'Release objects from memory.
> > Set rst = Nothing
> > Set cnt = Nothing
> >
> > 'Clear inputrange.
> > rnData.ClearContents
> >
> > 'Restore the settings.
> > With Application
> > .Calculation = xlCalc
> > .EnableEvents = True
> > .ScreenUpdating = True
> > End With
> > End Sub
> >
> > Any ideas will be greatly appreciated.
> >
> > Norma
> >
>
>
>
Similar Threads
