regds.,
rangaWhich data provider are you using?
Terrisql
ALTER TABLE PROJECT.RSURETTA ADD FOREIGN KEY(VOCE )
REFERENCES PROJECT.RSANVOCE(VOCE ) ON DELETE SET NULL ON UPDATE NO ACTION
But, when I try to run the script the system says:
Incorrect syntax near the keyword 'SET'.
Can I assign a null value to an other table with a reference?
Thank you
Fede"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:cjbp73$h69$1@.atlantis.cu.mi.it...
> I'm a beginner in using SQLServer and I 'm trying to bring a db Schema
> written for DB2 into SQLServer.
> My problem is this: using a tool to translate the script for creating the
> DB, I obtain the following code:
> ALTER TABLE PROJECT.RSURETTA ADD FOREIGN KEY(VOCE )
> REFERENCES PROJECT.RSANVOCE(VOCE ) ON DELETE SET NULL ON UPDATE NO
> ACTION
> But, when I try to run the script the system says:
> Incorrect syntax near the keyword 'SET'.
> Can I assign a null value to an other table with a reference?
> Thank you
> Fede
No - SET NULL is not implemented in MSSQL 2000 (I think it will be in MSSQL
2005); the only options are NO ACTION or CASCADE. If you need this
functionality, then triggers would probably be the best way to go, or
perhaps a stored procedure which performs the DELETE and also sets the
values to NULL in the referencing table.
Simon|||"Simon Hayes" <sql@.hayes.ch> ha scritto nel messaggio
news:41596f8d_2@.news.bluewin.ch...
> No - SET NULL is not implemented in MSSQL 2000 (I think it will be in
MSSQL
> 2005); the only options are NO ACTION or CASCADE. If you need this
> functionality, then triggers would probably be the best way to go, or
> perhaps a stored procedure which performs the DELETE and also sets the
> values to NULL in the referencing table.
Thank you!
Fede
I've got a System DSN set-up to SQL Server and am using links within
Access 2000 to get to the SQL Server tables.
My forms worked fine until I made a few minor changes to the database
schema on SQL Server (e.g. added a foreign key, or added a column).
After that, all the links break - I click on a table link and get an
error msg like "invalid object name."
Deleting the links after a schema change and re-adding the links seemed
to fix the problem. The forms I'd already created seemed to work fine
after re-creating the links.
But then I got more advanced with my forms. I have it set up so that
for certain entry fields, the combobox gets populated with values from
a table (the description appears in the drop-down and the corresponding
primary key value gets populated in the table). I created a number of
forms using this technique, entered data, and everything worked fine.
Made a small schema change and it broke everything -- not the actual
table links, but the functionality for the drop-downs. My values no
longer appeared, and this was true for forms that accessed tables whose
schemas did not change.
This is driving me nuts. Is there any way to keep my forms from
breaking each time I make a small schema change?
Thanks.
- DanaHI,
Have a similar setup here and found that it's just easier to have
comboboxes populated by a local table. It's al depending if the values
you are looking for changes all the time.
Grtz
Daniels|||Hate to say it, but it would be a good idea to sort out the design of
your data before jumping in to developing the application. It would
avoid issues such as this in most cases. OK, so there will be occasions
where you will need to make changes to the structure, but it is a
feature of linked tables in Access and nothing to do with SQL that is
causing you the problems. It should be easy enough to refresh the
links, and if your application is coded properly, you shouldn't have
too many issues picking up the changes.
I would recommend seeking further advice from :
http://groups.google.com/groups?hl=...bases.ms-access|||<dananrg@.yahoo.com> wrote in message
news:1106079114.508343.35830@.f14g2000cwb.googlegro ups.com...
> I've created a small company database where the tables reside in a SQL
> Server database. I'm using Access 2000 forms for a front end.
> I've got a System DSN set-up to SQL Server and am using links within
> Access 2000 to get to the SQL Server tables.
> My forms worked fine until I made a few minor changes to the database
> schema on SQL Server (e.g. added a foreign key, or added a column).
> After that, all the links break - I click on a table link and get an
> error msg like "invalid object name."
> Deleting the links after a schema change and re-adding the links seemed
> to fix the problem. The forms I'd already created seemed to work fine
> after re-creating the links.
Access stores a definition of the tables when you link them.
You need to refresh this if you change the sql server database since
there'll be a mis-match otherwise.
If you search using google on the access database you can find code which'd
do this.
> But then I got more advanced with my forms. I have it set up so that
> for certain entry fields, the combobox gets populated with values from
> a table (the description appears in the drop-down and the corresponding
> primary key value gets populated in the table). I created a number of
> forms using this technique, entered data, and everything worked fine.
> Made a small schema change and it broke everything -- not the actual
> table links, but the functionality for the drop-downs. My values no
> longer appeared, and this was true for forms that accessed tables whose
> schemas did not change.
> This is driving me nuts. Is there any way to keep my forms from
> breaking each time I make a small schema change?
> Thanks.
> - Dana
Write the forms after you have designed your database.
It's like building a house.
First off you design the whole thing.
Put your plans together.
Then you do the foundations...
Then the walls.
Then the roof.
You don't start building anything before you have the plans.
In this simile, your database is the foundations.
Change them and anything you already built will fall down.
--
Regards,
Andy O'Neill|||Dana,
If designing the database completely and not making any changes to it is not
an option for, you try one of these.
1. Do all your work in Access while building the App in access when you are
finished use the database splitter and upsizing wizard to move to SQL when
finished.
2. Try using a Access project instead of a access database, projects sit
directly on top of a SQL database, so some of your linked table blues may
disappear ( as well as the need for DSN's)
HTH
Regards
Reg Besseling
<dananrg@.yahoo.com> wrote in message
news:1106079114.508343.35830@.f14g2000cwb.googlegro ups.com...
> I've created a small company database where the tables reside in a SQL
> Server database. I'm using Access 2000 forms for a front end.
> I've got a System DSN set-up to SQL Server and am using links within
> Access 2000 to get to the SQL Server tables.
> My forms worked fine until I made a few minor changes to the database
> schema on SQL Server (e.g. added a foreign key, or added a column).
> After that, all the links break - I click on a table link and get an
> error msg like "invalid object name."
> Deleting the links after a schema change and re-adding the links seemed
> to fix the problem. The forms I'd already created seemed to work fine
> after re-creating the links.
> But then I got more advanced with my forms. I have it set up so that
> for certain entry fields, the combobox gets populated with values from
> a table (the description appears in the drop-down and the corresponding
> primary key value gets populated in the table). I created a number of
> forms using this technique, entered data, and everything worked fine.
> Made a small schema change and it broke everything -- not the actual
> table links, but the functionality for the drop-downs. My values no
> longer appeared, and this was true for forms that accessed tables whose
> schemas did not change.
> This is driving me nuts. Is there any way to keep my forms from
> breaking each time I make a small schema change?
> Thanks.
> - Dana|||Thanks everyone for your replies.
- Dana
My upgrade of SQL Server 2000 to SQL Server 2005 is being blocked as follows:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
FTCATALOGPATH
DEPRECATEDSP
FTCATALOGNAMERESTRICTION
FTNONPERSISTEDCOMPCOL
Many thanks
JMX
Can you supply the upgrade command that you are running? And which versions/editions are you going from and to?
Thanks,
Sam Lester (MSFT)
My upgrade of SQL Server 2000 to SQL Server 2005 is being blocked as follows:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
FTCATALOGPATH
DEPRECATEDSP
FTCATALOGNAMERESTRICTION
FTNONPERSISTEDCOMPCOL
Many thanks
JMX
Can you supply the upgrade command that you are running? And which versions/editions are you going from and to?
Thanks,
Sam Lester (MSFT)
