How Do I Translate My Access Programs to SQL?

xljunkie

Board Regular
Joined
May 20, 2011
Messages
92
So I'm a long time user of Access, and I know some SQL. I have Access programs that run many different queries, manipulate the data, and I might end up with a singular table that is the result of many different tables' results and data transformations.

When I go to SQL, there is space for me to write a SELECT FROM WHERE statement which would give me one table of results. If I wanted to perform two different SELECT statements in the same process in a logical flow of a program, how do I do that? If I hit F5 in SSMS in a query that contains two SELECT statements, I'll get an error.

I'm trying to connect how I normally write a program in Access to how I can achieve the same in SQL. Do I need to look into stored procedures? Or is there something else I need to research?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
How are you writing the separate queries?

Each one needs to be terminated with a semi-colon.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,667
You don’t get an error running multiple selects in smss, I do it all the time - nor do you need to terminate with semi colons, though it’s good practice.

I fear that you may misunderstand the difference between Access and SQL server. One isn’t usually a direct replacement for the other, access offers a programming environment, macros, forms etc and is a user facing tool. SQL server is not, it’s only part of the equation and you need something else as well to generally write a program or provide something to end users.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,526
So I'm a long time user of Access, and I know some SQL. I have Access programs that run many different queries, manipulate the data, and I might end up with a singular table that is the result of many different tables' results and data transformations.
Generally you can do this easily in SQL server in a proc or even in a single script that runs like a query. As stated - you certainly can run two or more sql queries in one script, create and use temp tables as well, so a lot is possible.
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,170
your question is confusing to me

MS Access allows you to write queries using a language called SQL
you can write queries normally in Access using Design View, but then you can do sql view and see the actual sql

SQL Server is a Microsoft product that is "kid of similar" to Access but MUCH more advanced
SQL Server also uses the language called SQL

SSMS is SQL Server Management Studio, also a Microsoft product

SQL Server is the product that stores the tables and data and queries (these queries are known as stored procedures)
SMSS is the product that connects to SQL Server and allows you to create tables and queries using SQL or a point and click user interface

Access can also connect to SQL Server and you can use SQL to create tables and queries
Access is not SSMS

so, where does your data live ?
in Access or in SQL Server ?
are you trying to write SQL in Access or in SSMS ?
 

Forum statistics

Threads
1,078,285
Messages
5,339,296
Members
399,292
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top