By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,358 Members | 2,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,358 IT Pros & Developers. It's quick & easy.

Problem using Access 2000 as a front-end to SQL Server 2000 tables

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
HI,
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

Jul 23 '05 #2

P: n/a
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=e...ases.ms-access

Jul 23 '05 #3

P: n/a
<da*****@yahoo.com> wrote in message
news:11*********************@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
Jul 23 '05 #4

P: n/a
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
<da*****@yahoo.com> wrote in message
news:11*********************@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


Jul 23 '05 #5

P: n/a
Thanks everyone for your replies.

- Dana

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.