SQL tools & syntax 3

[ajetrumpet]

Will someone please provide me with some definitions?
I would like to know the difference between the following tools:
1)MySQL, 2)Oracle, 3)Microsoft Query

How do these relate to writing code in MS Access? Are there any differences, even minor ones? What is the connection between these tools and the SYNTAX concept? How do I found out the connection between these tools and the language that works in Access? thanks guys.

-J

 

[lespaul]

MySQL, Oracle and Access are all relational databases. MySQL is free, Oracle is VERY expensive and Access is mid range. Access also includes an interface to develop a GUI. The others you use a programming language to develop a GUI to interact with the database tables.

Microsoft Query is a GUI that allows you to use an ODBC connection and write queries in SQL (Structured Query Language) to the database connected in the ODBC.

Access uses JetSQL. There are other "flavors" of SQL. These are usually minor differences in getting the information. For example, in DB2 (and other databases), you have to use the SUBSTRING command to extract information from the middle of a string; in Access you use the MID function.

The database tables are normally referred to as "the BACKEND" and the user interface (GUI) as "the FRONTEND".

If you want to code in Access, it comes with all the tools needed to develop both the front and backend. If you want to download MySQL or buy Oracle, then you will need another development environment to develop the frontend (I think!). You could use Visual Basic, Delphi, C++, C#, Java....any development language can access database tables in one way or another.

What exactly are you trying to do?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ajetrumpet]

Thank you for the information, I got out of it exactly what I needed, but it has raised some more questions...

Take for example, this code I received from SkipVought a while back...

sub Num2Txt()
'paste into an excel module and run in excel
'change the range reference as required
dim r as range
for each r in range([B1], [B1].end(xldown))
with r
if isnumeric(.value) then "'" & .value
end with
next
end sub

This code was for Excel formatting, but I assume it is written in SQL (not VBA?). After all, doesn't the STANDARD in SQL imply the universal understanding of the language by different MS programs, or any others for that matter?

I knew about the "flavors" of the language, but I didn't know and still don't, how many there are. Is this even worth mentioning? In what context do the 'minor' differences exist?

I have a lot of knowledge about coding (as far as integration and purpose), but none of it refers to the technical aspect or the art. I would one day like to create an entire document or spreadsheet (or whatever) by coding, but I first have to understand more than just the meaning of COMMAND, CLAUSE, STRING, OPERATOR, etc...

I guess I'm asking you what I should do next if I want to develop the skills for this. I've been able to debug previously written code by looking at expressions and such in modules. I was even able to manipulate the code (mentioned above) to perform other functions in Excel without even studying the language. Just picked up on the repetition and structure. What should I do, not to get started as a beginner, but to learn the intricacies of this?
Thanks again for your information, that was well written. =)

-J

 

[PHV]

This code was for Excel formatting, but I assume it is written in SQL (not VBA?).
This code IS VBA (more precisely Excel VBA)

About the "universality" of SQL:

http://troels.arvin.dk/db/rdbms/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

a SQL statement will look like:

[b]SELECT[/b] Field1, [b]Left[/b](Field2, 3), Field3, [b]SUM[/b](Field4) 
[b]FROM[/b] TableName
[b]INNER JOIN[/b] AnotherTableName on TableName.FieldName = AnotherTableName.FieldName
[b]WHERE[/b] Field1 = "SomeValue"
[b]GROUP BY[/b] Field1, Field2, Field3
[b]HAVING SUM[/b](Field4) > 1000

It has functions (Left & SUM), filtering (WHERE & HAVING) and you retrieve records from the tables that match the criteria.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ajetrumpet]

Thanks lespaul.
How about another question? Actually this is for both of you. (PHV, the link doesn't explain to me what I want)

I have purchased a book on SQL. I get everything from history and explanations to flavors and examples. This doesn't really tell me though about the process in which I would like to learn. Tell me if I am right about it....I assume I will have to learn the constraints and coding "format" (or procedures, if you will) of each flavor (such as Access/Excel VBA) and what ever other flavors are there for other programs.

I am interested in understanding the little intricacies of code writing itself for different programs, WHY they are different, and WHAT they are so I can use them in code writing. A perfect example is this....

Using [] for a command in one flavor, and not in the other.
Am I reading to much into this, or are these legitimate questions?

Thank you both for your input.

 

[PHV]

such as Access/Excel VBA
Access VBA and Excel VBA ARE THE SAME LANGUAGE but hosted by 2 different Application objects.


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajetrumpet]

