AddNew function in Access VBA

A

amey.gupte

Hi,
I am using the AddNew function in VBA to populate my access table from
data in excel. The following is the piece of code:

With WriteRS
.AddNew
!column_Names(0) = "acctNo"
!B = "CoName"
!C = "acctMan"
.Update
.Bookmark = LastModified
End With


The column names in the access tables are A, B and C. If I hard code
these values it works fine.However, if I try to make it dynamic as done

above (!column_Names(0) = "acctNo") by storing column names in an
array and then retriving it it does not work. Is there a way I can get
the column names dynamically in AddNew function or is there any other
function that serves the same purpose as AddNew and allows dynamic
population of column names.


Thanks & Regards,
Amey Gupte.
Research Assistant,
Supply Chains Systems Lab,
Texas A&M University.
 
K

Klatuu

Unless you have your values in an array or you are using automation and
addressing the location of the Excel data using the Cells object, there is no
point to this. The syntax is:

With WriteRS
.AddNew
For intX = 0 To .Fields.Count -1
.Fields(intX) =
Next intX
.Update
.Bookmark = LastModified
End With

I don't understand the Bookmark line. What are you trying to do?
 
B

Brendan Reynolds

I have not tested this, but you could try replacing this line ...

!column_Names(0) = "acctNo"

.... with this ...

..Fields(column_Names(0)) = "acctNo"

The first, incorrect example means "the field named 'column_Names(0)'", and
of course there is no such field. The second, hopefully correct example (but
remember I have not tested it) means "the field with the name that matches
the value stored in the first element of the array named 'column_Names'".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top