Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 35

Thread: Cracking the ice

  1. #1
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location

    Cracking the ice

    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!




  2. #2
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    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

  3. #3
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by XLGibbs
    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
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    Quote Originally Posted by 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.
    TT, you can EVEN use SQL to query an Excel workbook!
    ____________________________________________
    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

  5. #5
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    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

  7. #7
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    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

  8. #8
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    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!




  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    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

  10. #10
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    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

  12. #12
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    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

  14. #14
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    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

  16. #16
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    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.

  17. #17
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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

  18. #18
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    Hi Steve,
    this is what i get when running thecode in my above post.

    Attachment 6662

  19. #19
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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

  20. #20
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    Well, "database", it's just the list Bob gave. i copied it into a workbook.
    But here it is...

    Attachment 6663

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •