DELETE column in TABLE
in [Queries]
|
From: GoodLook on 17 Mar 2010 09:53 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 --------------------------- Your Partner for Quality "PieterLinden via AccessMonster.com" wrote: > GoodLook wrote: > >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. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 > > . >
From: GoodLook on 17 Mar 2010 10:11 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. -- --------------------------- Your Partner for Quality "Jerry Whittle" wrote: > 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. > -- > Jerry Whittle, Microsoft Access MVP > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > "GoodLook" wrote: > > > 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
From: Jerry Whittle on 17 Mar 2010 11:32
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "GoodLook" wrote: > 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. > > -- > --------------------------- > Your Partner for Quality > > > "Jerry Whittle" wrote: > > > 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. > > -- > > Jerry Whittle, Microsoft Access MVP > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > > > > "GoodLook" wrote: > > > > > 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 |