Access 2000 ADO Execute failed

[Matt.]

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

 

[Brendan Reynolds]

It seems 'Module' is a reserved word. It worked for me after I put square
brackets around 'Module' ...

Public Sub TestAdoExecute()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Dim ClockNumber As Integer
ClockNumber = 10

Set conn = CurrentProject.Connection

' strSQL = "CREATE TABLE [Employee Training] ([Clock Number] INT,
[Module] VARCHAR(25))"
' conn.Execute strSQL

strSQL = "DROP VIEW vwEmpModules"
On Error Resume Next
conn.Execute strSQL
On Error GoTo 0

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

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

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