Programatically attaching a SQL view (not table)

[shotput87]

I can easily write code to attach tables and views to a SQL 2000 server. My
problem is I want to write code that attaches views with no user input. But
if I attach the view without specifying a key, the view is attached with no
primary key (which is no good). If I add a primary key object to my code, I
receive an "Invalid Argument." run time error. Does anyone have sample code
to attach a view that also specifies the primary key?

 

[Douglas J. Steele]

Run DDL SQL like:

CREATE INDEX __UniqueIndex ON [MyTableName] (
[Field1], [Field2])

That's two underscore characters before UniqueIndex.

 

[shotput87 via AccessMonster.com]

Thanks Doug,
This is so obvious, I do not know why I did not try it before. I just dont
understand why I can attach tables with ADOX code but not add the index.
Here is the code I had to comment out because of the runtime error. It bombs
out if you use the index object or the key object.

Rem idxPK.PrimaryKey = True
Rem idxPK.Unique = True
Rem idxPK.Clustered = False
Rem idxPK.Name = "__uniqueindex"
Rem idxPK.Columns.Append "WONumber"
Rem idxPK.Columns.Append "Suffix"
Rem tbl.Indexes.Append idxPK


Thanks again.

Run DDL SQL like:

CREATE INDEX __UniqueIndex ON [MyTableName] (
[Field1], [Field2])

That's two underscore characters before UniqueIndex.

I can easily write code to attach tables and views to a SQL 2000 server. My
problem is I want to write code that attaches views with no user input.

[quoted text clipped - 6 lines]

code
to attach a view that also specifies the primary key?

 

[Douglas J. Steele]

Why you need to do it for views but not tables is because Access only picks
up the indexes for tables, not views.

I'm not sure I really understand your reply, though. Are you saying that the
DDL I gave you worked, but the ADOX code you show didn't? Take a look at the
values of the PrimaryKey, Unique and Clustered properties of the index once
you've created it. I somehow suspect that it won't show up as a PrimaryKey.
(Sorry, I don't have access to SQL Server at the moment, so I can't check
myself)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,
This is so obvious, I do not know why I did not try it before. I just dont
understand why I can attach tables with ADOX code but not add the index.
Here is the code I had to comment out because of the runtime error. It
bombs
out if you use the index object or the key object.

Rem idxPK.PrimaryKey = True
Rem idxPK.Unique = True
Rem idxPK.Clustered = False
Rem idxPK.Name = "__uniqueindex"
Rem idxPK.Columns.Append "WONumber"
Rem idxPK.Columns.Append "Suffix"
Rem tbl.Indexes.Append idxPK


Thanks again.

Run DDL SQL like:

CREATE INDEX __UniqueIndex ON [MyTableName] (
[Field1], [Field2])

That's two underscore characters before UniqueIndex.

I can easily write code to attach tables and views to a SQL 2000 server.
My
problem is I want to write code that attaches views with no user input.

[quoted text clipped - 6 lines]

code
to attach a view that also specifies the primary key?

 

[shotput87 via AccessMonster.com]

yes the DDL works great. but the ADOX code in any combinations do not.
regardess of whether you use the unique or clustered properties. I am going
to change my code to use the DDL when I attach my views. I have a table that
list all the tables and views that need to be attached for my app. I have a
boolean field that indicates true if it is an SQL view and false if its a
table. I also have another column that lists the primary key(s) separated by
commas. So concatinating a string with the DDL code will be a snap.
Thanks again.

Why you need to do it for views but not tables is because Access only picks
up the indexes for tables, not views.

I'm not sure I really understand your reply, though. Are you saying that the
DDL I gave you worked, but the ADOX code you show didn't? Take a look at the
values of the PrimaryKey, Unique and Clustered properties of the index once
you've created it. I somehow suspect that it won't show up as a PrimaryKey.
(Sorry, I don't have access to SQL Server at the moment, so I can't check
myself)

Thanks Doug,
This is so obvious, I do not know why I did not try it before. I just dont

[quoted text clipped - 26 lines]