Solved

execute sql statement in vb6

Posted on 2013-11-05
4
308 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

685 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