Cracking the ice

[XLGibbs]

What? Nobody needs SQL related help here yet!

Sheesh. Malik and I will have no fun then.

[TrippyTom]

Ok, how about talking about how you personally use SQL? That might get the ball rolling.

I know it sounds naive, but I thought SQL was for database applications, and since I don't have MS Accesss at work, it was never an option for me (although I am curious to see how powerful it could be).

I haven't been curious enough to go out and buy a book on it yet. :)

[malik641]

Sheesh. Malik and I will have no fun then. Werd

Personally, I consider myself a novice in the SQL language. But for some reason it just really interests me. So I bought two books on it: a beginner book, and an advanced book. Which are:

Beginning SQL (http://www.amazon.com/Beginning-SQL-Programmer-Paul-Wilton/dp/0764577328/ref=pd_bbs_sr_1/105-6740498-5531613?ie=UTF8&s=books&qid=1188525349&sr=1-1)
SQL Server 2005 Unleashed (http://www.amazon.com/Microsoft-SQL-Server-2005-Unleashed/dp/0672328240/ref=pd_bbs_sr_1/105-6740498-5531613?ie=UTF8&s=books&qid=1188525358&sr=1-1)

They are both written very well (especially the Unleashed book...I mean, I turned to like 1300th page and just started reading about clustered indices and I understood what they were talking about fairly well).


I use SQL at my job about 1/4 of the time. We have a SQL Server 2000 back-end and an Access front-end. It was created by a third party and we have no on-site DBA at our job...so any type of special UPDATE query or SELECT query or INSERT INTO etc. that doesn't exist in the functionality of our database system I usually can come up with something. And the more I read articles on tuning SELECT queries and such, the more I understand why some of my queries were so slow :) (like I said...still novice here, but learning strong!).

If somebody needs a special report, I'll develop it in Access because the users can just enter some beginning dates and end dates (for example) and run the query based on that (which is a custom SQL written query).

If it's a one-time query, I'll do it in SQL Server Management Studio Express and save the query for future reference (and to see how bad it was when I become more advanced ;)). My ultimate goal at my job is to create a VB.NET app that will build the SQL queries that the Engineering department will use to make common database revisions (since the functionality doesn't exist in the database). "Why not just have the third party add the functionality?" you ask? Well, I don't think my company sees it as a high priority. And if I can create something for them, then why not pay less for the same thing?

Beyond that, I'm trying to understand the MSDN Transact-SQL forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1) threads :)



Hope that gets the ball rollin'.

[Bob Phillips]

Ok, how about talking about how you personally use SQL? That might get the ball rolling.

I know it sounds naive, but I thought SQL was for database applications, and since I don't have MS Accesss at work, it was never an option for me (although I am curious to see how powerful it could be).

I haven't been curious enough to go out and buy a book on it yet. :)

TT, you can EVEN use SQL to query an Excel workbook!

[TrippyTom]

XLD, omg! I just became more interested in it now that I know I can use it at work. Do I have to get some 3rd party code editor to use it?

[Bob Phillips]

Nope, you already have it.

Here is a very simple example that reads a workbook sheet



Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Test\SQL Reading Excel.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub


It gets more interesting if you have a nicely structured table with headings, then you can query based on a condition.



Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Test\SQL Reading Excel.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$A1:C12] WHERE Region='South'"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub


and even better if the table is a Excel defined name, you can query that just like a database



Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Test\SQL Reading Excel.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM StateData WHERE Region='South'"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub


Hope that I have whetted the appetite.

[TrippyTom]

I tried using the first example in a file and ran it, but I got an error window with a red X in it with a blank message. Do I have to set up some references to a library or something for it to work? I renamed a phone list I had to the same path/file specified in the code.

[XLGibbs]

The extended properties would have to reference the correct version of Excel perhaps.

As far as your question, I use SQL for a ton of things, mostly to automate reporting from SQL database to Excel templates. I also use it within C# and VB projects to manage various tasks, including executing stored procedures etc.

It is quite handy!

[Bob Phillips]

No you shouldn't as I used late binding.

I forgot to include teh constants in the first example, those in exaples #2 and #3. Put those in as well.

Other than that, step through it and find where it errors.

[lucas]

Bob can you explain what the South in this line is doing. I had to remove it to get it to work. I used StateData for the defined name in the data file.
sSQL = "SELECT * FROM StateData WHERE Region='South'"


This also seems to handle the data file differently...with some restrictions unless I'm doing something wrong. I seem to be required to have numbers in column A and no numbers in any other column...could you clear up what I am obviously missing?

I got all three to work. My two files attached. One with the macro's and one with the data.

I might as well learn something new too.

[Bob Phillips]

Steve,

My example was based upon a 3 column table, the first being a state name, the second being a region, the third being an amount.

For instance, I had something like

State Population Region
Alabama 4,447 South
Alaska 627 West
Arizona 5,131 West
Arkansas 2,673 Mid-west
California 33,872 West
Colorado 4,301 West
Connecticut 3,406 East
Delaware 784 East
Florida 15,982 South
Georgia 8,186 South
Hawaii 1,212 West
Idaho 1,294 West
Illinois 12,419 Mid-west
Indiana 6,080 Mid-west
Iowa 2,926 Mid-west
Kansas 2,688 West
Kentucky 4,042 South
Louisiana 4,469 South
Maine 1,275 East
Maryland 5,296 East
Massachusetts 6,349 East
Michigan 9,938 Mid-west
Minnesota 4,919 Mid-west
Mississippi 2,845 South
Missouri 5,595 South
Montana 902 West
Nebraska 1,711 West
Nevada 1,998 West
New Hampshire 1,236 East
New Jersey 8,414 East
New Mexico 1,819 West
New York 18,976 East
North Carolina 8,049 South
North Dakota 642 West
Ohio 11,353 Mid-west
Oklahoma 3,451 Mid-west
Oregon 3,421 West
Pennsylvania 12,281 East
Rhode Island 1,048 East
South Carolina 4,012 South
South Dakota 755 West
Tennessee 5,689 South
Texas 20,852 South
Utah 2,233 West
Vermont 609 East
Virginia 7,079 South
Washington 5,894 West
West Virginia 1,808 East
Wisconsin 5,364 Mid-west
Wyoming 494 West

and the query was pulling back all rows where the region was South.

[lucas]

Ok Bob..got that working. Still doen't retieve the poplulation though...?
will post back if I figure out what I'm doing wrong.

[Bob Phillips]

Steve,

The other thing that you should do IMO is to put the ADO code in a class module, and just expose those properties and methods that you would want, and hide all of the more obscure stuff from the main coding.

For instance, you would have a querystring as a property, and a Run method which might return a recordset, but would probably be better returning an array. The mainline code would then determine what the query was, set the QueryString property, and also handle the array. So you mainline code would then be somthing as simple as



Set myQuery = New clsQuery

With myQuery

.File = "f:\Temp\SQL Reading Excel.xls"
.QuerySTring = "SELECT * FROM StateData WHERE Region='South'"
myArray = .Run
End With


Of course you still have to process myArray, but that has to be done anyway.

This can be made as complex or as simple as you wish, for instance you could have a data source property and allow queries against Excel workbooks, Access, SQL-Server, etc., add a write method, etc., etc. The beauty is that you can develop and test the class in isolation, get it working nicely with a clearly defined interface, then use it anywhere you want.

[lucas]

I can definitly see the advantage of the class file. sql seems very fast.

[Bob Phillips]

And it leaves the workbook closed.

With Access or SQL_server, you can a get multi-user Excel application, something only the foolhardy try with Excel.

[Gert Jan]

My boss found out that i know a little bit about vba, and so he thought it a good idea to have me in another department.
My knowledge of vba is all from just reading here on VBAX. I've spent many ours of reading posts and the KB.
Since the beginning of this week i started working with Hyperion (=Brio?) to get information from our database.
For now its just drag and drop, but i hope to learn SQL quickly.

So, i for sure will be a regular guest here in this forum.

I started with Bob's example,
Option Explicit
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "C:\Documents and Settings\Gert Jan\Mijn documenten\ _
SQL pogingen\Test1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Blad1$A1:C51]WHERE Region='South'"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveSheet.Range("A2").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub

