Newbie: Converting T-SQL to Access-SQL and vice versa

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Hi,

My apologies if I am out of topic.
I was wondering if there is a small script, program or web reference that
would explain the syntactical differences between TSQL and Access SQL.

TIA
-steve
 
Tsql is vast by comparison, ie it covers creating cursors, triggers, sending
emails, configuring jobs etc, it's not just a set of common functions with
slight syntactical differences.
 
You are right, and I am sorry for not being clear enough, but i was hoping
the "Newbie" part of the subject would suffice.

I am talking about *simple* stuff.
e.g.
myTbl.value > '55.5' OR "55.5" OR 55.5
cast operators
Need a comma to terminate the qurry or you dont.

.....etc.
 
Steve,
Maybe. TSQL is much bigger and better than JetSQL. If there are specific
results you are looking for I might be able to illustrate how to write a
SELECT statement in both that gets the same result. Given the option,
though, I'd write in TSQL.
 
Actually my application is supposed to acess the same databse in either
Access or SQL Server.
Before you jump: i know about the danger of updating it!... :)

So I construct my SQL querries (simple enough) in TSQL and then , if the
user has selected to access the DB locally (Access version) then i pass the
SQL string through a function that will transform it to JetSQL.

For example how to transform the comments ( -- Tsql comment ) lines in
JetSql comments.
Single quotes to double quotes , etc.

TIA
 
steve said:
Actually my application is supposed to acess the same databse in either
Access or SQL Server.
Before you jump: i know about the danger of updating it!... :)

So I construct my SQL querries (simple enough) in TSQL and then , if the
user has selected to access the DB locally (Access version) then i pass the
SQL string through a function that will transform it to JetSQL.

For example how to transform the comments ( -- Tsql comment ) lines in
JetSql comments.
Single quotes to double quotes , etc.
Click to expand...
Basically, you can't expect to do this in any reasonable timescale.

Even fairly simple queries could be difficult and anything with CASE
statements or similar would be practically impossible.

There are situations where T-SQL allows sub-selects but JET doesn't. The
list goes on and on.

By the way, unless they've snuck in recently, you can't have comments in
JET SQL.
 
Exactly!
Not complete, and as other members mentioned, almost impossible to do due to
the difference in size and power between Jet and TSQL. But, pretty much what
i wanted!

Thanx everyone for your time and effort!!!

-steve
 
I went down that road a couple of years back.
But we did pretty much a complet re-write,
including changing table structures.
The big problem in this case was data migration.

Good luck.
 
Back
Top