Solved

execute sql statement in vb6

Posted on 2013-11-05
4
305 Views
Last Modified: 2013-11-05
I would like to execute the following statement in vb6.  Will this code work?

esql = "update tblRegistration set NewPart = 1 Where ID in (SELECT tblRegistration.ID FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID]Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And  Total > 0 And Combo11 like '" & rec1.Fields(0) & "')"

conn.Execute (esql)
0
Comment
Question by:al4629740
  • 2
4 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 39624907
there needs to be a space here:
"tblRegistration.[ID]Where" -> "tblRegistration.[ID] Where"

Is there a field/column in your table named Combo11 ?

I suggest explicitly using the value property:
rec1.Fields(0) -> rec1.Fields(0).value

You are using a LIKE keyword in your SQL, but I do not see any pattern characters as part of the rec1.Fields(0) value you are concatenating.  If this the field does not contain wildcard characters, you will get faster performance using an equal comparison operation.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 39624909
is Combo11 a field on your form, of a field in a table in the Database?
0
 

Author Comment

by:al4629740
ID: 39625089
Thanks

Will this statement also execute it?

conn.Execute (esql)

Open in new window

0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 250 total points
ID: 39625124
yes, that statement will execute the query (give the appropriate database connection), but the statement itself may not be valid.

AW
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now