What is JET SQL ?

[neillovell]

What's this JET SQL thing, and how is it different from SQL ?

 

[scriverb]

JET SQL is the ACCESS SQL language that is used by the ACCESS JET Database engine. It differs from MS SQL, TERADATA SQL and many others in differences in syntax. Each SQL server software has its own nuances and differences between each other and ACCESS. You can view the JET SQL by looking at the SQL screen behind a query in your ACCESS database. This SQL can be modified by hand or you can use the Design Screen. The SQL can either be compiled in a Query and run with VBA code with ACCESS such as:

docmd.openquery "qryQueryName"

Or you can take the same SQL code and execute it with a command call such as

db.execute "Select name, address FROM tblYourTable;"

The important difference to remember that it executes in the JET database engine which is on your PC. If you link to an ORACLE or MS SQL server that has millions of records and you try to run a query to select records from a table ACCESS will attempt to run the query on your PC where memory and disk storage will not be enough. Also, the slowness of the process will be prohibitiive. You can create a pass-through query in ACCESS which is just a query that is setup to be sent to the SQL server and run with the power of the server database and only return the selected records. The SQL code in that query will have to be in the format of the SQL server NOT ACCESS SQL.

You can take in as input to an ACCESS query a pass-through query and have the ACCESS query use this recordset of records to create a new table within access. This would be a make-table query using a pass-through to access the server.

I probably have gone too far in my explaination so I will stop here. If you have any more questions please reply back with more questions. Bob Scriver

 

[neillovell]

Thanks for taking the time and trouble to answer my question, much appreciated!