What? Nobody needs SQL related help here yet!
Sheesh. Malik and I will have no fun then.
What? Nobody needs SQL related help here yet!
Sheesh. Malik and I will have no fun then.
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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.
Office 2010, Windows 7
goal: to learn the most efficient way
Originally Posted by XLGibbs
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
SQL Server 2005 Unleashed
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 threads
Hope that gets the ball rollin'.
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
TT, you can EVEN use SQL to query an Excel workbook!Originally Posted by TrippyTom
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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?
Office 2010, Windows 7
goal: to learn the most efficient way
Nope, you already have it.
Here is a very simple example that reads a workbook sheet
[vba]
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
[/vba]
It gets more interesting if you have a nicely structured table with headings, then you can query based on a condition.
[vba]
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
[/vba]
and even better if the table is a Excel defined name, you can query that just like a database
[vba]
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
[/vba]
Hope that I have whetted the appetite.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Office 2010, Windows 7
goal: to learn the most efficient way
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!
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
[VBA]sSQL = "SELECT * FROM StateData WHERE Region='South'"
[/VBA]
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Ok Bob..got that working. Still doen't retieve the poplulation though...?
will post back if I figure out what I'm doing wrong.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
[vba]
Set myQuery = New clsQuery
With myQuery
.File = "f:\Temp\SQL Reading Excel.xls"
.QuerySTring = "SELECT * FROM StateData WHERE Region='South'"
myArray = .Run
End With
[/vba]
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I can definitly see the advantage of the class file. sql seems very fast.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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,
[vba]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[/vba]
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
Last edited by Gert Jan; 09-01-2007 at 10:05 AM.
Hi Gert,
Is it returning the population data for you? I cannot get it to retrieve numbers in any column other than column A
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hi Steve,
this is what i get when running thecode in my above post.
Attachment 6662
could you possibly post your database file so I could take a look at it please.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Well, "database", it's just the list Bob gave. i copied it into a workbook.
But here it is...
Attachment 6663