AddNew function in Access VBA

[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.

[Rich P]

Hi Amey

you can try something like this:

Dim DB As DAO.Database, RS As DAO.RecordSet, i As Integer
Set DB = CurrentDB
Set RS = DB.OpenRecordset("tbl1")
For i = 0 to Ubound(excelArray)
RS.AddNew
RS(0) = excelArray(i)
RS.Update
Next

Note: the excelArray in this example only has one column. If you use
ADO to read a range of Excel Values with say 10 columns you can do this:

Dim ... i As Integer, j As Integer
Do While Not RSexcel.EOF
RS.AddNew
For j = 0 to RSexcel.Fields.Count - 1
RS(j) = RSexcel(j)
Next
RS.Update
RSexcel.MoveNext
Loop
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Terry Kreft]

I take it that column_Names is an array of the column names? If so then:-

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

Terry Kreft
<am********@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

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.

[Bas Cost Budde]

You want to use the Fields collection of the recordset. I inserted a sample.

am********@gmail.com schreef:

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"

.Fields(0) = "acctNo"

!B = "CoName"
!C = "acctMan"
.Update
.Bookmark = LastModified
End With

--
Bas Cost Budde
Holland

[amey.gupte]

Hi Rich,
Thanks for the reply and sorry for the delay in replying. I tried the
code as suggested by you and it worked fine. However, as this method
does an Update after each row there were some performance issues, hence
we resorted to the DoCmd.TransferSpreadSheet method.

Thanks & Regards,
Amey Gupte.

Rich P wrote:

Hi Amey

you can try something like this:

Dim DB As DAO.Database, RS As DAO.RecordSet, i As Integer
Set DB = CurrentDB
Set RS = DB.OpenRecordset("tbl1")
For i = 0 to Ubound(excelArray)
RS.AddNew
RS(0) = excelArray(i)
RS.Update
Next

Note: the excelArray in this example only has one column. If you use
ADO to read a range of Excel Values with say 10 columns you can do this:

Dim ... i As Integer, j As Integer
Do While Not RSexcel.EOF
RS.AddNew
For j = 0 to RSexcel.Fields.Count - 1
RS(j) = RSexcel(j)
Next
RS.Update
RSexcel.MoveNext
Loop
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[amey.gupte]

Hi Terry,
Thanks for the reply and sorry for the delay in replying. Your
assumption is correct, column_Names is the array of column names. I
tried the code as suggested by you, but it was not working correctly,
may be I was doing some mistakes. However, as this method does an
Update after each row there were some performance issues, hence we
resorted to the DoCmd.TransferSpreadSheet method.

Thanks & Regards,
Amey Gupte.

Terry Kreft wrote:

I take it that column_Names is an array of the column names? If so then:-

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

Terry Kreft
<am********@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

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.