Modifying Sql server data through Access

[romiorojo via AccessMonster.com]

We are planning to move our database from Access to Sql server.But the front
end application to remain in Access.We will be creating ODBC links to the sql
server database to use its functionality. The problem is if while creating
the links in Access if I do not supply the field for indexing, Sql server
does not allow me to modify the tables through Access. I do not prefer to
supply index key for all the tables as there are some temp tables which can
have any data not corresponding to a key.What is the best solution for this?

 

[Sylvain Lafontaine]

Make sure that you have defined a primary key for each table and Access
shouldn't ask to supply the field for indexing.

Also, this newsgroup is about ADP, not ODBC linked tables.

 

[romiorojo via AccessMonster.com]

Hello,
I cannot give primary key to some of the tables as some tables are temp
tables and store any kind of dupliate information on any key combinations. So
what should be the solution?

Make sure that you have defined a primary key for each table and Access
shouldn't ask to supply the field for indexing.

Also, this newsgroup is about ADP, not ODBC linked tables.

We are planning to move our database from Access to Sql server.But the
front

[quoted text clipped - 7 lines]

have any data not corresponding to a key.What is the best solution for
this?

 

[Sylvain Lafontaine]

Simple: add a column with an identity value (or autoincrement field in SQL
parlor) as the primary key to be used with these tables.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Hello,
I cannot give primary key to some of the tables as some tables are temp
tables and store any kind of dupliate information on any key combinations.
So
what should be the solution?

Make sure that you have defined a primary key for each table and Access
shouldn't ask to supply the field for indexing.

Also, this newsgroup is about ADP, not ODBC linked tables.

We are planning to move our database from Access to Sql server.But the
front

[quoted text clipped - 7 lines]

have any data not corresponding to a key.What is the best solution for
this?

 

[romiorojo via AccessMonster.com]

Yes you are right Sylvain, I know that exists as one solution but I was
thinking that may not be the appropriate solution, may be there is an option
within sql server which could help me do it without the identity column.

Simple: add a column with an identity value (or autoincrement field in SQL
parlor) as the primary key to be used with these tables.

Hello,
I cannot give primary key to some of the tables as some tables are temp

[quoted text clipped - 12 lines]

 

[Sylvain Lafontaine]

Even when you don't create a primay key explicitely, SQL-Server will still
create one which will be hidden; so you lose nothing to create it yourself.
This is true even for temporary tables.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Yes you are right Sylvain, I know that exists as one solution but I was
thinking that may not be the appropriate solution, may be there is an
option
within sql server which could help me do it without the identity column.

Simple: add a column with an identity value (or autoincrement field in SQL
parlor) as the primary key to be used with these tables.

Hello,
I cannot give primary key to some of the tables as some tables are temp

[quoted text clipped - 12 lines]

have any data not corresponding to a key.What is the best solution for
this?

 

[romiorojo via AccessMonster.com]

Is there any maximum limit for the identity column increment because my
program will keep on adding and taking off records.

Even when you don't create a primay key explicitely, SQL-Server will still
create one which will be hidden; so you lose nothing to create it yourself.
This is true even for temporary tables.

Yes you are right Sylvain, I know that exists as one solution but I was
thinking that may not be the appropriate solution, may be there is an

[quoted text clipped - 9 lines]

 

[Sylvain Lafontaine]

If you take an integer, this will be something like 2 billions (2E9); if
this is not enough, then you can go with a Big Int.

Also, you can choose to not use an identity (or autoincrement) field for the
primary key and use something else to give these values to each record.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Is there any maximum limit for the identity column increment because my
program will keep on adding and taking off records.

Even when you don't create a primay key explicitely, SQL-Server will still
create one which will be hidden; so you lose nothing to create it
yourself.
This is true even for temporary tables.

Yes you are right Sylvain, I know that exists as one solution but I was
thinking that may not be the appropriate solution, may be there is an

[quoted text clipped - 9 lines]

have any data not corresponding to a key.What is the best solution
for
this?

 

[Guest]

Your access client will be an .adp file, i.e. an Access project. In Access
you can make controls like buttons or comboboxes. For these controls you can
write event procedures in visual basic that call stored procedures on the sql
server.

The stored procedures are a very effective way to select, update delete or
insert data. The stored procedures will often (normally) do their job without
any indexes or primary keys on tables, but indexing may speed up things
considerably.

Regards

Tore