query fieldnames to new table

[matrixindicator]

Hallo,

I have a large query (2.000.000 records) with a lot of expressions. The result should be fixed in a table. MakeTable query is not working, too large, too less memory.

I like to create first a emmpty table with the query field names and then fill up the table with an append query. If I create a table with fixed names the update query should not find the corresponding fields. So I need to make it dynamically.

How with VBA can I get the names of the fields of the query and create a table of it.

 

[Remou]

You could run two queries, the first a make table query with zero records (WHERE 1=0) and the second an append query.

 

[matrixindicator]

Hey,

I followed your advice. It works but little trouble remains.
If you make the table with this query the field length is set to the default 255 for each text field and this is too much, this table is filled with 2.000.000 record.
Can you predefine the field length (options reset did not influence the field length set to 255).

 

[Remou]

You can run a Create Table Query (DDL) rather than a Make Table Query:

CREATE TABLE tblT (Field1 Int, Field2 Text(10))

You can also alter field lengths with DDL

ALTER TABLE tblT ALTER COLUMN Field2 Text(5)

And you can do any number of things in VBA. However, it seems a little odd that the text fields are all set to 255. Where are you getting the data?

 

[JerryKlmns]

And what 's the problem matrixindicator, with text fields been 255 chars long? In MS-Access, space occupied in disk is the actualy used! It is not padded with spaces like SQL Server.