471,355 Members | 1,632 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

copy a column from table to table in access VB

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?

Feb 15 '07 #1
5 4962
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

Feb 15 '07 #2
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.

Feb 15 '07 #3
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

Feb 15 '07 #4
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?
Feb 16 '07 #5
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

Feb 16 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by davidgordon | last post: by
2 posts views Thread by C G | last post: by
2 posts views Thread by Robert Fitzpatrick | last post: by
4 posts views Thread by andrewmac | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.