473,888 Members | 1,424 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
5 2142
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
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
<da*****@yahoo. com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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
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******** *************@f 14g2000cwb.goog legroups.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
Thanks everyone for your replies.

- Dana

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
1950
by: NurAzije | last post by:
I have written a code and can't figure what is wrong, the code will read a content of a file and cut all the emails from it,then echo them, but I have a problem with '"' charecter I think.. Take a look: **************************** <?php $url="http://www.bihstudenti.com/kontakt.php"; $lines = file_get_contents($url); $done=explode("@",$lines); for($i=0;$i<count($done)+1;$i++)
10
5818
by: DataBard007 | last post by:
Hello Access Gurus: I use Win98SE and Access97. I just built a simple Access97 application which holds all contact information for my personal contacts, such as first name, last name, address, city, state, etc. When the user wants to search for a particular record, he does two things: 1. On the form is a text box on which he enters the text he is searching for.
3
3791
by: Dave | last post by:
I have an Access 2K application that is distributed to about a dozen users (all with identical NT environments and identical Access versions, object libraries and service packs). I am using the VBA Replace function in a couple of queries to reformat some data. The application works flawlessly on 8 of the workstations, but on the other 4 throws a "Unrecognized function Replace" error when the queries in question are encountered. I have...
5
2739
by: IkBenHet | last post by:
Hello, I use this script to upload image files to a folder on a IIS6 server: ******************* START UPLOAD.ASPX FILE ********************** <%@ Page Language="VB" Debug="true" %>
7
4723
by: garyusenet | last post by:
This is the first time i've worked with openfile dialog. I'm getting a couple of errors with my very basic code. Can someone point out the errors in what i've done please. ========================================== using System; using System.Collections.Generic; using System.ComponentModel; using System.Data;
11
5002
by: Rioshin an'Harthen | last post by:
In designing a new site for the local bridge club, I've stumbled upon a problem with my css code. Interesting fact is that I copied the code from another website an aquaintance did a few years back, and on that page it displays correctly. The front page stuff, for some strange reason I can't fathom, doesn't display in the div-element I have for the contents of the page in Firefox and Opera; other pages show up inside the correct div....
1
2647
by: ansc1 | last post by:
Hello, I'm new to using php coding. I need help with the following: 1. There is a submit button on the form and is saves information to my database. After clicking on "Save Measurement" it redirects me to another page in my site. What I would like to do is change what page directs it to. Currently the submit button redirects me to page /measure/men_measure. I would like to be able to change this. Please see below my page below:...
3
1259
by: tshad | last post by:
I get the following error: PageInit.cs(43,71): error CS0246: The type or namespace name 'User' could not be found (are you missing a using directive or an assembly reference?) The error is from the last line in this snippet of code ***************************************** using System; using System.Web;
1
1801
by: curiousEngine | last post by:
how to make provision of a dynamic array say of size 5 with each slot holding a structure of type passenger? /* * Labsheet Queues Question 1 Implement a program that shows a queue of people lining at a bus-stop. The first one in the queue is the first one to get on the bus. Your program should display the following data : Id
7
5095
by: vjayis | last post by:
hi i hav a front page from which an ajax page is called and an text field is written in the front page using this ajax page. I need to validate the user access for this page: 1.the ajax page should be executed only when it is called via ajax(i,e) when the ajax page URL is typed in the address bar it should not show its contents. 2.or the parent page(i,e) the front page url should be taken without the knowledge of the users in the...
0
11181
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10886
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5819
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3252
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.