Upsize from Access 97 to SQL 2000 Problem

Terry

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

Subscribe Reply

2066

Larry Linson

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

Similar topics

1518

Can table ownership be changed during upsize to SQL

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

Microsoft Access / VBA

5576

Upsize Database to Sql Server

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...

Microsoft Access / VBA

2028

Access 2000 Form Does Not Return Data After Upsize to Project

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: ...

Microsoft Access / VBA

2264

upsize to SQL plus split database plus change Access Forms to DAP what order?

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...

Microsoft Access / VBA

2519

Upsize to SQL Server

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

Microsoft Access / VBA

1756

VB.Net code to upsize Access DB to SQL Express?

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...

Visual Basic .NET

2619

Upsize access 97 to sql server express

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.

Microsoft Access / VBA

2399

Upsize frontend and backend. Or just backend

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.

Microsoft Access / VBA

2209

Upsize from mdb to SQL Express 2005

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

Microsoft SQL Server

8277

Changing the language in Windows 10

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,...

Windows Server

8803

Problem With Comparison Operator <=> in G++

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...

C / C++

8465

The easy way to turn off automatic updates for Windows 10/11

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,...

Windows Server

8581

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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...

General

7298

AI Job Threat for Devs

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...

Career Advice

5612

Couldn’t get equations in html when convert word .docx file to html file in C#.

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();...

C# / C Sharp

4285

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1910

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1588

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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...

General