Programming Languages used in Access (1 Viewer)

[HeavyD]

Hello folks,

So far Access has proven to be a very powerful tool. I have enjoyed the learning experience.

I do have a question regarding the different programming languages used in Access. How many languages are there?

I've seen this:
Public Sub MakeNew_Click()
DoCme.SaveRecord
DoCmd.GoToRecord , , acNewRec
End Sub

I've seen this:
SELECT [ProductName], Count(*) AS Expr1
FROM Inventory
WHERE [AssetNumber]="pending"
GROUP BY [ProductName]

I've read the terms Macros, Expressions, VBA, SQL...It's dizzying trying to wrap my head around the differences. I find it difficult to articulate my questions and often finding myself looking for examples that are in the format that I am somewhat familiar with. Please help me unlock the full potential of Access. At present, I am competent at C and C++.

Thanks for the insight.

 

[NauticalGent]

Your question isn't worded accurately. The examples you are showing are BOTH VBA - Visual Basic for Applications. Your first example are Macros, the second is raw SQL - Structured Query Language. Both are using VBA to execute but they are using a different WAY to accomplish the execution.

I am sure that I have misspoken and/or left something important out. Sit tight, there are others who will chime in soon who will clean up after me!

 

[sonic8]

Sit tight, there are others who will chime in soon who will clean up after me!

You should be old enough to clean up after yourself. ;-)

SQL, in Access and elsewhere, is definitely not VBA. Even though SQL can be used from VBA code and SQL can call VBA functions, they should be considered completely independent languages.

In other Microsoft Office application Macros and VBA are indeed the same. Access however uses a macro "language" different from VBA. Their relation is the same as between SQL and VBA. Both can use (call) each other, but should still be considered independent.

Expressions are just that. Tiny building blocks, like formulas, that can be evaluated to "calculate" a result.

 

[NauticalGent]

You should be old enough to clean up after yourself. ;-)

 

[theDBguy]

I could be wrong here, but I think the reason for so many ways to get things done in Access is because it doesn't use just one tool but many. Please don't quote me on this, but for example, SQL statements are executed using the database engine, VBA code are executed by the VBA interpreter, expressions are executed by the Expression Service. I don't even know what executes macros.

 

[The_Doc_Man]

Access uses VBA exclusively and no other language that we can see. BUT... it has an auxiliary database engine called ACE (or, if you have a MUCH older version of Access, it's called JET) that uses SQL. Having said that, I have a vague memory that the implementation language of Access is some variant of C, probably C++.

Structured Query Language (SQL) has been around for at least a decade before the first version of Access. The first "official" standard for SQL that I remember was ANSI-88, but other standards have been released since then. I believe that ACE uses the ANSI-92 standard for SQL, with limitations having to do with the external interfaces.

I don't believe that you can use ODBC protocols on ACE to any degree, because if you could, we would have a web section in a heartbeat. There is actually a tech reason for that. Has to do with a "virtualization bubble" - which is tech-speak for having a virtual internal environment that hides things from the outside world.

Unable to use the Access ODBC, OLEDB or DAO interfaces outside Office Click-to-Run applications - Microsoft 365 Apps

You can't create an ODBC DSN for drivers provided by Access in the Data Sources ODBC Administrator if you use an Office C2R installation.

docs.microsoft.com

According to the linked article (with a VERY recent date of 11/15/21), there is some work underway that would allow ACE to use ODBC, which would open up some pretty powerful options for data-sharing.