How do you design for both Access and Sql Server

Question

How do you design for both Access and Sql Server

mtait

SSC-Addicted

Points: 464
More actions
September 1, 2005 at 4:24 am

#165049

Hi - like most people I expect, I am trying to develop applications to run with either and Access or a SQL Server backend - using .net. I thought all I would need to do is change my provider from SqlClient to OLEDB in .net - but it appears what works in one DB, does not work in the other, when it comes to joins. So I find I am having to completely rewrite all my queries to run on bothe databases.
For example:
SQL Server:
SELECT dmsnet_files.id, dmsnet_users.user_id
FROM   dmsnet_files INNER JOIN
       dmsnet_groupfolder ON dmsnet_files.parentid = dmsnet_groupfolder.folder_id INNER JOIN
       dmsnet_users INNER JOIN
       dmsnet_usergroup ON dmsnet_users.user_id = dmsnet_usergroup.user_id ON dmsnet_groupfolder.group_id = dmsnet_usergroup.group_id
WHERE (dmsnet_users.user_id=1) AND (dmsnet_files.id=34)
Access:
SELECT dmsnet_files.id, dmsnet_users.user_id
FROM   (dmsnet_files INNER JOIN dmsnet_groupfolder ON dmsnet_files.parentid = dmsnet_groupfolder.folder_id) INNER JOIN
       (dmsnet_users INNER JOIN dmsnet_usergroup ON dmsnet_users.user_id = dmsnet_usergroup.user_id) ON dmsnet_groupfolder.group_id = dmsnet_usergroup.group_id
WHERE (((dmsnet_users.user_id)=1) AND ((dmsnet_files.id)=34));
This is just for simple Inner Joins - it looks even more complicated when it comes to Left Outer Right joins etc.
So, have I just done this the wrong way around - by writing the queries in SQL Servers EM first, rather than MS Access's designer?
Will a query designed in Access firstly, always work in SQL Server?
Is there a rule of thumb when using parenthesis?
Any general pointers on how to write for both would be really useful.
Thanks for any help,
Mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

As a general rule, Access writes its queries through Wizards from the inside out, (hence the RIGHT JOINs - which always throw me) but they often work for SQL Server.

Access uses different functions though, and that can mertilize you. For example Now() versus GETDATE().

You may have to keep individual SP's for one or the other and test which application you are using before calling them through some type of "Main" SP called before calling any other SPs...

Just a thought for an approach. (I migrated a terrible Access application to an existing SQL Server set up, (the company did not do good Due Diligence before buying this new company) and it can be a real bear...).

I wasn't born stupid - I had to study.

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 2

Depends on what Access version you are using since the more recent ones work with MSDE (slimmed down sql server). In this case they have the same query syntax.

1) The queries syntax are mostly the same between Access-backend and Sql Server.

Some differences:

wildcare % versus wildcard *

CASE ... WHEN -> iif (...)

getdate()<-> now()

If you omit the owner in your SQL-Server statements, you can run in problems later. (normally owned by dbo)

I would make those queries in ANSI SQL, since the query designer in Access really loves parentheses.

It might be non-trivial to convert Access functions for SQLServer

Beware that Access is pickier in outer joins.

If possible use ISO-format for dates (not sure if Access is still restricted to US-format)

2) Perhaps you should write 2 DAL's (Data Access Layers) and use the one appropriate for the backend.

3) You mostly don't need parentheses only when the order of logic is in danger

AND mixed with ors

mtait SSC-Addicted Points: 464 More actions · Answer 3

Thanks for the replies.

It's the parenthesis mainly that's the problem - the actual query, and structure are the same (given the small changes you've mentioned in functions), but it's Access which won't live without the brackets.

Cheers for the help,

Mark