Delete all field names from table

juniorz

Registered User.
Local time
Tomorrow, 01:33
Joined
May 9, 2013
Messages
38
Hello,

I need to delete all the field names from my table so I can import a new excel file with different field name headings,

currently I run:

DoCmd.RunSQL "DELETE * from table1"

this deletes the data in the table, but not field names.

What Vba command will allow me to do this?

Thank you,
 
Delete the table:
Code:
DoCmd.RunSQL "DROP TABLE table1"
 
Will running this affect query's in any way. obviously if the table is deleted it wont run the query but if im importing a new one straight away with the same table name. will that be fine?

thanks
 
Will running this affect query's in any way. obviously if the table is deleted it wont run the query but if im importing a new one straight away with the same table name. will that be fine?

thanks
Yes it will.
 
Removing all the field names from a table will absolutely effect queries built upon it.

SELECT [LastName] FROM YourTable;

If YourTable has no [LastName] field it will throw an error. You've asked us to help you accomplish a specific task without telling us the purpose. My guess is theres a better way to achieve that purpose than this task. If you would like to give us the big picture purpose maybe we can help with a better solution...

With that said, an ALTER query will achieve this task:

http://www.w3schools.com/sql/sql_alter.asp
 
Make a new table based on the field names of the new import and leave the exiting table alone.
 
And here's the VBA way:
Code:
    Dim dbs As DAO.Database
    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("[COLOR="Blue"]TableName[/COLOR]")
    
    For Each fld In tdf.Fields
        tdf.Fields.Delete fld.Name
    Next
    
    tdf.Fields.Refresh
    
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

But like plog said, without knowing the reasons why you're needing to do this, we can't advise on a better approach. I'm curious as to why this is being done.
 
I'd like to hear the "business rationale" for this.
Are you importing the same file each time (but some values change)?
Are the field names different (really) for each import? If so, why?

The programming should be helping you automate. I think you may have to step back and see what is really required (business wise) and then look at options. But as vbaInet and others have said --- we need more info.
 
I apologize for the vagueness, Plog this is also on the same database as my queries issue.

The data is of student test results, it is used to track the student over several years and compare progress. these are exported from the internet into an excel document. From this document I then Import it into a table on the database. The exported document contains The students first name, last name, student id number, each individual answer for the question, their score at the end of the test, the score in a percentage and a scaled score.

From this I can create a query that only selects the fields of the students name, their ID number and the score. It will also grab previous years results. e.g Year 10 score, year 9 score, year 8 score.

First Name l Last name l Year 10 Score from table test 10 l Year 9 Score from table test 9 l Year 8 Score from table test 8
James Bond 30 25 28

I want to then put that query into a report so that It can be easily read.


The problem of having each field name already typed into the table is that each test can have varied amount of questions, meaning it has different amount of field names. I also cannot directly import the file as i need to use a static import to select the starting cells.

Therefore, if I deleted the table, then the query that gets the studentname, ID number and score, will be searching for a table that contains that info. If I import a table with that same name and contains those fields then will that work?

Im more than likely making this more complicated than it should be, I appreciate your patience in this.
 
First Name l Last name l Year 10 Score from table test 10 l Year 9 Score from table test 9 l Year 8 Score from table test 8
The main problem is your table structure and I'm sure that plog has mentioned that in your other thread. So if he's already helping you out with that then you can always come back on this thread once that's sorted.

In the meantime, upload a spreadsheet containing the different types of formats the spreadsheets are received. Include both headers and some sample data.
 

Users who are viewing this thread

Back
Top Bottom