Issues with AddNew method arguments

Hello guys,

It is my first thread posted here due to the fact I've spent 2 days to find a solution to my issue.
Shortly, I use an ADO Data Control to extract some data from a database (server) and I want to copy these records in other database (local). For this operation I use 2 methods:

1. "GetRows", which loads my selection in an variant array : myValues(columns, rows)
2. "AddNew" , which adds these records in my database (a simple table with 6 columns)

My issues is related to the "AddNew" arguments. Even it has 2 optional arguments I think both are necessary...
I would like to add just my table values, without header (FieldList), but I don't think it is possible.
After many attempts, I tried to load 2 arrays for these 2 arguments, but each time I failed in the execution.
The error occurred is "Runtime Error 3001 – Arguments are of the Wrong Type or out of acceptable range or are in conflict with one another". Simply, I don't understand how to transfer some records from a table in other...
Is something wrong with this ADODC ? Are some ADODC commands improperly declared ? I mention that I use Access2003 and the code I've written till now is below:

Please Login or Register  to view this content.

Thank you in advance.

Hello Norie,

Even the FieldList requires a 1 dimensional array I have used a 2 dimensional one for compatibility reasons...
Taking into account the Microsoft recommendations "If Fields is an array, Values must also be an array with the same number of members; otherwise, an error occurs. The order of field names must match the order of field values in each array." I thought these two arrays should have a similar structure but it seems something goes wrong. Anyway, I have tested both situations (one dimensional + bi, bi+bi) and, unfortunately, the error is the same. On the other hand, as we know, GetRows method always returns a two-dimensional array.
I wonder if there is someone who has successfully used this control ADO for updating. Thank you for your interest.

Hello Norie,

Thanks for reply. I see your arrays, I have not tested but I can bet they work with these two method. Is useful that you have simplified the problem and we have now common base for discussion. In your exemple you have 2 fields: "Account_No", "Note". You try to add a single row only but, imagine, we have to add 100 rows...
In this case, how could you insert all rows using an array like arrValues = Array("Emal4", "Text Here") ? In this array, as we see, data are not stored by rows.

Thank you.

Hello Norie,

It is a bit frustrating that I have bi dimensional array generated with GetRows method (so I have the all new records inside) but I have no solution to add them in a database as new records...Strange. I cannot do in vba Access what I can do in vba Excel... On the other hand, I would prefer to avoid to create a link to that table. It doesn't seem a flexible or modern way to transfer a selection of fields and data. I will try to search other dedicated controls for this job or even other database environment which providing a feature like bulk insert records.
Thank you for your help.

Hello Norie,

After I have checked a net dictionary , it seems "complementary" has the same meaning like in my language (1. Forming or serving as a complement; completing 2. Supplying mutual needs or offsetting mutual lacks.). So, in our case the complementary method of GetRows would be, let's say, InsertRows. If we try an analogy with vba excel, as you know, we can get all values from a sheet using varArray = Range("A1:Z" & x).Value - where varArray is a bi dimensional array as varArray(r,c). Now, the "complementary" method will be here something like this : Range("A1:Z" & x).Value = WorksheetFunction.Transpose(varArray). This is what I wanted to point out : we do not have in Access (with ADO or without) an efficient method to insert values on rows/columns. But, to get to the point, I will detail below the code I have written for a simple update:

Please Login or Register  to view this content.

This is my modest approach. If you have a more efficient alternative, in terms of speed or simplicity (number of lines of code written), I would appreciate if you share your solution.

Hello Norie,
Unfortunately, I had to loop the array because I haven't had a convenient alternative to use addNew method as bulk insert; it seems this method is built for inserting a single value (record) at one time. If RecordCount isn't always reliable I would consider this event as bad luck or one more reason to work in other, more stable, database management environment...
No, I have not tested the linking method and I am not sure if it brings something new or better in this approach.
In this point I should mention that my table (the local table) is just a selection of rows and columns comparing with the server table (which has 40 columns). Do you consider a linked table could be better solution, more advantageous? Could you provide me a short example? I have considered this is a matter of taste. Personally, I prefer to read, copy and leave instead of a permanent link with an object, like in a boring marriage.
Thanks in advance.