There's a question about that, can you create a column header in there and how?
So, not just the data but pute "State", "Population" and "Region above it

Gert Jan

[lucas]

Hi Gert,
Is it returning the population data for you? I cannot get it to retrieve numbers in any column other than column A

[Gert Jan]

Hi Steve,
this is what i get when running thecode in my above post.

6662

[lucas]

could you possibly post your database file so I could take a look at it please.

[Gert Jan]

Well, "database", it's just the list Bob gave. i copied it into a workbook.
But here it is...

6663

[Bob Phillips]

Here you are Gert Jan. I have had to change the filenames and I have used a table name




Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long

sFilename = "C:\Test\SQL Reading Excel.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM StateData WHERE Region='South'"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
For i = 1 To oRS.fields.Count
ActiveSheet.Cells(1, i).Value = oRS.fields(i - 1).Name
Next i
ActiveSheet.Range("A2").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub

[Bob Phillips]

Since the beginning of this week i started working with Hyperion (=Brio?) to get information from our database.

Brio is my all time favourite app, but I haven't used the version since taken over by Hyperion (yes I know it was years ago). I still use a version from 2002, and it is absolutely great, so handy, it pivots and charts in a way Excel can only dream about.

[lucas]

That's it Gert and it works fine. I wonder what the difference was in this file and the one I used. I had not changed the formatting and yours is set to General.

