Moving an Access 2003 App to SQL Server 2008 (1 Viewer)

[Tallboy]

Hi Guys,

I have been asked to investigate the efficiency (and wisdom) of moving an Access 2003 DB to SQL Server.

Obvioulsy splitting the database into a Access Front End(FE) and Access back End(BE) Then imporintg the MS access tables into SQL Server.

Then relinking the SQL Server tables to the Access FE( which has the forms and the queries).

My question relates to the linking, does this give an efficiencies over previous linking to the Access BE ?

This is a relatively small database with no more than 5000 records of 30 fields storing the usual client name client address etc etc.

My concern relates to the queries.

In true client server the queries would be stored in the SQL server and called from the FE but this now cannot be done as we have lost that advantage.

Unless there some way to import the Access queries without any moficiations?

The Access DB is working perfectly over a WAN but company policy to to migrate to SQL Server!

 

[bparkinson]

When you migrate your data to SQL Server, your local Access queries would still work, as long as when you link the SQL Server tables, you rename them to the names the queries expect. However, in SQL Server, you have Views, which are like queries. In my app which is Access 2010 FE SQL Server 2008 R@ BE, I make extensive use of SQL Server Views. They end up linked in the FE as if they were tables.

My advice is to ditch the Access queries and take advantage of Views. Rewrite the queries as Views and let the data processing happen on the server where it belongs, which is what I think you are asking. I have hundreds of views in my app, but not a single Access query.

Also, there enormous advantages to using Stored Procedures whenever possible. A good example would be marking a batch of orders as shipped. Much more efficient to just pass a batch number to a stored proc rather than handling it in the FE.

Stored procedures can replace Access UPDATE and APPEND queries.

 

[Tallboy]

Hi BParkinson,

I am in total agreement with you on taking the queries into SQL server and using views and SP etc.
But nfortunately my hands are tied in this case and I want to avoid pitfalls down the line.

I understand what your saying re linking sql tables, but I had the impression its not all plain sailing as sql never stores dates differently!

Any experiences that people have in taking the path of moving just the tables into SQL Server (and hitting problems/failing) would be greatly apprecaited


regards to all

 

[mdlueck]

Any experiences that people have in taking the path of moving just the tables into SQL Server (and hitting problems/failing) would be greatly appreciated

Access and any SQL database product are very different from each other. I would be very concerned about the accuracy of queries in the case that you did NOT use Pass-Through queries to be executed by the SQL back end.

If you do NOT use Pass-Through queries, then you end up with a fight between Access and the SQL product both messing with the query. As just one example, Access has a very weird/ugly table join syntax, whereas SQL databases use the SQL JOIN statement. I would not care to imagine sending a complex JOIN type query with double interpretation! Yikes!

 

[Galaxiom]

If you do NOT use Pass-Through queries, then you end up with a fight between Access and the SQL product both messing with the query.

Can you give an solid example?

I mainly use ADO/OLEDB connections to the server these days but in the past I have not had any problems running queries in Access on ODBC linked tables in SQL Server.

The ODBC interface is supposed to deal with the language differences.

