Solved: SQL Update query problem

[gibbo1715]

Can anyone tell me why the code below update query doesnt update my record?

I have a table called tbl_Test with field names

FieldName1
FieldName2
FieldName3


Thanks Gibbo

[VBA] Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = (stCon)

sSQL = "SELECT * From tbl_Test"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE tbl_Test " & _
" SET FieldName3 = 'None' " & _
"WHERE FieldName1 = 'Me.TextBox1.Text' AND FieldName2 = 'Me.TextBox2.Text'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From tbl_Test"
oRS.ActiveConnection.Execute sSQL
ary = oRS.GetRows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0) & ", " & ary(3, 0)
End If
oRS.Close
Set oRS = Nothing [/VBA]

[alimcpill]

Unless I've misunderstood, I fancy the update SQL should look like this:
[vba]
sSQL = "UPDATE tbl_Test " & _
" SET FieldName3 = 'None' " & _
"WHERE FieldName1 = '" & Me.TextBox1.Text & "' AND FieldName2 = '" & Me.TextBox2.Text & "'"
oRS.ActiveConnection.Execute sSQL
[/vba]

In other words, at the moment you're only updating the table where the actual value of FieldName1 is literally "Me.TextBox1.Text", rather than the value of Me.TextBox1. You missed some of the concatenation, easy mistake to make!!

[gibbo1715]

excellent thanks

I ve only just started looking at SQL so appreciate all the help i can get at the moment

Can anyone suggest a good place to look to learn more please

cheers

Gibbo

