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.
- 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
No comments:
Post a Comment