How to Add Fields (Columns) in a QueryDef Using Access VBA

Question

I'm trying to add a new field in a querydef with the Fields.Append method. Firstly, I tryed the follownig code:

Dim myField as Field    
myQueryDef.Fields.Append myField

Then, I tryed as follows:

myQueryDef.Fields.Append "my_field_name"

None of then worked. Maybe, something is missing. maybe not. If any of you know how to do it, please, help me. I'd appreciate your help.

I also need to add this new field in a especific position within the querydef, the second position.

thanks in advance.

Are you sure you are not confusing tabledef and querydef? A querydef uses an sql statement, a tabledef allows you to add fields / columns but the position is irrelevant. — Fionnuala
– Fionnuala, Commented Nov 23, 2014 at 3:03

Gord Thompson · Accepted Answer · 2014-11-23 09:45:29Z

1

The only way you can add a field to a QueryDef object is by changing the SQL statement in the .SQL property to output an additional column. Yes, the QueryDef object has a .Fields property, but it is read-only (ref: here).

answered Nov 23, 2014 at 9:45

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thor My Over a year ago

really? I thought it was possible. what a pity! well, what about inserting the new column in the second position within the querydef/tabledef using the SQL statement? thanks a lot, Gord.

user783388 · Accepted Answer · 2014-11-23 13:49:57Z

0

The easiest way to do this (see Gords Answer) is:

dim SQLstring as string
SQLString=myQueryDef.SQL
myQueryDef.SQL=Replace(SQLString,"FROM",", myField FROM")

answered Nov 23, 2014 at 13:49

user783388

2 Comments

Thor My Over a year ago

ok. I understood, Johanness. so, if I want to insert myField right after the first field with the SQL statement, I should replace the source SQL like as follows: myQueryDef.SQL=Replace(SQLString,"firstField","firstField, myField") I thought there was a more automated way. thanks a lot for your help.

user783388 Over a year ago

QueryDefs are just stored procedures (=very simple objects). Unlike Tables where you have to be able to change one field without affecting the other, you actually replace the one procedure (=SQL String) with another (=new SQL String).

Collectives™ on Stack Overflow

How to Add Fields (Columns) in a QueryDef Using Access VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related