UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Deleting A Field, Access 2007    
 
   
TinyGiant2010
post Oct 25 2019, 08:36 AM
Post#1



Posts: 214
Joined: 1-June 10



I am not sure where in UA to post this, so please forgive me if this is not the right forum.
Here goes.

I use a master database that I export a table from into a child database. I open the child database and strip out thousands of records. I use a DELETE QUERY to do this - specifically here is one that I use:

DELETE FREIS.Status, FREIS.PType, FREIS.Listed, FREIS.Nr1, FREIS.NEWS, FREIS.Street, FREIS.FreisNo
FROM FREIS
WHERE (((FREIS.PType)<>"res"));

https://www.UtterAccess.com/forum/style_ima.../rte-indent.gif
This deletes a lot of records.
My question is, in the FREIS table there is a certain field (not records, but a field in those records) that I'd like to make disappear in the child database. I know I can open the table itself in design view, scroll until I find the field, then just delete the field. I can live with that I guess.

However, is it possible to make the field disappear via a DELETE QUERY, one that I could add to the list of the 4 DELETE queries I already regularly use?

I think the answer will be "open the table and delete the field" but I thought I'd ask anyway to see if there might be a way to automate this.

Insights appreciated.

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 08:41 AM
Post#2


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


CODE
ALTER TABLE FREIS
  DROP COLUMN ColToDelete;


If the field is part of a foreign key then this won't work until the relationship is deleted first.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2019, 08:50 AM
Post#3


UA Admin
Posts: 36,165
Joined: 20-June 02
From: Newcastle, WA


Let me see if I understand everything.

A "master" accdb has a table in it. That table includes one field you DO NOT want to include when that table is exported as a table in your "child" accdb. You replace the table in the child accdb each time you do the export.

I can see two possible approaches, one being the option David offered.

The other is to create a query in the "master" accdb which omits that undesirable field and export that query, not the actual table.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
TinyGiant2010
post Oct 25 2019, 09:12 AM
Post#4



Posts: 214
Joined: 1-June 10



The table is really stand-alone, and is not linked to others.

ALTER TABLE FREIS
DROP COLUMN REALTORREM;

Since RealtorRem is the name of the field I'd like to delete out of the FREIS table. The datatype is a Memo field.

My code experience is almost nil. I build/use queries in design view, and my result posted previously was generated by looking at SQL view then copying/pasting. (Obviously)

I don't know how/where to inset the ALTER TABLE solution to make this work.

Regarding a "make table query" I could generate in master, then export to child that omits REALTORREM field; this may be a smart thing for me to do; albeit far more challenging. I use the child database to populate a website. The site only uses 3 asp files; and it looks at the child database for "answers". That said, the actual fields being sought by these asp files are maybe 25-30; whereas the database itself contains near the maximum number of fields permitted in one table. (20+ years of big hairy bloat, to be sure!) The MAKE TABLE query, then export the "made" table could strip this down dramatically. I would just need to pay attention to the table names in the child database, being sure to rename the imported-in table correctly.

Thanks for the suggestions!

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 09:14 AM
Post#5


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


Create a new query.

Go straight to SQL view (don't choose any tables)

Replace 'SELECT;' with the ALTER TABLE statement.

Click the red exclamation mark toolbar button to execute.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 09:16 AM
Post#6


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


If you want to do it in code, try something like:
CODE
  Dim strSQL As String

  strSQL = "ALTER TABLE FREIS DROP COLUMN REALTORREM;"
  CurrentDb.Execute strSQL, dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
TinyGiant2010
post Oct 25 2019, 11:19 AM
Post#7



Posts: 214
Joined: 1-June 10



The SQL modification works like a champ!

5.3mb file shrinks to 3.6mb = tremendous!

Thanks!

I've learned something new today!
This post has been edited by TinyGiant2010: Oct 25 2019, 11:23 AM

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 11:21 AM
Post#8


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


yayhandclap.gif

I'm pleased George and I could help.

yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 05:08 AM