One of the best little know useful tools out there in accessing your databases. The microsoft datbase access query is a truly easy way to access sql databases and other databases.
I use this to create end user reports. It is a fast and efficient way to get into a database and return a result set if you want it to have a pretty front end without a lot of effort.
Okay so how do you do it? I will explain in Excel 2002 but i will work for most versions.
First open a blank workbook. Second select Data --> Import External Data --> New database query from the menu tab.
Select (New datasource) from the window, if you are familar with odbc or SQL in general creating a connection will be a breeze. For example give your datasource a name. I try to give each datasource some specific details in the description in case I want to reuse it for other queries later on. for this example i would use Employeedatabaseondevserver
Then pick a driver from the drop down menu, for this example I am using SQL Server
then click "connect" in the dialog window type the name of your sql server instance. in this example my sqlserver and instance is burkular\sqlburkular, I would recommend you specify a login id and password, using trusted connection is okay but if you want others to use it they must have the same credentials as you. so i usually use a local sql server username and password that has very little access (ie read permissions and execute permissions to specific procedures only within specific databasses) . Then click options here you can specific a specific database or leave the default. If you are going to create multiple reports off the same datasource then you just need to qualify the database name in your query explicitly. I recommend this it is easier. Once connected "master" or your default database will appear. In step 4 select a default table, leave this blank, it is not necessary. Then it is up to you and your security practices as to whether or not you want to save username and password, I tend to leave this unchecked, yes you will be prompted, but it is safer if you don't want others to be able to run the report without pluging in specific credentials each time. If you don't care about the security then save the username and password, but remember others can see this in the code. Then click "Ok" and then "Ok" again. A dialog window will pop up that will expose access to available database items from the default database. choose the first one that pops up or a table that you know is really small, don't worry you will be getting rid of it soon enough from your query. I have not figured out a way to bypass this yet, so just do it. Once you click the table the available columns will show up in the right pane, click next, next, next, you should be at a window that is titled "Query Wizard - Finish" Click view data or edit query in Microsoft Query then hit finish. A query dialog screen will invoke showing your default table and a query editor screen. This is the money location....
From the menu click on "SQL" an SQL statement window will pop up. Now if you made to here then you can pretty much do everything you need to. Clear out the existing select statement, replace it with whatever select you would like.
Here is an example if you have access to pubs database and you did not drop the authors table.
Select * from pubs.dbo.authors (ensure you specify the database and the schema)
then hit execute.
Voila data is returned
1. Yes you can have mulitple table joins
2. You can copy your code from query analyzer into this window
3. Yes you can use parameters.
4. Yes you can set parameters up in your excel spreadsheet and use these to return specific results from your query.
5. No you don't have to fully qualify your tablename.column names each time.
6. You are limited to 65536 rows of data being returned in any excel version earlier than 2007
7. Yes you can re-query your data by setting up conditions on the access query.
8. Yes you can use procedures... this is a bonus.
9. Yes you can access views.
10.. Want to learn some of these options follow my upcoming blogs
