What happened to the queries after upsizing to SQL Server? (1 Viewer)

[JGT]

What happened to the stored queries defined in the FE of our Access FE-BE application, after upsizing the Access BE (just tables) to SQL Server?

As an example: [ProjectChances].[PropNum] ç related è [Sales].[PropNum]

Below the excerpt of a SQL Server printout showing the relationship between two tables:

ALTER TABLE [dbo].[ProjectChances] ADD
CONSTRAINT [ProjectChances_FK00] FOREIGN KEY
(
[PropNum]
) REFERENCES [dbo].[Sales] (
[PropNum]
)
GO

An original query using this 2 tables before upsizing:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [Sales] INNER JOIN [ProjectChances] ON Sales].[ PropNum] = [Sales].[ PropNum]
WHERE (((Right([Prop],1))<"8"));

The same query expression the way it appears after the upsizing:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [Sales], [ProjectChances]
WHERE (((Right([Prop],1))<"8"));

As one can see, the table relationship in the query just disappeared.

Worse, designing the same query starting from zero with the tables now linked to the SQL Server, the relationship that appears spontaneously in:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [ProjectChances] INNER JOIN [Sales] ON [ProjectChances].ID = [Sales].ID
WHERE (((Right([Prop],1))<"8"));
is not based on the defined BE relationships but on fieldnames: 2 fields happen to have the same name “ID” however are not related at all in the db BE design.

Apparently the FE is not aware of any relationships established at the BE level, something that also seems to be confirmed by the fact that no relationships appear in the Access relationship window.

It looks like as if I will have to rewrite all the queries of the application”, something that really surprises me: what one sees in discussion groups are statements like this: “creating links to the new SQL backend allow your application to run just as it is, with very minimal modifications” and “if you use linked tables, your front end can work virtually unchanged with SQL Server”
Can somebody give me a clue to what is happening here?

Going through MS white papers I saw a list of necessary APIs. Could it be that Access needs some SW added in order to work properly with the SQL Server?

Thank you very much for any support!

 

[SQL_Hell]

What version of sql server
What service pack is sql server?
What version of Access / service pack.

Did you get any errors relating to the problems you had in your upsizing report?


How many queries do you have to upsize?

 

[MSAccessRookie]

What happened to the stored queries defined in the FE of our Access FE-BE application, after upsizing the Access BE (just tables) to SQL Server?

As an example: [ProjectChances].[PropNum] ç related è [Sales].[PropNum]

Below the excerpt of a SQL Server printout showing the relationship between two tables:

ALTER TABLE [dbo].[ProjectChances] ADD
CONSTRAINT [ProjectChances_FK00] FOREIGN KEY
(
[PropNum]
) REFERENCES [dbo].[Sales] (
[PropNum]
)
GO

An original query using this 2 tables before upsizing:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [Sales] INNER JOIN [ProjectChances] ON Sales].[ PropNum] = [Sales].[ PropNum]
WHERE (((Right([Prop],1))<"8"));

The same query expression the way it appears after the upsizing:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [Sales], [ProjectChances]
WHERE (((Right([Prop],1))<"8"));

As one can see, the table relationship in the query just disappeared.

Worse, designing the same query starting from zero with the tables now linked to the SQL Server, the relationship that appears spontaneously in:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [ProjectChances] INNER JOIN [Sales] ON [ProjectChances].ID = [Sales].ID
WHERE (((Right([Prop],1))<"8"));
is not based on the defined BE relationships but on fieldnames: 2 fields happen to have the same name “ID” however are not related at all in the db BE design.

Apparently the FE is not aware of any relationships established at the BE level, something that also seems to be confirmed by the fact that no relationships appear in the Access relationship window.

It looks like as if I will have to rewrite all the queries of the application”, something that really surprises me: what one sees in discussion groups are statements like this: “creating links to the new SQL backend allow your application to run just as it is, with very minimal modifications” and “if you use linked tables, your front end can work virtually unchanged with SQL Server”
Can somebody give me a clue to what is happening here?

Going through MS white papers I saw a list of necessary APIs. Could it be that Access needs some SW added in order to work properly with the SQL Server?

Thank you very much for any support!

I know this is probably a dumb rookie question, but how did you get your queries upsized to Stored procedures? I got about 83 of my queries upsized to Views, and had to make the stored procedures we have by hand.

 

[JGT]

Thanks a lot for being interested in my problem.

To answer yr questions regarding SQL Server, I passed them to the SQL Server Admin., as I have no access to the Server (just the Query Analyzer) which is corporate IT business of the compagny. I got following answers:

