Access 2000 ADO Execute failed

  • Thread starter Thread starter Matt.
  • Start date Start date
M

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
 
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.


Matt. said:
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
Click to expand...
 
Back
Top