Hello and a Question re: a step within a macro for adding a simple field

[Deb S]

Hello All,
I am designing a macro with several steps for users totally unfamiliar with Access. One step I need and am blanking on - when they import their dataset into the new database, how can I automate the addition of a field?

 

[Deleted member 147267]

If a macro can do that, I'd be surprised. A macro could call code that could do that, but it begs the question "why??" I suppose this would be a regular thing, based on importing data, so it really sounds like there is something wrong with your design if you find that to be necessary. It suggests a lack of normalization of the tables.

EDIT - I just noticed that you posted this in the Introductions forum. A moderator will probably move it, just so you know.

 

[Isaac]

I'd be surprised too, but don't use macros so can't say 100%. Adding one thought. I've mostly given up trying to persuade people to switch from Macros to VBA, because I understand most people want an answer to their immediate problem vs. a lecture. And I understand that BUT -- This really sounds like a good task to at least consider it.

Macros are limited, less flexible, less powerful, harder to document, troubleshoot & maintain. You would be doing yourself and your projects a big service by using this opportunity to switch to VBA if you're interested. It's a bit of a learning curve but well worth it. Go cart vs. race car.

A lot of developers eschew macros entirely and I've listed a few reasons why. You got an earful here but it was based on good intentions.

 

[MajP]

I have never used a macro, but you can add a field to a table def using sql

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Or you can do it using the table def fields properties

TableDef.CreateField method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[isladogs]

Moved to Macros forum.
Agree with previous comments that
1. You can't do this with a macro
2. If your table is properly designed, there should be no reason for the end user to do this anyway

@MajP has outlined how this can be done if it is absolutely necessary

 

[Deb S]

I'm not a regular employee with this team/division and am working as a contractor. User will need to do the import step for her Excel data sets she downloads then run the macro that will hold all the MakeTable and Update queries, plus other manipulations of the data before exporting. No one on the team is at all familiar with databases and barely, data sets. I won't be around to help once my contract is done and want to leave them with AS MUCH AUTOMATED as possible. Was merely curious to know if it's possible to do this and greatly appreciate suggestions that were helpful!

 

[Deb S]

And yes, I realize that the user can add the necessary field in Excel - again, they are not skilled with Excel or Access, field types, etc. so......see above.

 

[Deb S]

Moved to Macros forum.
Agree with previous comments that
1. You can't do this with a macro
2. If your table is properly designed, there should be no reason for the end user to do this anyway

@MajP has outlined how this can be done if it is absolutely necessary

Thanks! And sorry that I posted in the wrong place - first time here and in a rush.

 

[Isaac]

@Deb S
Understood. If you choose a particular path forward and get stuck, post back. Best of luck with your project.

 

[Deleted member 147267]

And yes, I realize that the user can add the necessary field in Excel

I don't think that was suggested? Adding an Access table field via vba was though.
FWIW, if you want to have as much automation as possible as you say (and I presume stability is just as important as quantity) then macros will never achieve that.
Good luck with your project.

 

[theDBguy]

I'm not a regular employee with this team/division and am working as a contractor. User will need to do the import step for her Excel data sets she downloads then run the macro that will hold all the MakeTable and Update queries, plus other manipulations of the data before exporting. No one on the team is at all familiar with databases and barely, data sets. I won't be around to help once my contract is done and want to leave them with AS MUCH AUTOMATED as possible. Was merely curious to know if it's possible to do this and greatly appreciate suggestions that were helpful!

Hi. I don't do Excel, but I think Excel users refer to VBA code as "macro." So, if that's where you're coming from, then you already have your answer from @MajP. You can automate adding a field using VBA in Access. Macro and VBA have different meanings to Access users.

 

[vhung]

I have never used a macro, but you can add a field to a table def using sql

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Or you can do it using the table def fields properties

TableDef.CreateField method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

through this codes "TableDef.CreateField method (DAO)"
only create new table and set=tdf with additional fields
wish for a code to insert field on existing table...

i just finished the "Table and Field (create/add)" yesterday...

 

[isladogs]

@vhung
Both methods mentioned by @MajP definitely work and I use them both regularly.
If you are having difficulty with the table defs approach, here is another link explaining it from Allen Browne's excellent website http://allenbrowne.com/func-DAO.html#ModifyTableDAO

 

[vhung]

@vhung
Both methods mentioned by @MajP definitely work and I use them both regularly.
If you are having difficulty with the table defs approach, here is another link explaining it from Allen Browne's excellent website http://allenbrowne.com/func-DAO.html#ModifyTableDAO

yes sir;
anyway and good news i just finished my Form on Create Table_(New) and Add Fields_(New)
i guess i got the syntax correct and error msgbox prompt...

 

[isladogs]

@vhung
Hey. I'm immortalised as both a table and its fields! I feel honoured!

 

[vhung]

@vhung
Hey. I'm immortalised as both a table and its fields! I feel honoured!

Thanks;
i never meant' someone ahead of me on the track would lean and appreciate my accomplishment...
specially at Access World Forum...