(I acknowledge that PassThrough queries are better than Access queries because they use the server's capabilities.)

 

[tehNellie]

[edit] Late night, facts probably wrong, will double check in morning

 

[Galaxiom]

I think what Nellie said in the post before they deleted it was pretty right (it was in the email notification). The tables are just held on the server. Access's JET/ACE engines are doing the work on the client machines and just reading the data from the server via the ODBC DSN.

There is very little gained by simply moving the tables to the server and linking them back to Access. However it is a foundational step toward setting it up as a true server/client database and the change can be implimented incrementally.

It also faciltiates the potential for better security.

 

[mdlueck]

Can you give an solid example?

Been too long to provide an example of a broken non-pass-through query. All I remember is needing to join a few tables, query would not return the expected result set. I discovered how to properly submit queries via Pass-Through directly to the SQL BE DB, and never went back!

I believe this was the link that first put me onto being able to define queries in pass-through mode.

"Create tables from the results of a pass-through query (MDB)"
http://office.microsoft.com/en-us/a...-of-a-pass-through-query-mdb-HP005188053.aspx

I do a slight variation in that I do not create tables on-the-fly, rather leave them in the FE DB so that I can properly optimize them with indexes and what not. Inner QueryDef does the Pass-Through query to the SQL BE DB, outer wrapper QueryDef downloads records from the inner QueryDef and places them in the FE temp table.

 

[Galaxiom]

All I remember is needing to join a few tables, query would not return the expected result set.

Maybe you used a TSQL query in Access. The join bracketing syntax in TSQL and JetSQL is different.

I do a slight variation in that I do not create tables on-the-fly, rather leave them in the FE DB so that I can properly optimize them with indexes and what not. Inner QueryDef does the Pass-Through query to the SQL BE DB, outer wrapper QueryDef downloads records from the inner QueryDef and places them in the FE temp table.

That seems really clumsy to me.

 

[tehNellie]

I think what Nellie said in the post before they deleted it was pretty right (it was in the email notification). The tables are just held on the server. Access's JET/ACE engines are doing the work on the client machines and just reading the data from the server via the ODBC DSN

Doh! I also didn't like the tone of what I wrote, so sorry for that.

The short version for those that missed it was in response to:

My question relates to the linking, does this give an efficiencies over previous linking to the Access BE ?

And the answer was "no"
With a bit of explanation as per Galaxiom (i.e. that all the queries are in access so SQL Server is just storing the data meaning you get none of the advantages of SQL Server while retaining all of the annoyances and slow performance of Access)

 

[mdlueck]

Maybe you used a TSQL query in Access. The join bracketing syntax in TSQL and JetSQL is different.

I do no bracketing in my JOIN syntax.

Later I asked about the differences, and vabInet answered that Access and SQL Server do not use the same JOIN syntax:

"SQL Server vs Access JOIN Syntax"
http://www.access-programmers.co.uk/forums/showthread.php?t=219764#post1121621

That seems really clumsy to me.

I will not argue with success, no matter how "clumsy" the configuration appears on the surface. For me more important is reliable answers to my SQL queries. Pass-Through definitely was the answer I was seeking.

 

[mdlueck]

Can you give an solid example?

Searching for an old post of mine, I came across the post which turned the light on about Pass-Through queries...

"Passing CURRENT_TIMESTAMP through from an Access 2007 query?"
http://www.access-programmers.co.uk/forums/showthread.php?t=214245

No going back after that discovery!

 

[Tallboy]

Hi guys, im enjoying reading your submissions thanks for contributing.

I know a bit more now and can advise.

The sql server will just be storing my tables, and are then linked back to the access FE.

All queries will be executed on thr FE.

I know this will never be true client server, it will not be efficient either.

But i am wondering what other pitfalls i wil meet?

 

[mdlueck]

But i am wondering what other pitfalls I will meet?

Perhaps the application going the way of the S.S. Titanic... (shrug)

You have been warned.

 

[bparkinson]

Hi guys, im enjoying reading your submissions thanks for contributing.

I know a bit more now and can advise.

The sql server will just be storing my tables, and are then linked back to the access FE.

All queries will be executed on thr FE.

I know this will never be true client server, it will not be efficient either.

But i am wondering what other pitfalls i wil meet?

I still don't understand why you are being forced into using worst practices. Not using stored procedures where you can is guaranteed to give you much poorer performance. You also forgo security advantages, code re-use opportunities, modularity, not to mention triggers.

Is it the case that IT won't let you into the SQL Server, or that they won't assign you a SQL Server DBA? I can't imagine any competant IT dept with SQL Servers wanting this application architecture.

 

[mdlueck]

Can you give an solid example?

Found another one. I am building a query in the SQL Server Management Studio which will not execute in Access against the Linked Table from SQL Server in non-Pass-Through mode.

SQL Server working query... early in development:

SELECT MAX(CASE [OCpddoco] WHEN NULL THEN NULL ELSE [OCpddoco] END) AS [OCpddoco],
       MAX(CASE [OVpddoco] WHEN NULL THEN NULL ELSE [OVpddoco] END) AS [OVpddoco]
FROM (SELECT [pddoco] AS [OCpddoco], NULL AS [OVpddoco]
      FROM [dbo].[jdepo]
      WHERE ([pddoco] = 300133)
      UNION
      SELECT NULL AS [OCpddoco], [pddoco] AS [OVpddoco]
      FROM [dbo].[jdepo]
      WHERE ([pddoco] = 1307058)) AS [x];

Should be Access equiv, however does not work:

SELECT MAX(CASE [OCpddoco] WHEN NULL THEN NULL ELSE [OCpddoco] END) AS [OCpddoco],
       MAX(CASE [OVpddoco] WHEN NULL THEN NULL ELSE [OVpddoco] END) AS [OVpddoco]
FROM (SELECT [pddoco] AS [OCpddoco], NULL AS [OVpddoco]
      FROM [dbo_jdepo]
      WHERE ([pddoco] = 300133)
      UNION
      SELECT NULL AS [OCpddoco], [pddoco] AS [OVpddoco]
      FROM [dbo_jdepo]
      WHERE ([pddoco] = 1307058)) AS [x];

The Access version complains:

Syntax error (missing operator) in query expression 'MAX(CASE [OCpddoco] WHEN NULL THEN NULL ELSE [OCpddoco] END)'.

By pushing the Pass-Through button and entering SQL Server credentials, the SQL Server query works perfectly from Access.

 

[Galaxiom]

Found another one. I am building a query in the SQL Server Management Studio which will not execute in Access against the Linked Table from SQL Server in non-Pass-Through mode.

By pushing the Pass-Through button and entering SQL Server credentials, the SQL Server query works perfectly from Access.

Sure but that is not a case of "a fight between Access and the SQL product both messing with the query".

It is simply because SQL Case is not supported by JET/ACE. Access uses the Switch function to achieve similar functionality.

However there is never a situation where Access and SQL Server will "both mess with the query". The query either goes to the SQL Server engine when using a pass through or it is processed on the client by the JET/ACE engine against the linked tables.

Provided the appropriate dialect is used the results will be as expected.