Hello and a Question re: a step within a macro for adding a simple field (1 Viewer)

Deb S

New member
Local time
Today, 08:14
Joined
Jul 20, 2020
Messages
6
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?
 

Micron

AWF VIP
Local time
Today, 09:14
Joined
Oct 20, 2018
Messages
3,476
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

Lifelong Learner
Local time
Today, 06:14
Joined
Mar 14, 2017
Messages
8,738
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

You've got your good things, and you've got mine.
Local time
Today, 09:14
Joined
May 21, 2018
Messages
8,463
I have never used a macro, but you can add a field to a table def using sql
Or you can do it using the table def fields properties
 

isladogs

MVP / VIP
Local time
Today, 13:14
Joined
Jan 14, 2017
Messages
18,186
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

New member
Local time
Today, 08:14
Joined
Jul 20, 2020
Messages
6
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

New member
Local time
Today, 08:14
Joined
Jul 20, 2020
Messages
6
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

New member
Local time
Today, 08:14
Joined
Jul 20, 2020
Messages
6
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. :cool:
 

Isaac

Lifelong Learner
Local time
Today, 06:14
Joined
Mar 14, 2017
Messages
8,738
@Deb S
Understood. If you choose a particular path forward and get stuck, post back. Best of luck with your project.
 

Micron

AWF VIP
Local time
Today, 09:14
Joined
Oct 20, 2018
Messages
3,476
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 here to help
Staff member
Local time
Today, 06:14
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 06:14
Joined
Jul 8, 2020
Messages
235
I have never used a macro, but you can add a field to a table def using sql
Or you can do it using the table def fields properties
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...
 
Last edited:

vhung

Member
Local time
Today, 06:14
Joined
Jul 8, 2020
Messages
235

Attachments

  • Accdb Create Table Add Field_(New).png
    Accdb Create Table Add Field_(New).png
    74.2 KB · Views: 129
  • Accdb Create Table Add Field_(New)2.png
    Accdb Create Table Add Field_(New)2.png
    55.4 KB · Views: 129

vhung

Member
Local time
Today, 06:14
Joined
Jul 8, 2020
Messages
235
@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...
 

Users who are viewing this thread

Top Bottom