Do I need SQL server to run SQl Queries in Access?

Does MS Access need SQL engine installed?

Trying some simple queries for a client, I dont know if they have SQL server installed (probably don't), they would work much more smoothly if I used SQL instead of VBA. SO do I need SQL Server installed to run SQL queries in stand alone MS Access?
Hares FakAsked:
Who is Participating?
 
Ryan ChongCommented:
SO do I need SQL Server installed to run SQL queries in stand alone MS Access?

not necessarily. Both are different products which can worked independently without one another.
1
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No. In stand-alone Access, you use SQL to access Access (pun intended) tables or views. The SQL functions and features are slightly different between MSSQL, Access and e.g. Oracle, so you often need to change your queries if switching the backend (where the DB is running).
2
 
PatHartmanCommented:
Access is not a database engine.  Jet/ACE and SQL Server are database engines.  Access is a Rapid Application Development (RAD) tool. It is used to create forms/reports/queries/macros/code.  The database engines manage the data.  Access can natively use Jet (.mdb) and ACE (.accdb) or via ODBC, pretty much any database engine on the market.

When you create embedded SQL or querydefs in Access, you use Access syntax for the SQL.  If you want to create pass-through queries.  The syntax must be whatever the ODBC database requires.  In the case of SQL Server, that would be T-SQL.

So, to attempt to answer your question.  You would only need SQL Server installed if you wanted to use SQL Server as your database engine.  When you install Access, you also get either Jet (.mdb) or ACE (.accdb) installed automatically.

Because Access is a separate product from Jet and ACE, you can use Jet and ACE without having Access installed.  You can think of Access as SSMS for Jet and ACE.  They can use DAO/ADO or DDL queries to manage all objects. You only need Access installed if you want to use a GUI to create database objects.  The converse is not true.  Access uses either Jet or ACE to store its own objects but it does NOT need Jet or ACE to hold data.  Data can be any relational database that offers an ODBC interface.  This dependency causes great confusion and so people who do not understand what Access actually is, blame Access for the shortcomings of Jet and ACE.  The reality is that Jet and ACE are excellent database engines if you want a desktop engine, your data can fit in 2g, and you won't have more than 50 concurrent users.  Beyond that, Access is still an excellent RAD tool, you just upgrade your database engine.
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.

 
HuaMin ChenSystem AnalystCommented:
SQL server is totally different from MS Access.

When running MS Access query, it definitely DOES NOT rely on SQL server.

One probably linkage between MS Access and SQL server, is that, you may use ODBC to access SQL server, within MS Access.
0
 
PatHartmanCommented:
The question was asked and answered.  It is quite possible that someone else might have the same question and this will help them.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Closing recommendation:
Accept with equal split between #a42518580, #a42518582 and #a42518782.

The first two comments (posted within "the same" time frame) answered the question sufficiently, the next one added some details to give more background.
0
 
HuaMin ChenSystem AnalystCommented:
I suggest to also include my reply as one of the answers.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No, because you added nothing not already stated earlier. Hence I did not include your comment in my suggestion.
0
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.

All Courses

From novice to tech pro — start learning today.