473,396 Members | 1,789 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 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 5153
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: jcwhui | last post by:
Hello all, Yes, it is another Oracle 8.0 question. We are just too small to force our customer to migrate to newer Oracle. My problem is I need to copy the value of the long column in table A...
19
by: davidgordon | last post by:
Hi, I need some pointers/help on how to do the following if it possible: In my access db, I have the following: Tables: Products, Sub-Assembly, Product-Pack Table, Products
2
by: C G | last post by:
Dear All, I'm trying to insert an xml file into my database. I have a table with a single text column. My intention is just to have the xml file take up one row in the table. I've tried the...
2
by: Robert Fitzpatrick | last post by:
Does COPY require values for each column even though the database field is not set to NOT NULL? I did a COPY TO first to line up the data and then COPY FROM a tab delimited file. Here are the first...
4
by: Kevin Murphy | last post by:
This is a tip for the record in case it helps somebody else in the future. I have an import script that relies on a stored procedure that runs as a trigger on inserts into a temporary table. ...
4
by: andrewmac | last post by:
I hope someone can help - I am new to Access so please be patient. I have a list of about 100 equity tickers in a column in Excel formatted as Text. I am trying to copy and paste into a field...
10
by: pbd22 | last post by:
Hi. Like the title says - how do i do this? I was given the following example: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A' The above statement threw the following error:
3
by: Manuel | last post by:
Hi to all, I'm trying to copy a Datacolumn from a table to another, but with this code: destTable.Columns.Add(srcTable.Column); I got this error: Column 'colname' already belongs to another...
2
by: dashingdude | last post by:
I run an Access-based application that uses main1.mdb (updated weekly). However going forward I would only receive main2.mdb file with different structure. My question is how I can copy data from...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.