running sql in access 2007

[drstein]

I am new to access 2007 but I do have experience using sql in sql server 2008 r2.

In access 2007, can I write and execute sql without using the graphically tools? If so, can you tell me how to run start sql in access 2007?

 

[pbaldy]

Click on the Create tab, then Query Design. Close the Show Table box and then change the View (far left icon) to SQL. You can type SQL directly into that box.

 

[Galaxiom]

Create a query and go to the SQL view (see context menu of query designer). You can put you code straight in there. Then you save it and run the query by double clicking it in the Navigation Pane. At any time you can use the DataSheet View to show the data that will be selected by the current text of the query without saving it.

Another way is to use VBA to create a stringvariable with the SQL text and use the following command to run it:
CurrentDb.Execute stringvariable, dbFailOnError

Note there are several difference between Access SQL and TSQL. The database engine used with Access likes dates in the format #mm/dd/yyyy# instead of the single quote delimiters used in SQL Server.

The structure of nested joins in Access SQL likes a lot more parentheses. Create a query in the designer and look closely at the pattern before you start tearing out your hair wonding why it rejects what works in TSQL.

The datatypes are also a bit different. eg SQL's Integer is Long Integer in Access.

As you are experienced in SQL Server you might want to consider using it for your back ends rather than Access. You can write PassThrough queries in Access that will be run directly by the SQL Server. See context menu again.

Note that Access 2007 doesn't understand the new DateTime format in SQL Server 2008R2.