Index Name Internal_Value Character_
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 524288 8.00.2039
3 Language 1033 English (United States)

No conversion errors reported.

Regarding Access:
MS Office Professional Edition 2003 - Access 2003 (11.6566.6568) SP2

OS:
MS Windows XP Professional - 5.1.2600 Service Pack 2

Right now there are only 2 authorized users, sitting on a corporate LAN accessing the SQL Server and connecting through Windows Authentication - ("ODBC;DATABASE=dbmasdatpg;DSN=usmasdatpg") -
Once in operation there should be about 30 users, some of them – we expect so - through WAN or VPN .

I didn’t develop the ap from the beginning, but got in somewhere rather late. I myself prefer to work in VBA /sql expressions rather than using Macros or Queries, to keep things together, whenever possible. But my predecessor, who started the ap, didn´t work in VBA at all. So there are a lot of Queries – I learned yesterday that they are called stored queries (as counterparts in Access to the stored procedures in SQL Server sic.) – of all kinds - about 150.
The same way, for the underlying info for the forms, boxes etc., I prefer to use sql expressions, but my predecessor used mostly Queries or Tables.
Finally there are a lot of queries embedded in VBA.
As the sql strings specify the relationships between the tables, they should not cause any trouble. However the “stored” queries, most of them are composed of more than one table, do not longer work without the connections between them.

To Rookie: I do not have any stored procedures (yet), there were no “stored” queries in the Access BE converted to SQL Server.

Thank you for your help. The fact that I know now that my problem is real - I was really worried of being it normal or not. – is already a relief.

 

[JGT]

In the mean time, missing the experience with SQL Server, I decided to install the SQL Server in my home Desktop, and see what happens if I convert the Access BE to SQL, far away from the corporate environment. To my great deception, after “upgrading” the Access dB into the SQL Server, and linking it through ODBC to an Access FE, I got all tables nicely linked but no relationships showed up at all. So it looks like this is the normal way things are - still a kind of strange to me, because no-one replied this way.
Bit it became clear to me that the Access-SQL FE/BE arrangement is somewhat like o socket and a plug that do not really fit, you have to bend some pins to get things running, like using a motor of one car and put it in another type of vehicle. Although working side by side, in one and the same big company, who developed Access didn’t want to know a dam about SQL and vice-versa. Different SQL dialects, different data types etc.
Although a MS recommended solution, not really a nice job.

 

[georgedwilkinson]

It was wise of you to do this away from the production server.

But the relationships are still enforced by the BE, right? They should still be doing the thing that they were made to do, and that's ensure referential integrity. The Front End no longer has to do that once the data is in another environment.

Access works in this arrangement with a multitude of back-ends, including SQL Server. The BE for all these different DBMSs are assumed to enforce RI.

The moment the FE is separate from the BE, Access becomes just another client side solution, and does it remarkably well.

The only value I've found in having the relationships displayed in the FE is that it makes better assumptions about joins when you're using the query builder. Is that the issue you're having?

 

[wazz]

The only value I've found in having the relationships displayed in the FE is that it makes better assumptions about joins when you're using the query builder.

hi george. are you saying it is possible to show the relationships from a sqlsvr BE within access?

 

[georgedwilkinson]

hi george. are you saying it is possible to show the relationships from a sqlsvr BE within access?

No, but you can re-create them (as 1:1) in Access without impacting the FKs in SQL Server. It makes using the QB much easier.

In fact, you can partially modify the table design in Access without it screwing up SQL Server. Be cautious!

 

[JGT]

Hi George,

Thanks for your reply.

No doubt about the fact that the BE has to take care of RI, this with or without SQL. What fails now is that Access FE no longer knows about the relationships between the tables – at least that is what I am experiencing. As I said, I have about a 150 so called stored queries, most of them with relationships. These links disappear, what means that the queries no longer work, as without link the outcome is the combination of all possibilities of both sides of the tables. So I have to rewrite them one-by-one.

 

[Banana]

JGT,

To make it clear, you *don't* have to tell Access anything about the relationship. You still can join (which aren't the same thing as relationship) tables in queries manually, and if you do something in Access that would violate the relationship, SQL Server will return an error and you can trap for this.

Did that help?

 

[georgedwilkinson]

I'm not sure why you'd have to re-write them. Sounds like more work than you should have to do. I've never used the wizard to split a database but I frequently create new tables in SQL and link them. The linking tool typically prepends the database owner's name to the table. I always go and rename the table so I can continue working with the raw table name. You might want to try that before you go rewriting all your queries.

I'm a little confused by what you said about the links disappearing. This makes no sense. Can you elaborate?

