DELETE column in TABLE

[GoodLook]

Hi all,

Is it possible to create a query that DELETES column in a table in ACCESS
2007?
the reason is, because I receive a very big database from my supplier, I
need to delete a large number of column that are not needed by us.
now there are 200 fields and i only need 15.

when publishing to complete database it takes to much time and is way to big
in file Size.

regards,
Geert

 

[PieterLinden via AccessMonster.com]

Hi all,

Is it possible to create a query that DELETES column in a table in ACCESS
2007?
the reason is, because I receive a very big database from my supplier, I
need to delete a large number of column that are not needed by us.
now there are 200 fields and i only need 15.

when publishing to complete database it takes to much time and is way to big
in file Size.

regards,
Geert

Set up your import specification to not map all of the fields. That way,
only the fields you specify will be imported.

 

[PieterLinden via AccessMonster.com]

Hi all,

Is it possible to create a query that DELETES column in a table in ACCESS
2007?
the reason is, because I receive a very big database from my supplier, I
need to delete a large number of column that are not needed by us.
now there are 200 fields and i only need 15.

when publishing to complete database it takes to much time and is way to big
in file Size.

regards,
Geert

One way to solve the problem:
1. Create a blank database.
2. create a linked table pointing to the database table with all the data.
(File, Get External Data)
3. create a MakeTable query that includes just the columns you want in your
table.
4. save the query and run it.
5. add the indexing to your table.

 

[Jerry Whittle]

ALTER table TheTable drop ExtraField;

Even if you drop a bunch of fields, don't be surprised if you have trouble
if you try to add fields to the table in the future. Access tends to remember
the number of fields that have been in the table in the past. A compact and
repair may help.

 

[GoodLook]

I tested both methods and this method is not so good.
when mapping, first time is works great.
When the supplier changes the field order, or some fields or deleted then it
go wrong and it is not possible to import at all. ( I simulated with deleting
some fields in the database to import.

working with a linked table is best, because of the flexibility of the query.

Thanks you very much for the very fast and correct response.
You folks are great !

 

[GoodLook]

I tested both methods and this one is the best for me.

because of the flexibility of the query. When there are some changes from
the supplier, it is easy to correct the query and overwrite the existing
table.

this method is fast and correct.

Thanks again for the very fast and correct response
Fantastic

 

[GoodLook]

this method works great first time

ALTER TABLE RAY_160310 DROP COLUMN a_soort, a_volgnr

when supplier changes the field names or deletes fields i get message like
table contains no filed a_soort.

is it possible to check if exists, like if exist a_soort then DROP COLUMN
a_soort ?

for now I use the method with the linked table from PieterLinden, because
for this database I need to get only 15 fields out of 200.
When I should have 200 field and only need to DROP 10 field then your method
would be better because of smaller query.

thank you.
I love the Internet.

 

[Jerry Whittle]

It would be very easy to do in a database like SQL Server and Oracle where
the columns/fields are listed in a table.

There is a way or two in VBA to extract the names of fields in a table, but
I don't know it off hand. Maybe one of the coders could help here.