Data Definition Query

[Guest]

I have been given access to several SQL ODBC table containing CRM (customer
relationship managment) data. I am wanting to use these tables to append key
information to an existing table. I have linked the ODBC tables to the DB
containing my existing data. I created an update query between a linked
table and the original table and received the following error: "Operation
must use an updateable query".

I looked at the SQL tables and there is not a primary key for any of the SQL
tables. I wrote the following data definition query for one of the tables,
and tried to then run an update query and received the same error.

CREATE UNIQUE INDEX ID ON dbo_comet_acct
(ID)

Am I headed in the right direction? Can someone please help?

 

[Chris2]

I have been given access to several SQL ODBC table containing CRM (customer
relationship managment) data. I am wanting to use these tables to append key
information to an existing table. I have linked the ODBC tables to the DB
containing my existing data. I created an update query between a linked
table and the original table and received the following error: "Operation
must use an updateable query".

I looked at the SQL tables and there is not a primary key for any of the SQL
tables. I wrote the following data definition query for one of the tables,
and tried to then run an update query and received the same error.

CREATE UNIQUE INDEX ID ON dbo_comet_acct
(ID)

Am I headed in the right direction? Can someone please help?

Shad,

ALTER TABLE <table name>
ADD CONSTRAINT <constraint name>
UNIQUE (<column name>)

<table name> := replace with your table name.
<constraint name> := replace with your name for the constraint. I
prefer the format shown further below.
<column name> := replace with the column name (or columns names in a
comma delimited list).

Format for UNIQUE <constraint name> := un_<table name>_<column name>
(but it can be any valid object name you want).


On a CREATE TABLE statement:

CREATE TABLE <table name>
(<column name> <datatype>
,<column name> <datatype>
,CONSTRAINT <constraint name>
UNIQUE (<column name>)
)

If your tables are in SQL Server, your query will need to be of the
pass-through variety.


Sincerely,

Chris O.

 

[Guest]

Chris2,

Below is the code I typed in a pass through query.

Alter Table dbo_comet_acct
Add Constrant C1
Unique (SQLID)

Format for UNIQUE C1 = un_dbo_comet_acct_SQLID

I haven't done the create table statement, but when I tested the above it
acted like it was trying to connect to the SQL Server. I canceled because I
was worried that it would add this data to the table. I want to make sure
that what I am doing will not alter or change the SQL tables in any way as I
would get in BIG trouble.

Additionally, I didn't really understand the Create Table statement. Do I
do this in the same pass through query, or some other SQL query?

Thanks for your expert advise and help.

Shad


I didn't understand the create

 

[Chris2]

Chris2,

Below is the code I typed in a pass through query.

Alter Table dbo_comet_acct
Add Constrant C1
Unique (SQLID)

Format for UNIQUE C1 = un_dbo_comet_acct_SQLID

I haven't done the create table statement, but when I tested the above it
acted like it was trying to connect to the SQL Server. I canceled because I
was worried that it would add this data to the table. I want to make sure
that what I am doing will not alter or change the SQL tables in any way as I
would get in BIG trouble.

Additionally, I didn't really understand the Create Table statement. Do I
do this in the same pass through query, or some other SQL query?

Thanks for your expert advise and help.

Shad


I didn't understand the create

Shad,

Since your tables are in SQL Server, then MS Access will need to
connect to your SQL Server in order to execute the query you wanted.

The query should not alter data. It should only add an index.

However, before ever taking newsgroup advice, you should always make
sure that you are operating on a test database.

You should change production only when you know you have a good backup
available (test a restore from it to prove it is good).

(Note: You had Constraint misspelled as Constrant. I have corrected
that below.)

This:

Alter Table dbo_comet_acct
Add Constraint C1
Unique (SQLID)

Will create a unique index on column SQLID in table dbo_comet_acct,
and that index will have an internal name of C1.

To give the index an internal name of un_dbo_comet_acct_SQLID, use:

Alter Table dbo_comet_acct
Add Constraint un_dbo_comet_acct_SQLID
Unique (SQLID)

------------------------

If you are uncertain about doing this, don't do it.

If you have SQL Server 2000/2005, you should also have Enterprise
Manager, and you can add an index directly via the GUI (I believe so,
anyway, it's been a while). Personally, I never do that, because if
you ever have to drop the table, you have no record available of
exactly what needs to be done to re-create the table.

If you want to, just create a table in a new MS Access database that
has the same name and columns names as your SQL Server 2000 table.
Then run the above queries in MS Access directly against this test
table (just open a new blank query, switch to SQL View, paste the SQL
into it, and save, and it will automatically become a DDL Query).
Obviously, there will be no connection to any SQL Server in this case.

------------------------

The CREATE TABLE statement was just an additional example I threw in
about how to add CONSTRAINTS while using a CREATE TABLE statement.


Sincerely,

Chris O.