copy a column from table to table in access VB

[Hokiecow]

I'm trying to import specific columns from an excel file
(Requirements.xls) into an access table (tblRequirements). Using VBA,
I'm able to import the entire excel file into table
(tblImportRequirements). Instead of going row by row of
(tblImportRequirements) and copying the fields needs to
(tblRequirements) it would be faster to just copy the column and over
write the other table's column. I'm not sure how to copy a few
columns from (tblImportRequirements) to (tblRequirements) using VBA.
Any suggestions? Is this even possible?

[kingston via AccessMonster.com]

How about linking the Excel spreadsheet and then using an update query on the
specific field? This assumes that there is a way to relate the Access table
and the Excel spreadsheet (via a key field or a combination of fields).

Ho******@gmail.com wrote:

>I'm trying to import specific columns from an excel file
(Requirements.xls) into an access table (tblRequirements). Using VBA,
I'm able to import the entire excel file into table
(tblImportRequirements). Instead of going row by row of
(tblImportRequirements) and copying the fields needs to
(tblRequirements) it would be faster to just copy the column and over
write the other table's column. I'm not sure how to copy a few
columns from (tblImportRequirements) to (tblRequirements) using VBA.
Any suggestions? Is this even possible?

--
Message posted via http://www.accessmonster.com

[Hokiecow]

On Feb 15, 9:39 am, "kingston via AccessMonster.com" <u27511@uwe>
wrote:

How about linking the Excel spreadsheet and then using an update query on the
specific field? This assumes that there is a way to relate the Access table
and the Excel spreadsheet (via a key field or a combination of fields).

That would be a good idea but I use a different excel files each time
I import. What I'm trying to do is link from DOORs to access. Since
DOORs is a proprietary database I use excel as a go between. So I'm
importing a few columns from DOORS via excel and maitianing additional
information in other collumns in the access table.

[kingston via AccessMonster.com]

It's OK to use different Excel files so long as the copy that Access is
linked to has the same name and location every time. In other words, copy
the Excel files to another intermediate file that Access uses. It's not
clear whether you need to access all of the Excel files together. I'm
assuming that you get a refresh every so often and you want the data in that
refresh to replace existing data in Access.
Ho******@gmail.com wrote:

>On Feb 15, 9:39 am, "kingston via AccessMonster.com" <u27511@uwe>
wrote:

>How about linking the Excel spreadsheet and then using an update query on the
specific field? This assumes that there is a way to relate the Access table
and the Excel spreadsheet (via a key field or a combination of fields).

That would be a good idea but I use a different excel files each time
I import. What I'm trying to do is link from DOORs to access. Since
DOORs is a proprietary database I use excel as a go between. So I'm
importing a few columns from DOORS via excel and maitianing additional
information in other collumns in the access table.

--
Message posted via http://www.accessmonster.com

[Hokiecow]

On Feb 15, 2:23 pm, "kingston via AccessMonster.com" <u27511@uwe>
wrote:

It's OK to use different Excel files so long as the copy that Access is
linked to has the same name and location every time. In other words, copy
the Excel files to another intermediate file that Access uses. It's not
clear whether you need to access all of the Excel files together. I'm
assuming that you get a refresh every so often and you want the data in that
refresh to replace existing data in Access.

I could make it so the exported excel files are exported to the same
location and have the same name. I would only be periodically syncing
one excel file at a time. What happens if new rows are added to the
excel files, will they automatically be added to the access table?

[kingston via AccessMonster.com]

You would need to use an Append query to add new rows to your table. If
possible, set up a unique key to prevent duplicate records or put date-based
criteria or something similar in the Append query. So in short, you'd run an
Update query to change existing data (selected columns) and an Append query
to add new records from a linked Excel spreadsheet. This can be done in an
autoexec macro so users don't have to intervene unless there is a problem;
then they'd give you a call.
Ho******@gmail.com wrote:

>On Feb 15, 2:23 pm, "kingston via AccessMonster.com" <u27511@uwe>
wrote:

>It's OK to use different Excel files so long as the copy that Access is
linked to has the same name and location every time. In other words, copy
the Excel files to another intermediate file that Access uses. It's not
clear whether you need to access all of the Excel files together. I'm
assuming that you get a refresh every so often and you want the data in that
refresh to replace existing data in Access.

I could make it so the exported excel files are exported to the same
location and have the same name. I would only be periodically syncing
one excel file at a time. What happens if new rows are added to the
excel files, will they automatically be added to the access table?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1