Also, instead of re-writing queries one at a time (I don't recommend re-writing any, BTW), you should check out V-Tools which allows you to change strings throughout your database. You can download it here: http://www.skrol29.com/dev/en_vtools.htm

Don't re-do your queries yet. Let me take a look at them if this advice doesn't help.

 

[JGT]

Hi Banana!
Going through discussion groups, gathering info, is such a great help that I went through a lot of discussions when the idea came up to upgrade the Access BE to SQL Server. As a result, I got the impression that normally an application will work with minor changes, eventually or probably one has to introduce changes to improve performance, making use of the alternate way SQL works. So I must say that I am really surprised I have to re-write all those Queries, just to keep things working.

 

[JGT]

George, I really liked you being confused about the vanished links. Its in fact hard to accept this. Just go to my initial mail and you will see the sql expression of a Query before and after upgrade. The latter has no relationship anymore.

 

[Banana]

Hi Banana!
Going through discussion groups, gathering info, is such a great help that I went through a lot of discussions when the idea came up to upgrade the Access BE to SQL Server. As a result, I got the impression that normally an application will work with minor changes, eventually or probably one has to introduce changes to improve performance, making use of the alternate way SQL works. So I must say that I am really surprised I have to re-write all those Queries, just to keep things working.

Did you use upsizing wizard? I haven't, but I strongly doubt they import the relationship information.

But the point is, you only need to define the relationship inside SQL Server, not Access. Queries then should work as normally.

Furthermore, anyone who said it'd be a walk in the park is overstating it a bit. For a simple database, it's no brainer, but when you have business rules making up for quite weird requirements, you will have to expect some manual tinkering to get it working correctly in backend's particular dialect.

 

[JGT]

Well, Banana, you just raised a nice question, does the Access Wizard import relationships? Who can answer this?
But thinking a little further, this does confuse me. The Wizard transforms Access in SQL, as a matter of fact, the existing relationships went into the SQL Server.
eg
ALTER TABLE [dbo].[ProjectChances] ADD
CONSTRAINT [ProjectChances_FK00] FOREIGN KEY
(
[PropNum]
) REFERENCES [dbo].[Sales] (
[PropNum]
)
GO

Now, once in the Server, Access establishes the links through ODBC, no upgrade Wizard is acting anymore.
The point is that if there is a relationship between table A and table B, if I am using an Access BE, the FE reflects the design of the BE.
However if I have a Query using A and B, linking through ODBC, Access doesn’t see this relationship, and the former Access Query looses the link between A and B.
Before: FROM [Sales] INNER JOIN [ProjectChances] ON Sales].[ PropNum] = [Sales].[ PropNum]
After: FROM [Sales], [ProjectChances]
The outcome without link is a disaster of cause.
Maybe re-designing the relationships on Access FE level would make things working again as before, but I do not like this kind of distorted solution at all. I expected Access to “see” the SQL db transparently. What is a dB without relationships? A spreadsheet!

 

[Banana]

That's expected behavior.

You just need to rejoin the query, but not define the relationship in FE.

See, Access's default is to "Auto-Join" queries that for tables that has relationships. All of this is implicit so when you move it out, Access thinks those two tables are no longer related and no longer implicitly joins it, when you wanted a explicit join.

As I said before, joins != relationships, but to Access, if it's related, it's *guessed* to be joined (but you can remove that manually and you can add it manually as well).

 

[JGT]

Well that’s the point, that’s what I was afraid of, I will have to compare 150 queries, looking how they were originally and than linking them again.
(That’s why I like Access so much; of course it has its weak points, but no doubt Access should be a standard for SW user-friendliness.)

If redesigning the relationships in the FE would spare me this job, it would be a winner (besides the fact that it would help me for future query design). I will give it a try.

 

[Banana]

An idea, if you still have a backup of the database before you upsized it-

Write a temporary function to loop through all queries with the correct SQL (Using SQL property of QueryDef) then create new querydef in upsized with the correct SQL.

This would be feasible, of course if you didn't alter any naming, change the structures, etc.

 

[JGT]

I agree that Access does some guessing, but rather rarely, only if no relationship is defined. As soon as a relationship is defined, guessing stops, and Access constructs the right join. More, if I switch BE dbs – I always switch between corporate LAN BE and local Test BE – whatever join was defined in FE, remains. When ODBS get in the middle, apparently this mechanism stops working. Or: Access to Access is transparent, Access to SQL Server not.

 

[JGT]

Great idea Banana, you made my day, I will loop through the queries and save them, thanks a lot..