By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

copy a column from table to table in access VB

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.