Access Sql 1

[c5665]

Hello,


How do I modify an access query's SQL code to include an INDEX field when running a Make-Table query? Can somebody help me out there, thanks!

 

[PaulBricker]

I don't believe Jet supports creating a unique index within the structure of a Make Table query. I think you would have to use the CreateIndex Method to accomplish what you want.

Paul

 

[Craig0201]

Not in a make table. You can after using the CREATE INDEX statement. Look it up in Help.

Craig

 

[PaulBricker]

Craig, just to bring this to your attention, this is right at the top of the Create Index Statement Page in Help. It indicates that this is not available to normal Access processes.

Note For non-Microsoft Jet databases, the Microsoft Jet database engine does not support the use of CREATE INDEX (except to create a pseudo index on an ODBC linked table) or any of the data definition language (DDL) statements. Use the DAO Create methods instead. For more information see the Remarks section.

I think this has to be done with DAO or ADO methods.

Paul

 

[Craig0201]

That's the point. It supports it within Access but won't create a table in SQL Server, for example.

The question related to Make Table which will create locally, hence CREATE INDEX applies.

Paste these into two separate queries and watch it work

CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT);

CREATE UNIQUE INDEX Test ON FirstTable (FirstName, LastName) WITH DISALLOW NULL;

Craig

 

[PaulBricker]

You are right and I'm wrong. I absolutely misread that first line. Have a star.

Paul