Newbie: Access SQL and SQL and MS SQL

[Guest]

So, I've been working hard at learning Microsoft Access and have made pretty
nifty tools to help do my job better. I've started writting some VBA and
custom functions. I want to make one of my queries a little more dynamic,
edit some of the filter without actually going into the 'design query' view.
So, of course I started using the DoCmd.RunSQL - made sense, worked for a
bunch of simple SQL stuff. I got this error saying my query (which I copied
from SQL View) isn't valid... so... I popped into VS2005 and started to
rebuild the SQL query with its fancy GUI interface... they're not the same at
all. So, I'm guessing its safe to assume that Access SQL isn't the same as
'real' SQL - yes?

The thing is, my Access queries have several calculated fields, using custom
functions I wrote in VBA... can SQL call VBA functions in an application? Is
there a method? Or am I SOL?

Thanks!
Mike

 

[Granny Spitz via AccessMonster.com]

they're not the same at
all. So, I'm guessing its safe to assume that Access SQL isn't the same as
'real' SQL - yes?

Each database engine has its own dialect (flavor) of standard SQL. Access
uses Jet SQL, SQL Server uses Transact-SQL, Oracle uses Oracle SQL, PL/SQL
and SQL Plus. It can be confusing when you jump from one to another, because
the SQL for any dialect that strays from standard SQL won't work in another
database engine.

The thing is, my Access queries have several calculated fields, using custom
functions I wrote in VBA... can SQL call VBA functions in an application?

No. You can write custom functions for SQL Server 2000 and newer with T-SQL,
not VBA. Only Jet SQL can call VBA functions in an Access database.

 

[Brendan Reynolds]

The thing is, my Access queries have several calculated fields, using
custom
functions I wrote in VBA... can SQL call VBA functions in an application?
Is
there a method? Or am I SOL?

<snip>

It depends. You can call your custom VBA functions from JET queries executed
within Access, but that will not work if those queries are executed outside
of Access, for example if you were to attempt to execute them from a VB app
or an ASP or ASP.NET Web page.

Your references to 'MS SQL' and VS.NET leave some doubt as to just what type
of application you're developing. If it is an Access application using a JET
(MDB) database, then you can use your custom VBA functions in your queries.
But if it is a VS.NET application using a SQL Server database you will not
be able to use them.

(I understand that SQL Server 2005 now hosts the .NET CLR, which means that
you can write stored procedures in VB.NET or C# rather than T-SQL. I have
not used that feature, though.)

 

[Guest]

Cool, thanks for the info!

Each database engine has its own dialect (flavor) of standard SQL. Access
uses Jet SQL, SQL Server uses Transact-SQL, Oracle uses Oracle SQL, PL/SQL
and SQL Plus. It can be confusing when you jump from one to another, because
the SQL for any dialect that strays from standard SQL won't work in another
database engine.


No. You can write custom functions for SQL Server 2000 and newer with T-SQL,
not VBA. Only Jet SQL can call VBA functions in an Access database.

 

[Granny Spitz via AccessMonster.com]

Cool, thanks for the info!

You're welcome, Mike. Good luck to you on learning SQL. It's a very
powerful language.