Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 46

Thread: Solved: SQL Update query problem

  1. #21
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Sorry, gotta run for a few hours. At this point, I would recommend zipping up both mdb and xls for more focused attention. Hopefully someone else can help pick up where I left off. Otherwise, I'll get back to it later this afternoon (evening your time).

  2. #22
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks I ll await your return later,

    Gibbo

  3. #23
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by XL-Dennis
    BTW, I sent You a PM the other day about the ADO helpfile which I believe is the best resource available unless You want buy books.
    Which ADFO file is that Dennis?
    ____________________________________________
    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

  4. #24
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Bob - it's named ADO210.chm.

    xCav8r - Thanks for pointing it out and Gibbo is in "good hands" now

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #25
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by XL-Dennis
    Bob - it's named ADO210.chm.
    Dennis,

    I never thought of looking for a helpfile, seeing as ADO is not a 'runnable' product .

    The only thing I have on my system is ADODC98.chm, which is not much use. Any chance you could mail it to me?
    ____________________________________________
    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

  6. #26
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Bob - No problems, PM me Your e-mailaddress

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  7. #27
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Gibbo, could you zip up and attach your files (minus any confidential data) to a post?

  8. #28
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Posted what im playing with,

    I appologise for this workbook being all over the place,

    I have made a userform to try out a load of stuff around vba ado and sql and some of it aint pretty but anything you can help me with is much appreciated

    cheers

    Gibbo

  9. #29
    VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by XL-Dennis
    Gibbo,

    Here is a small SQL-guide that may get handy for You:

    http://techonthenet.com/sql/

    I agree, SQL is in the long run more difficult then the ADO-stuff. One approach You may benefit from is create queries in MS Access and view the SQL code.

    Edit: My post here may also be of interest in general terms for DB & ADO & SQL:
    http://www.ozgrid.com/forum/showthread.php?t=32246

    Kind regards,
    Dennis
    Thanks for the extra info.

  10. #30
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Wow! Pretty ambitious intro to ADO, Gibbo. At first glance, I'd say you should start working toward building your own class module for ADO operations.

    Anyway, I'll take a more careful look tomorrow and help you return the results of a query with criteria taken from a form's controls to a listbox.

  11. #31
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Sorry for the late response. I created a new attachment with references to ADO 2.8 and OWC 11. I used the spreadsheet control to display the results of the queries, because it seemed like the best control to use in the absence of the datagrid in VBA. For more about this control, check out Dennis' articles here at VBAX.

    I demonstrated several concepts. First, I populated a combo box using an array that I obtained from the northwind sample database via ADO. Second, I added a few items to the combo box that mimic Excel filter-like functionality. Third, changing a value in the combo box requeries the spreadsheet control based on the value chosen. If you really want the results of the query to populate a list box or combo box, then you should be able to adapt what I did pretty easily. If not, just let me know.

  12. #32
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    xCavr8,

    Very nice of You to take the time and set up an excellent example - thank You

    This may be of interest to develop and upload an article about it

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  13. #33
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Wow, wasnt expecting so much assistance, thankyou. Only problem is im running office 2000 so dont have the reference libraries, any ideas?

    Cheers

    Gibbo

  14. #34
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    Open the VB-editor and choose the appropiated versions of the ADO library via the command Tools | References....

    I believe that if You run Windows 2000 and Office 2000 the correct version should be 2.5. That's Microsoft ActiveX Data Object 2.5 Library.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  15. #35
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Gibbo,

    You would probably have to roll back to Microsoft Object Web Component reference as well in addition to changing to location of the northwind sample.

    Marco

  16. #36
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks for your patience

    I tried all the above but get an error on the form initalise

    "Method or Data member not found"

    I ve changed the references and pointed it at where my nwind database is located

    Any ideas

    Cheers

    Gibbo

  17. #37
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Do you have a reference to Microsoft Office Web Components x.x? Say, 10.0? It could be that they won't work with Office 2000, but you might try d/ling them at http://www.microsoft.com/downloads/d...displaylang=en.

    Assuming that your problem is with the spreadsheet control, here's a quickfix that should work...

    [VBA]
    ' Requires reference to Microsoft Office Web Components
    ' for the spreadsheet control
    Option Explicit
    Private Sub cmbFilterForCustomer_Change()
    Dim strSQL As String
    Dim strWHERE As String

    strWHERE = WhereParameter(Me.cmbFilterForCustomer.Value, "Country", , True)
    strSQL = "SELECT * FROM Customers " & strWHERE & gcstrSQLTerminus
    ActiveWorkbook.Sheets(1).Cells.CopyFromRecordset GetADORecordset(strSQL)
    'Me.sshResultsOfQuery.Cells.CopyFromRecordset GetADORecordset(strSQL)

    End Sub
    Private Sub UserForm_Initialize()
    ActiveWorkbook.Sheets(1).Cells.CopyFromRecordset GetADORecordset("SELECT * FROM Customers;")
    With Me
    '.sshResultsOfQuery.Cells.CopyFromRecordset GetADORecordset _
    ("SELECT * FROM Customers;")

    With .cmbFilterForCustomer
    .List = ADORecordsetToArray _
    ("SELECT DISTINCT Country FROM Customers", , True)
    ' User-friendly items like an Excel filter
    .AddItem gcstrFilterForAllRecords, 0
    .AddItem gcstrFilterForNull, 1
    .AddItem gcstrFilterForNotNull, 2
    End With

    End With

    End Sub
    [/VBA]

    This'll populate Sheet1 of the workbook instead. Unfortunately, without further changes to the code, it won't properly filter based on the value chosen in the combo box. Instead, it'll paste the new recordset to the top of the sheet. Anyway, although it isn't as clean, it should suffice for a demonstration.

    On a different note, it would be far easier to demonstrate the the ADO concepts using unbound forms in Access. Sticking data in a listbox is a poor alternative to using a datagrid or spreadsheet control. Do you have Access?

  18. #38
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Again

    I do have access yes (Although cant use it in my projects at my work as my users dont) and VB as well, but if there is another method that demos this better Id be really grateful to see it and then I can learn from it and adapt as necessary

    Cheers

    Gibbo

  19. #39
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Access is the easiest place to demonstrate these concepts. Its forms (and other objects) have a property called recordset which can be set to an ADO or DAO recordset. Alternatively, we could go the VB route. Do you have VB.NET?

  20. #40
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Yes i do, although only had it a few weeks so not got my head around it properly yet, also have VB 5

    I think if I could look at using vb (or VB.Net) that would be preferable to me

    Cheers

    Gibbo

Posting Permissions

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