Convert SQL code to Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Convert SQL code to Access

Author

Topic

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2005-09-30 : 13:49:33

I am using Access to design an interface to an MSSQL database. I dont know either very well, and of course this is causing all kinds of problems.

I have a simple sql query:

select c.computer
from tblComputer c
left join tblServer s on c.computerID = s.computerid
left join tblAccountSecurity a on a.serverid = s.serverid

(tested in MSSQL, the query returns exactly what I want)

Access didnt like the abbreviations, so I junked them. It is now giving me errors on the joins... I tried using the generic "join" and that failed. I am consistently running into stupid Access problems - but I have to use it for this interface. Ideas? Help?

Much appreciated.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-30 : 16:00:24

join and left join (= left outer join) are not the same and will give different results (probably).
Can you post what you tried ad the error message as I think it should have worked.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2005-09-30 : 16:45:44

I know that "join" and "left join" aren't the same, but I figured that Access would just default to some sort of join. I just wanted to see the statement work, before actually made sure it was the proper values that came up. My faith in Access is very high, as you might be able to tell haha. I really do appreciate the help.

select tblComputer.computer
from tblComputer
left join tblServer on tblComputer.computerID = tblServer.computerid
left join tblAccountSecurity on tblAccountSecurity.serverid = tblServer.serverid

Syntax error (missing operator) in query expression 'tblComputer.computerid = tblServer.computerid left join tblAccountSecurity on tblAccountSecurity.serverid = tblServer.serverID' - highlighting the first left join.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-01 : 17:25:52

I would expect that to work.
Access tends to put brackets round things so maybe that would help.

select tblComputer.computer
from tblComputer
left join (tblServer
left join tblAccountSecurity on tblAccountSecurity.serverid = tblServer.serverid)
on tblComputer.computerID = tblServer.computerid

Try that - I'll give it a go next time I get a m/c ith access on it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2005-10-02 : 20:38:05

Brilliant! I don't understand why Access is so anal.

Thank you so much for your help!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-10-02 : 20:51:49

Because Access is a piece of s$@#. That explains the anal piece quite well.

Moderators, feel free to delete this.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources