By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

Access 2000 ADO execution failing

P: n/a
Hi all!

The code below fails on the first Execute. Can somebody please tell me what
I'm doing wrong? I'm going to base the results in the table
TrainingModulesNotTaken for a report, otherwise I'd use recordsets for all
this.

cheers,
Matt.
>>>>>>>>>>>>>>>>>>>>>>>>.

Public Sub ModuleNotTakenA(ClockNumber As Integer)
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "Create View vwEmpModules AS " _
& "Select Distinct [Clock Number], Module From [Employee Training] " _
& "WHERE [Clock Number] = " & CStr(ClockNumber) & ";"

Set conn = CurrentProject.Connection

conn.Execute strSQL

strSQL = "Delete * " _
& "From TrainingModulesNotTaken ;"

conn.Execute strSQL

strSQL = "Insert Into TrainingModulesNotTaken (Module, Description, [Clock
Number]) " _
& "Select Module, Description, " & ClockNumber & " " _
& "From Module " _
& "Where Module NOT IN (SELECT Module FROM vwEmpModules);"

conn.Execute strSQL
Set rs = Nothing
Set conn = Nothing

End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Matt." <ma*******************@hotmail.com> wrote in message
news:I5*********************@news20.bellglobal.com ...
Hi all!

The code below fails on the first Execute. Can somebody please tell me what I'm doing wrong? I'm going to base the results in the table
TrainingModulesNotTaken for a report, otherwise I'd use recordsets for all
this.

cheers,
Matt.
>>>>>>>>>>>>>>>>>>>>>>>>>.

Public Sub ModuleNotTakenA(ClockNumber As Integer)
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "Create View vwEmpModules AS " _
& "Select Distinct [Clock Number], Module From [Employee Training] " _
& "WHERE [Clock Number] = " & CStr(ClockNumber) & ";"

Set conn = CurrentProject.Connection

conn.Execute strSQL

strSQL = "Delete * " _
& "From TrainingModulesNotTaken ;"

conn.Execute strSQL

strSQL = "Insert Into TrainingModulesNotTaken (Module, Description, [Clock
Number]) " _
& "Select Module, Description, " & ClockNumber & " " _
& "From Module " _
& "Where Module NOT IN (SELECT Module FROM vwEmpModules);"

conn.Execute strSQL
Set rs = Nothing
Set conn = Nothing

End Sub

Your WHERE clause is asking for a string variable, but does not include
single quotes:

conn.Execute "CREATE VIEW vwEmpModules AS " & _
"SELECT DISTINCT [Clock Number], Module FROM [Employee Training] " & _
"WHERE [Clock Number] = '" & CStr(ClockNumber) & "'"

As written, your second SQL will also fail, since you have used a varaible
ClockNumber in place of the field name Clock Number:

conn.Execute "INSERT INTO TrainingModulesNotTaken " & _
"(Module, Description, [Clock Number]) " & _
"SELECT Module, Description, [Clock Number]" & _
"FROM Module " & _
"WHERE Module NOT IN (SELECT Module FROM vwEmpModules)"
It also appears that you have used Module as both a field and a table, which
will assuredly confuse the little SQL guy inside Access.


Darryl Kerkeslager

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.