Creating an Indexed Field in a Make Table query

[Michael D. McGill]

Is there a way to have a make table query index a field in the table it
creates?

I run a process that copies a table out of a legacy system every 15 minutes
using a make table query through an odbc connection. I want the resulting
table that is created to have certain fields be indexed. How do I do this?

Thanks in Advance,
Mike

 

[Douglas J. Steele]

You can't do it using just a query. You'll need to use VBA code to create
the index. I'd use DAO, and the CreateIndex method, although you can also do
it using ADOX.

 

[MGFoster]

Is there a way to have a make table query index a field in the table it
creates?

I run a process that copies a table out of a legacy system every 15 minutes
using a make table query through an odbc connection. I want the resulting
table that is created to have certain fields be indexed. How do I do this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The make table query can't produce any indexes when it makes the
table. You'll have to use the DDL command CREATE INDEX to put indexes
on the table after the query runs. E.g.:

CREATE INDEX IndexName ON TableName (ColumnName)

See the Access SQL reference help article "CREATE INDEX Statement" for
more info.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFsOZYechKqOuFEgEQJEqgCgvEBQJdx0mXKGn1iqQzHJgFOiQvAAoMPb
kv/svtKg+72/GMyaf7IfnK1a
=7wYS
-----END PGP SIGNATURE-----

 

[Michael D. McGill]

Can I just put this SQL command into a RunSQL action in a macro. So the
macro would run the make table query then run the RunSQL action?

I tried this and I am getting a Syntax Error on my Create Index command.
Here is the syntax I am using:

CREATE INDEX number ON table1_test (number)

Any Ideas?

 

[MGFoster]

Can I just put this SQL command into a RunSQL action in a macro. So the
macro would run the make table query then run the RunSQL action?

I tried this and I am getting a Syntax Error on my Create Index command.
Here is the syntax I am using:

CREATE INDEX number ON table1_test (number)

Any Ideas?

< snip >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It should be running as you have it set up. Of course, the table must
exist before you run this macro.

You can also put the CREATE INDEX command into a query & run from there.
It can also be run from DAO:

CurrentDB.Execute "CREATE INDEX idx_number ON table1_test (number)"

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFsf2YechKqOuFEgEQJBmACgggaty7MsPd/GmJoklnh8kHhrRq0AoLp2
zEFjf1fJ70rkXsGdVoVT6naf
=KJf5
-----END PGP SIGNATURE-----