a transalator that can convert MS SQL 2014 query to MS Access SQL query

Hi Experts,

Is there a converter/translator for query where can convert MS SQL 2014 query to MS Access SQL Query?


Thanks for the help.
power12345sgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
What do you mean? Do you have the access mdb linked on the ms SQL ?
What version access mdb are you dealing with?
0
power12345sgAuthor Commented:
They are not link. I am using Ms Access 2013
I am wondering if I can just paste in the query direct into a converter to translate into MS access 2013 query from SQL statement.
0
arnoldCommented:
I am not familiar.  Is linking the access DB to an SQL server a consideration option?

Presumably you gave an access based app?
Breaking it into two with the DB part linked in SQL server, with the interface part in access.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arnoldCommented:
What is the query and error?
0
power12345sgAuthor Commented:
Below is the query in Ms SQL 2014..but how to convert it into Ms Access 2013 sql?


select count(*) as NumRows
from tblChanges as c
where Date1 = '2015-09-05'
;

declare @what_day as date = '2015-09-05'

select
      convert(varchar(10),c.Date1,121) as Date1
    , c.ConNo
    , c.Value as LastRecordedAT
from tblChanges as c
inner join (
        select ConNo, max(Date1) as Date1 from tblChanges
        where Date1 <= @what_day
        group by ConNo
      ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
;

declare @what_day as date = getdate()
    , convert(varchar(10), @what_day,121) as Paramdate
    , c.ConNo
    , c.Value as LastRecordedAT

select
      convert(varchar(10),c.Date1,121) as Date1
    , convert(varchar(10), @what_day,121) as Paramdate
    , c.ConNo
    , c.Value as LastRecordedAT
from tblChanges as c
inner join (
        select ConNo, max(Date1) as Date1 from tblChanges
        where Date1 <= @what_day
        group by ConNo
      ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
;


declare @what_day as date = getdate()

select
      c.Value as LastRecordedAT
    , count(*) as count_of
from tblChanges as c
inner join (
        select ConNo, max(Date1) as Date1 from tblChanges
        where Date1 <= @what_day
        group by ConNo
      ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
group by
      c.Value
;
0
arnoldCommented:
You have four separate queries. Does each one fail or does it fail when all are part of the request..


You second declaration seems strange to me.

Your copy/paste might have gotten to mixed up.............

What is the error?
0
power12345sgAuthor Commented:
0
power12345sgAuthor Commented:
Especially look at the very last comment where my problem is
0
arnoldCommented:
When you run a query from the example provided, what is the error?
It is the issue not with an error, but with the sata being returned?

is this supposed to be a report in a firm where one of the parameters is a date?
0
power12345sgAuthor Commented:
http://sqlfiddle.com/#!6/fbbe3/2

Maybe you can take a look at the above link given by previous attempt last expert which produce results I want . However, it is in MS SQL 2014 query but I want them in MS ACCESS 2013 SQL query..
0
power12345sgAuthor Commented:
It is a query but ultimately I will produce them as report by using the Query Wizard in MS Access.
0
power12345sgAuthor Commented:
The purpose of the query is to generate date and count the numbers of containers at various locations on a select date..
0
arnoldCommented:
The query you can create straight is the initial select

The inner join ( select ) as tablename on  is presumably what you are looking to include in the query.

Is tge main thrust of your need deals with how to have the query examples incorporated into your setup?

I believe access has the option of using the query wizard or directly.
0
power12345sgAuthor Commented:
I prefer for query set up will be generating date till Now() or todays date instead of select a date..
Access has limited functionality in query wizard....it need an MS Access SQL statement for complicated situation.
0
arnoldCommented:
Instead of getdate() you would use now()
You could within form request define, now, today, this month, this year.......


You should request attention, remove the references to ms SQL to have Access experts notified who would be in a better position to assist.


(Select columns from table wher ) as table_reference is an access syntax to allow join,
0
Gustav BrockCIOCommented:
There is not smart way.

You will to understand what the original query does, know how to do the same in Access SQL, and then rewrite the query. For example, replace @what_day with Date().

/gustav
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's an article based on my experience moving from an Access developer to a SQL Server developer -->  Migrating your Access Queries to SQL Server Transact-SQL
0
PortletPaulfreelancerCommented:
>>"There is not smart way.

I agree with Gustav.

If my query is doing what you want, then spend time understanding the logic of that query and then build an equivalent in MS Access.

Note I DELIBERATELY used a date parameter in my answer to your previous question because of this requirement:
          >>"I will be able to count the total number of containers at various location by picking a day

If you don't need that any more, as I mentioned in the previous answer, just use NOW() instead.

====
by the way. if that previous query does meet your functional needs perhaps you could close off that question?
0
Nick67Commented:
Is there a converter/translator for query where can convert MS SQL 2014 query to MS Access SQL Query?

I don't know of an automatic translator, like those you see for c# and VB.Net
SQL is SQL, but it is the various 'programming' extensions of the various databases that make such a tool non-existent.

With Access, you cannot declare variables and you can only return a single select statement.
So, this SQL wouldn't translate direct to Access in any way shape or form.
You may be able to knock it together with some UNION statements, but what you have posted won't become an Access query without significant clarification and rework.

If you have access to create a stored procedure on the SQL Server, you should probably leave the SQL in the stored procedure and get it using a passthrough query from Access, anyway.  It will be faster and more efficient to do the processing on the server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
The variable is NOT NEEDED.

In Access it would be substituted by a parameter from a form.

OR - as mentioned - if you dont need the flexibilty, just use NOW ()  in the where clause
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.