Programatically attaching a SQL view (not table)

  • Thread starter Thread starter shotput87
  • Start date Start date
S

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?
 
Run DDL SQL like:

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

That's two underscore characters before UniqueIndex.
 
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.
Click to expand...
[quoted text clipped - 6 lines]
code
to attach a view that also specifies the primary key?
Click to expand...
Click to expand...
 
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)


shotput87 via AccessMonster.com said:
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.
Click to expand...
[quoted text clipped - 6 lines]
code
to attach a view that also specifies the primary key?
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
[quoted text clipped - 26 lines]
Click to expand...
 
Back
Top