473,692 Members | 2,131 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Upsize from Access 97 to SQL 2000 Problem

Hello,

Has anyone experienced the following problem following an Upsize from
Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see
what the problem might be.

Before Upsize (Access 97 front end and Access 97 tables): A MainForm
loads up correctly and then I enter a ReferenceNumber value in a
MainForm field control. I then click on the first field of a SubForm,
the ReferenceNumber then shows at the top of the SubForm and further
subform details can be entered. Finally, I can click on a SubForm
button to open another Form which allows me to enter more details. (It
also includes what I have already entered on the previous SubForm.)
This works...

After Upsize (Access 97 front end and SQL 2000 tables): ...As above ...
Finally, I click on a SubForm button to open another SubForm which
allows me to enter more details and at this point it returns an error
saying SubForm record does not exist.

Points that may be relevant. There are no triggers on the SQL tables.
There are three tables involved MainForm data table, SubForm data table
and a third table acting as a link between the first two tables. The
SQL table relationships appear to match those in Access 97. In the
Before Upsize all these tables get created, in the After Upsize only
the first two tables are created not the linking table. The SubForm has
the correct Child and Master links (as in the Before Upsize database).
As far as I can see the only difference between the Before and After is
that I am accessing SQL tables. There is no code creating any of these
records only the implicit SubForm Child/Master field links.
Interestingly, if I add triggers to the tables this ensures that all
records are created, however the error still appears and will not go
away until I close down the MainForm then reopen it.

Thanks for your time in reading this problem and any advice or help you
can give.

Kind regards

Terry

Nov 13 '05 #1
1 2071
There can be some problems with using an MDB with Jet, ODBC and server
databases because the server equivalent of AutoNumber is not assigned until
the record is written to the SQL Server table, and is not passed back to
ODBC and then to Jet.

This may well be what you are experiencing. I would assume that the key
field you describe is for the "parent" record and the actual key of the
"child" table may be a separate AutoNumber equivalent (perhaps in
combination with the key of the parent record).

To avoid this problem with both Informix and SQL Server databases, we had a
Stored Procedure that would obtain a "next id", create the record in the
server table, and return the id... so that we were not actually "adding a
new record from the Access front end" but editing a record newly-added by
the Stored Procedure. That was a couple of versions ago on SQL Server, but
as far as I know, that part still works the same with ODBC and Jet.

Larry Linson
Microsoft Access MVP

"Terry" <Te***@supradat a.net> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...
Hello,

Has anyone experienced the following problem following an Upsize from
Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see
what the problem might be.

Before Upsize (Access 97 front end and Access 97 tables): A MainForm
loads up correctly and then I enter a ReferenceNumber value in a
MainForm field control. I then click on the first field of a SubForm,
the ReferenceNumber then shows at the top of the SubForm and further
subform details can be entered. Finally, I can click on a SubForm
button to open another Form which allows me to enter more details. (It
also includes what I have already entered on the previous SubForm.)
This works...

After Upsize (Access 97 front end and SQL 2000 tables): ...As above ...
Finally, I click on a SubForm button to open another SubForm which
allows me to enter more details and at this point it returns an error
saying SubForm record does not exist.

Points that may be relevant. There are no triggers on the SQL tables.
There are three tables involved MainForm data table, SubForm data table
and a third table acting as a link between the first two tables. The
SQL table relationships appear to match those in Access 97. In the
Before Upsize all these tables get created, in the After Upsize only
the first two tables are created not the linking table. The SubForm has
the correct Child and Master links (as in the Before Upsize database).
As far as I can see the only difference between the Before and After is
that I am accessing SQL tables. There is no code creating any of these
records only the implicit SubForm Child/Master field links.
Interestingly, if I add triggers to the tables this ensures that all
records are created, however the error still appears and will not go
away until I close down the MainForm then reopen it.

Thanks for your time in reading this problem and any advice or help you
can give.

Kind regards

Terry

Nov 13 '05 #2

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

Similar topics

1
1519
by: Randy | last post by:
When I create tables in SQL I can specify dbo as the owner using the syntax below. Can ownship be changed when using the upsize wizard in Access 2000 so that everything being upsized will be owned by DBO? CREATE TABLE dbo.mytable (c1 int not null) Thank You, Randy K wawork@hotmail.com
3
5577
by: Karen | last post by:
I am attempting to upsize a 60MB database in Access 2000 to sequel server using the upsizing wizard. I go thru all the steps that it mentions in the book, but none of the tables are actually upsized/converted. The error report says "Table was skipped or export failed" for every table. When I attempt to add a new table to this new project, I get an error that says "Table cannot be opened due to a dropped server connection". I have no...
2
2032
by: David C. Barber | last post by:
upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code that I've changed to ADO code is working fine, HOWEVER the form Record Source itself does not seem willing to return data. I've set the Record Source to both the query, and the SQL contained within the query, and although the system pauses long enough to have gone out and retrieved the data, I can't see it. The form itself remains gray. In addition: ...
1
2266
by: rcmail14872 | last post by:
I have a standard Access database with standard Forms and it is not split. I am going to run the upsizing wizard to change the data tables to SQL. I also need to split the database and I am going to change the Forms to be Data Access Pages so that records can be added and edited through Internet Explorer. What order should I take these steps? Can I just save the forms as Data Access Pages and they will work? I think maybe I should get...
32
2522
by: dreadnought8 | last post by:
I have a client who wishes to upsize an A97 system from Jet backend to SQL Server 2000. They have up to 4 users. Will they need to buy the minimal 5-seat SQL Server product, or would it be cheaper to upsize the front end to A2K, A2K2 or A2K3 and use the version of SQL Server provided with that? The system uses local tables extensively and I would not want to disturb this. Enterprise manager would be a requirement. TIA
3
1757
by: Holysmokes99 | last post by:
Hello, All I would like to write a VB.Net app that will take an Access 2000 database that has tables, relationships, and data (no queries, forms macros, etc), and create a corresponding SQL Express database that matches it. The application will have no idea of what's in the database ahead of time other than the fact that there are tables with data and relationships. Any suggestions on how to proceed? I suppose I need to iterate through...
4
2622
by: R.E.V. | last post by:
Hello I've seen a number of posts concerning the above type of conversion but have not been able to locate anything that gives a clear set of instructions to accomplish this. Here is my problem, I have an application to be converted from access 97 to SQL Server Express (backend) and VIsual Basic.NET (frontend). I have the upsize wizard installed and visible in access as an add-in. SSE has been downloaded and installed as well.
4
2402
by: EiEiO | last post by:
Hello, I have a front end back end application created in Access. I need to get it into SQL Server. I used the upsize wizard to create the SQL Server database and the adp front end. The front end is complaining often when I try to execute code. I tried using the mdb front end and linking tables to SQL. After renaming the table (links) from dbo_tbl_Customers to tbl_Customers, the app runs great.
5
2209
by: Yoda | last post by:
Hi, i'm working on a project for convert from a mdb AccessXP(2002) application backend to SQL Server Express 2005. In the past i convertend a similar backend to SQL Server 2000. My problem is: I have many fields on mdb backend with spaces in field name (i know bad thing) With SQL Server 2000 I had no problem because it recognize the field
0
8535
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9079
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
8954
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...
0
7626
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6458
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4322
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
4555
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2968
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
1955
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.