Solved: SQL Update query problem

[xCav8r]

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).

[gibbo1715]

Thanks I ll await your return later,

Gibbo

[Bob Phillips]

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?

[XL-Dennis]

Bob - it's named ADO210.chm.

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

Kind regards,
Dennis

[Bob Phillips]

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?

[XL-Dennis]

Bob - No problems, PM me Your e-mailaddress

Kind regards,
Dennis

[xCav8r]

Gibbo, could you zip up and attach your files (minus any confidential data) to a post?

[gibbo1715]

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

[sheeeng]

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.

[xCav8r]

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.

[xCav8r]

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.

[XL-Dennis]

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

[gibbo1715]

Wow, wasnt expecting so much assistance, thankyou. Only problem is im running office 2000 so dont have the reference libraries, any ideas?

Cheers

Gibbo

[XL-Dennis]

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

[xCav8r]

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

[gibbo1715]

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

[xCav8r]

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?

[gibbo1715]

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

[xCav8r]

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?

[gibbo1715]

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