When you say SAME LANGUAGE, this means it is written exactly the same in code? If so, what about JetSQL? Is that written differently? If so, where can an explanation of the differences be found?

-J

How Do I Get Great Answers To my Tek-Tips Questions?

http://www.tek-tips.com/faqs.cfm?fid=2884

 

[PHV]

JetSQL is a flavor of Structured Query Language.
VBA is a programming language.
I quite don't understand why you persist to compare them.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajetrumpet]

I persist because I want an understanding deeper than what we're talking about here. Regarding your comment...I assume - FLAVORS of SQL are used to write procedures connected to more than one program? Programming language refers to code written specifically for action WITHIN one program?

 

[PHV]

Programming language is used to write programs.
SQL is used to write queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajetrumpet]

PHV,

So programming language writes programs AND performs SQL tasks as well, while SQL code types ONLY do query work? Yes?

If you don't want to continue this, that's fine, just say so, I see your answers are getting shorter.

 

[AlexCuse]

ajetrumpet -

'Programming Languages' include things like VB/VBA, C++, Java, etc... (think front end)

SQL Dialects will ONLY work in a DBMS, and some features will be specific to the DBMS product (like Access has some differences from MS SQL Server, and vice versa)

SQL is designed and optimized (in most cases) for set-based operations, while a 'programming' language will be better at finer details like looping through arrays and string manipulation.

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

A query is used to extract data from a table (or a bunch of tables). SQL is the language used to tell the database what information you want. All databases (MySQL, Oracle, SQL Server, Access) have the ability (via a GUI interface in most cases) to write queries in SQL on the fly and see the result set (the answer to the query - all the records in the tables that meet the criteria).

A programming language is used to develop a front end to a database and can use SQL statements to retrieve information from the database tables and manipulate the result set in much more complex ways than you can just using queries.

For example, I store information in the database tables about the number of hours a juror works in the court. If the juror is also a public employee, there are special rules that apply to the calculation of pay for public employees. They only get paid for hours outside of their normal work hours. So the database stores their normal work hours.

When the process begins to calculate pay for a specific time period the program runs a query and finds all the people who have time entries during the time period and if they are a public employee. The process then finds their normal working hours working hours and compares the time they worked at the court and if it was outside their normal hours the process determines how much time they should be paid for.

The database stores information that is retrieved via an SQL query which the program uses to process a complex calculation.

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ajetrumpet]

Per your explanation, would it make sense to say that SQL is used for simple extractions, while programming languages such as VBA are used for more complex situations (like writing commands for multiple objects)?

If I open a new module in Access to write code, am I always supposed to write the same "flavor"?

If I create a query (or any object) in Access, say, via design view, how do I view the code applied to it due to the mouse clicks I've performed and the procedures I've specified? Through modules?

-J

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?

 

[ajetrumpet]

A correction on my last question....the idea I'm trying to get at is weather or not programming language can ALSO be used to perform tasks that SQL performs. Is there any overlap between these two at all?

-J

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?

 

[traingamer]

If I create a query (or any object) in Access, say, via design view, how do I view the code applied to it due to the mouse clicks I've performed and the procedures I've specified? Through modules?

This forum isn't really designed for such basic questions.

If you've created a query in design view, simply click the 'SQL view' under 'View' to see the underlying query.

If you've created a 'form', any code in the modules is VBA.

You can call SQL from within VBA routines in several ways.


What are you really trying to accomplish? It seems to me (and I could well be wrong) that you are not asking the right questions.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ajetrumpet]

This thread is not about specific tasks or problems. This is an informational search. I am seeking to fully understand programming concepts, but I don't intend to carry this thread on forever. I HAVE books & tutorials to reference.

If you've created a 'form', any code in the modules is VBA. You can call SQL from within VBA routines in several ways.

Thank you for your response, this gets to the root of my previous question....(I don't intend to ask about the "several ways", I am sure I can figure that out through reading material.

 

[JoeAtWork]

the idea I'm trying to get at is weather or not programming language can ALSO be used to perform tasks that SQL performs. Is there any overlap between these two at all?

Any SQL is performed by the database engine. You may put together the SQL statement in your VBA code, but when it comes to actually retrieving the data, what you are doing is passing the SQL to the database engine, which processes it, and it returns the results to your VBA code. Then in your VBA you can use that data any way you want (fill in textboxes, load a grid, etc.).

 

[ajetrumpet]

Well written Joe (except for the last sentence, kind of vague) =)

How would you describe the relationship between JetSQL and VBA, if you don't mind me asking?

-J

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?