Code/macro to add a field to a table

[Access nubie]

Every month I import an excel file into a db. Each time, I have to add 2 fields (1 with text type, another as number type) with specific names. How can i automate the process of adding the 2 fields? Is there a VB function or macro function to do this? thank you.

 

[pbaldy]

The fact that you add fields every month is a pretty good indicator that your design is not normalized. I would address that first. That said, you can execute a DDL query to add a field to a table. I believe you can also use a DAO TableDef.

 

[Access nubie]

i'm sorry, i am a nubie. i am not familiar with a DDL query or a DAO TableDef.

in a different database, where an autonumbered field had to be added each time (also every month) *midway* through the queries, i used the function AddCounter(Files As String, ID As String) in a module. then, i created a macro using the action RunCode with the AddCounter function name.

 

[HiTechCoach]

In a properly normalized database, you should NOT need to be adding fields, only records.

Also note: there is a limit to the number of fields in a table.


It is possible to add fields using DAO or SQL, but you must have all other users out before attempting to add the fields so that you can be sure to back up the database first and avoid any other issues.

I would urge you to rethink your method and use a properly normalized tables is that you do not have to worry ale making backups first and all the other issues you will need to handle.

 

[Access nubie]

I am the only one who uses this database. I'd like to put the macro in place for the occasional times when I am out of the office & someone else needs to run the process, which occurs once a month. Also, this data is obtained through a report from a third party application. The data is downloaded as an excel file - i have no control over the format/layout of the data. I am only adding 2 fields - there are only 12 fields in the table.

Many thanks...

 

[pbaldy]

I think you'll regret it (it depends on what those fields represent), but look at TableDef and CreateField in VBA or DAO help. There is sample code there.

 

[joshandsony]

I also have a table that I need to add fields too. Except mine does not need any info in them. It is strictly so my vendors can add their own info and send it back. I am interested to see what can be done.

 

[DCrake]

In your original post you stated that you import data into your db and need to add 2 fields to the table.

Why not have the table definition preset with the two additional fields and append the data to that table when you run the import. Doing it this way you can set the data types and sizes correctly.

David

 

[joshandsony]

When you append the table, doesn't that just add to the records that are already there? My problem is that I upload a completly new set of records twice a week. What about using an append query? Would you be able to create a new table with just column names and run an append query to get those added to the query you are looking at?