To use thisworkbook path I am using this:
sFilename = ThisWorkbook.Path & "\SQL Reading Excel.xls"

[Gert Jan]

Works perfect Bob, thanks

Steve, I copied it into a texteditor, replaced the spaces in the State-names with a underscore and removed
the comma's (we use it for decimals over here) and finally imported it into Excel.

[Gert Jan]

Another question Bob,

Is it correct that the workbook, on wich one performs a query, always must have the data in a named range?

[Bob Phillips]

No, you can query the whole sheet, or a range within the sheet (your example was the latter).

A named range is just more flexible because it then acts like a table in a DB, and you can refer to clumn names.

[joprotus]

Hi Guys, sample codes here are great. How about executing a query command records with the same macro file and not from the external file, is it possible?

Thanks.

[Gert Jan]

Hi joprotus,

I'm not sure what you mean, but it seems that you're looking for the autofilter?

Gert Jan

[Gert Jan]

Steve,
If you're still here...
Yesterday i tried running the code on a file at my work. I kept on getting a message that the file was an unexpected format.
It took me a while before i realized that it was an excel generated by Hyperion.
When i copied it, and pasted values into a new book, the problem was solved.
Maybe something simular to what you experienced?

Gert Jan

[lucas]

I think you're right Gert, I have worked it out for now too....until something new comes along to throw a spanner in the works.

[joprotus]

Hi Gert Jan,

I have two worksheets in a single workbook. The first worksheet has a Sales Order Header Database and the second has the SO Details. I am doing a macro program in the same file and I want to know if it's possible to execute a join query command linking the two sets of records together.

Thanks,
Joprotus

[YellowLabPro]

(especially the Unleashed book...I mean, I turned to like 1300th page and just started reading about clustered indices and I understood what they were talking about fairly well)

Ok boy genius..... that is like Bob casually whipping out a little Latin to go w/ that burger and fries..... :rofl:

Indices, clusters, SQL... HUH????????

Cheers mate, and great job getting this forum going here!

[malik641]

Ok boy genius..... that is like Bob casually whipping out a little Latin to go w/ that burger and fries..... :rofl:
I can't compare with Bob. That guy is on a-whole-nother level that I hope to achieve one day :)

I mean, I could whip out my spanish skillz, but then I would just look like a poseaur.


Cheers mate, and great job getting this forum going here!
Thanks Doug.
I'm just glad there are people here who are as excited about the new forum as I am.

[Bob Phillips]

Thanks Doug.
I'm just glad there are people here who are as excited about the new forum as I am.

It has the potential to be a great forum Joseph, one in which people can really extend; you are to be commended for your initiative.

[malik641]

It has the potential to be a great forum Joseph, one in which people can really extend; you are to be commended for your initiative. Thanks, Bob. That means a lot to me :)