[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

[gibbo1715]

Thanks Dennis,

Loads to learn so appreciate all the help I can get with this

Gibbo

[gibbo1715]

Quick Question Dennis

Is there an easy way to use the sql as generated by access

i.e
[VBA] sSQL = SELECT [tbl_Test].[ID], [tbl_Test].[FieldName1], [tbl_Test].[FieldName2], _
[tbl_Test].[FieldName3], [tbl_Test].[FieldName4], [tbl_Test].[FieldName5]"
FROM tbl_Test _
WHERE ((([tbl_Test].[FieldName3])="None")); [/VBA]

Or do i have to convert it all to the syntax you ve already shown me?

Cheers

Gibbo

[XL-Dennis]

Gibbo,

As a beginner You can accept the generated syntax especially if You work with Access DB. However, as You notice it generate some overflow (in my opinion) You can later use:

[vba]
SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
FROM tbl_Test WHERE FieldName3="None";
[/vba]

But I'm convinced that other members here who have better knowledge of SQL can guide You better

Kind regards,
Dennis

[gibbo1715]

Thanks

Having generated my query then how do i then view the results?

I also assume id have to amend the above slightly to

[VBA]sSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, _
FieldName5 FROM tbl_Test WHERE FieldName3= '" & None & "';"
[/VBA]


Gibbo

[XL-Dennis]

Gibbo,

Yes, that's correct.

Having generated my query then how do i then view the results?

I'm not sure I understand Your question but if I'm correct then I assume You're in the Query -window and then You can right click and choose SQL from the popup menu.

Kind regards,
Dennis

[gibbo1715]

Sorry Dennis

Im accessing my database from a userform within excel so I need to return the results somehow to a userform within excel

A listbox on a userform within excel for example

Cheers

Gibbo

[XL-Dennis]

Gibbo,

OK, now I'm with You

And yet another link to another good friend's (aka Mark007) site where another good friend Will (aka WillR) have made an example available about populating a Userform ListBox with ADO:

http://www.thecodenet.com/articles.php?id=24

Kind regards,
Dennis

[gibbo1715]

Thanks Dennis

That answers a lot of questions for me, Im had wondered about populating an array so I can use the indiviual columns to populate text boxes etc

I assume the following will work but i ll check it later

Me.TextBox1.Value = vaData(0, 0)

Me.TextBox2.Value = vaData(1, 0)

Me.TextBox3.Value = vaData(2, 0)

and so on


thanks again

If you have other places I can research around ADO and SQL please let me know

gibbo

[XL-Dennis]

Gibbo,

Instead of answer I suggest that You try it out and study the outcome. If You got any questions You're most welcome

How serious are You when it comes to ADO / SQL? There exist some good books but they are usually expensive. Except for that they target different DB - platforms (SQL Server, Oracle, DB2 etc) and Access is not usually covered so well.

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.

Kind regards,
Dennis

[gibbo1715]

sorry dennis did mean to reply and that is very useful yes, this is definately a way forward for me at my work as it resolves a lot of issues for me

I will continue to play and learn

Thanks for your time

Gibbo

[XL-Dennis]

Gibbo - Enjoy the weekend and let us know when You got some questions

Kind regards,
Dennis

[xCav8r]

Gibbo,

I also assume id have to amend the above slightly to
[VBA]sSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, _
FieldName5 FROM tbl_Test WHERE FieldName3= '" & None & "';"
[/VBA]

Actually, I think Dennis overlooked or misunderstood what you were asking. What he wrote in the post to which you're responding in the above quotation is SQL and not VBA--despite his having had enclosed the SQL with VBA tags for the forum. To clear things up, the following is SQL:

 SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
FROM tbl_Test WHERE FieldName3='None';

The following is an example of an SQL statement incorporated into VBA:

[VBA]

Sub DemoSQL()

Dim strSQL As String
strSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, " _
& "FieldName5" & vbNewLine _
& "FROM tbl_Test WHERE FieldName3='" & Me.TextBox1.Value & "';"
Debug.Print strSQL
End Sub
[/VBA]
Results:

SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
FROM tbl_Test WHERE FieldName3='SampleData';

I suggest using helper functions to enclose criteria with quotation marks and pound signs (#) instead of writing them manually. It's not that they're difficult to write, but they are easy to overlook, so helper functions, no matter how simple, reduce errors.

[gibbo1715]

Thanks for clarifying, Im still a bit confused though, I ve added your amended SQL to my code but whenever i click the button I always only get the first record in the dataset

Any ideas what im doing wrong please

Cheers

Gibbo

[VBA] Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim sConnect As String
Dim sSQL As String
Dim vaData As Variant

On Error GoTo Err:
sConnect = (stCon)
sSQL = "SELECT * From tbl_Test"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
' Check to make sure received data.
If rs.EOF Then
MsgBox "No records returned.", vbCritical
Else
strSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, " _
& "FieldName5" & vbNewLine _
& "FROM tbl_Test WHERE FieldName3='" & Me.TextBox1.Value & "';"
rs.ActiveConnection.Execute sSQL

With rs
vaData = .GetRows
End With

'Populate the TextBoxes.

Me.TextBox1.Value = vaData(1, 0)
Me.TextBox2.Value = vaData(2, 0)
Me.TextBox3.Value = vaData(3, 0)
Me.TextBox4.Value = vaData(4, 0)
Me.TextBox5.Value = vaData(5, 0)
End If
rs.Close
Set rs = Nothing
Exit Sub
Err:
Call ErrorLog(Err.Description, Err.Number, Me.Name)[/VBA]

[xCav8r]

The sample query returns only those records where the field named "FieldName3" has a value of whatever is in the textbox called "TextBox1". In other words, it selects those rows that meet that criterion, returning the columns specified.

Is it possible that you only have one row where the value in FieldName3 is the same as the value in TextBox1? (Edit: just noticed that you're executing with sSQL instead of the strSQL that you copied from my post. You must only have one row in the table. Or you probably need to set the rows to return in the GetRows method. I usually do a workspace & Jet instead of ADO, so I don't remember and would need to mock it up on my end to tell you something more definitive.)

This may be more along the lines of what you're looking for...

[VBA] Sub PopulateTextBoxes()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Dim sConnect As String
Dim sSQL As String
Dim vaData As Variant

On Error GoTo PopulateTextBoxes_Error

sConnect = (stCon)
sSQL = "SELECT * From tbl_Test"

Set rs = CreateObject("ADODB.Recordset")
rs.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If rs.EOF = False Then
vaData = rs.GetRows
Me.TextBox1.Value = vaData(1, 0)
Me.TextBox2.Value = vaData(2, 0)
Me.TextBox3.Value = vaData(3, 0)
Me.TextBox4.Value = vaData(4, 0)
Me.TextBox5.Value = vaData(5, 0)
End If
PopulateTextBoxes_Exit:
rs.Close
Set rs = Nothing
Exit Sub
PopulateTextBoxes_Error:
Select Case Err.Number
'Case
Case Else
Call ErrorLog(Err.Description, Err.Number, Me.Name)
End Select
Resume PopulateTextBoxes_Exit
End Sub
[/VBA]

[gibbo1715]

Thanks

Sorry but I am actually trying to find a unique record and fill the textboxes with the values for that record

How do I do that

Thanks

Gibbo

[gibbo1715]

Think I ll post the others I ve been working on incase they are of use to others, these populate a listbox or a combobox on a userform (Mine is in Excel)

[VBA] Sub Populate_Listbox()
Dim cnADO As ADODB.Connection
Dim rstADO As ADODB.Recordset
Dim strCon As String, strSQL As String
Dim vaData As Variant
Dim lCols As Long

'Set the ADO connection
Set cnADO = New ADODB.Connection

'Specify the connection string.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _
& "C:\Gibbos.mdb;Persist Security Info=False"
'Specify your SQL statement.

strSQL = "SELECT * FROM tbl_Test"

With cnADO
.CursorLocation = adUseClient
'Client-side cursor location (as opposed to server-side)
'required as we are going to disconnect the recordset
'in order to populate the listbox.
.Open strCon 'Open the connection.
'execute the SQL statement.
Set rstADO = .Execute(strSQL)
End With

With rstADO
Set .ActiveConnection = Nothing
'Disconnect recordset.
lCols = .Fields.Count
'Populate a variant array with the recordset.
vaData = .GetRows
End With
'Close the connection.
cnADO.Close

'Populate the Listbox.
With UserForm1
With .ListBox1
.Clear
.ColumnCount = lCols
.BoundColumn = lCols
.List = Application.Transpose(vaData)
.ListIndex = -1
End With
End With

'Release objects from memory.
Set rstADO = Nothing
Set cnADO = Nothing

End Sub
Sub Populate_Combobox()
Dim cnADO As ADODB.Connection
Dim rstADO As ADODB.Recordset
Dim strCon As String, strSQL As String
Dim vaData As Variant
Dim lCols As Long
Dim x As Long

'Set the ADO connection
Set cnADO = New ADODB.Connection

'Specify the connection string.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _
& "C:\Gibbos.mdb;Persist Security Info=False"

'Specify your SQL statement.
strSQL = "SELECT * FROM tbl_Test"

With cnADO
.CursorLocation = adUseClient
'Client-side cursor location (as opposed to server-side)
'required as we are going to disconnect the recordset
'in order to populate the listbox.
.Open strCon 'Open the connection.
'execute the SQL statement.
Set rstADO = .Execute(strSQL)
End With

With rstADO
Set .ActiveConnection = Nothing
'Disconnect recordset.
lCols = .Fields.Count
'Populate a variant array with the recordset.
vaData = .GetRows
End With

'Close the connection.
cnADO.Close
'Populate the Listbox.
With UserForm1
With .ComboBox1
.Clear
.ColumnCount = 2
.List = Application.Transpose(vaData)
.ListIndex = 0
End With
End With

'Release objects from memory.
Set rstADO = Nothing
Set cnADO = Nothing

End Sub [/VBA]