By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Converting MS Access Queries to TSQL in SQL Server

P: n/a
I am attempting to upsize from access to SQL Server. I'm trying to convert
my queries to SQL Server views; however, I'm having a lot of difficulty with
the syntax differences.

For instance, a query with select mid(tblTable.FieldName,1,10)should look
something like select substring(tblTable.FieldName,1,10).

This is really the simplest of examples. I've tried using the SQL Server
Import tool, but it takes the queries and changes them into actual tables in
SQL Server. I've attempted to use the upsizing wizard, but I get an OVERFLOW
error.

Does anyone know of a tool that will convert the MS Access query syntax to
SQL Server?
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
OVERFLOW errors are almost always caused by DATE fields that have bad data,
say, a date
like 2/5/1099. That date is legal in Access, but not in SQL Server.

Make sure that all of your dates are valid SQL Server dates before upsizing.

From ther SQL Server 7 Books Online:

datetime:
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy
of three-hundredths of a second, or 3.33 milliseconds.

smalldatetime:
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy
of one minute.

To play it safe, you should make sure all of your dates are >= 1/1/1900. If you
absolutely need dates prior to that, then make sure they're all >= 1/1/1753,
and pray the upsize wizard uses datetime. If you need dates prior to that,
(maybe your application is tracking composer birth/death dates), then you have
to use a text field.

As far as converting your queries to views, SQL Server doesn't have near the
rich function library that Access has available to it. The MS Access dev team
has perfomed a small miracle by making Access SQL recognize VBA functions!

There's no shortcut for this...you nust convert them manually if they use VBA
functions.
Nov 12 '05 #2

P: n/a

You're right. I was shocked when I saw the oh-so-short list of functions
that come with SQL Server. I have definitely used just about everything
in my MS Access queries.

I have a database with .asp pages (using ultradev). And unless they had
the National Standards Format for health insurance claims back in
Beethoven's day, the date issue should be fine.

I found that the SQL Server Import will take care of the data problem if
you use the wizard carefully.

Guess I'll have to roll up my shirtsleeves and begin the task of
converting my queries...

Thanks for your help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
Before you do all this nasty stuff manually, try this:

http://datafast.cjb.net/

go here and then find the DataFast Utility... that might work for you...
Nov 12 '05 #4

P: n/a


I tried the tool, and I still have a problem where I've used functions
in Access that don't exist in SQL Server.

The current database is MS Access and ASP Pages looking at data through
queries. I'm not averse to using SQL Server and then the queries in MS
Access to view the data. However, when I link a MS Access databas to SQL
Server and create a query against that table, when I go into Ultradev
(the tool with which I have coded asp pages), the query isn't listed as
an available recordset.

I'm just shocked at the limited number of pre-defined functions SQL
Server has. I had no idea.

Anyway, I'll continue to use the tool, though, and where it cannot
handle a given function, will somehow code around it.

Thank you SO much for your help!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
Dana Shields <da*********@softhome.net> wrote in news:3f8c0b23$0$198$75868355
@news.frii.net:
I'm just shocked at the limited number of pre-defined functions SQL
Server has. I had no idea.


Huh?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #6

P: n/a
"Dana Shields" <da*********@softhome.net> wrote:
I am attempting to upsize from access to SQL Server. I'm trying to convert
my queries to SQL Server views; however, I'm having a lot of difficulty with
the syntax differences.

For instance, a query with select mid(tblTable.FieldName,1,10)should look
something like select substring(tblTable.FieldName,1,10).


FWIW where possible move the functionality out of the query. Accesses built in
functions makes life so much easier we're quite spoiled. So in this example can you
move the mid function to the form, report or VBA code?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #7

P: n/a
Umm... there are tons of functions in sql server. I haven't found anything
yet that I couldn't do, but I had to learn some new tricks first.

If you find a need for something and can't find a function, you can even
build it yourself if your using Sql 2000 with User Defined Functions. The
database can even react to the data by using table triggers, something
access doesn't do. The list goes forever, in which probably hard to imagine
from your current perspective.

Keep in mind that sql server is a product that is just for data, pure and
simple, built only to handle data. While I know and love Access functions,
I have to say that I don't really miss them in my daily work and feel that
sql server is the best tradeoff I have made in my development career. Give
it some time and you will see what I am talking about. For instance, check
out "Books Online" and select T-Sql and type "Function" as a sort term.

:)

"Dana Shields" <da*********@softhome.net> wrote in message
news:3f*********************@news.frii.net...


I tried the tool, and I still have a problem where I've used functions
in Access that don't exist in SQL Server.

The current database is MS Access and ASP Pages looking at data through
queries. I'm not averse to using SQL Server and then the queries in MS
Access to view the data. However, when I link a MS Access databas to SQL
Server and create a query against that table, when I go into Ultradev
(the tool with which I have coded asp pages), the query isn't listed as
an available recordset.

I'm just shocked at the limited number of pre-defined functions SQL
Server has. I had no idea.

Anyway, I'll continue to use the tool, though, and where it cannot
handle a given function, will somehow code around it.

Thank you SO much for your help!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.