Access Run Time error '-2147217900'

[AunN]

Dear Programmers!!

I have a scenario in which there is many to many relationship between customer and movie tables. But there is intermediate table that is transaction. Customer is having fields (CustomerId primary key,NAME,ADDRESS) and Movie is having fields (MovieId primary key,TITLE,DIRECTOR) while Transaction is having fields (TrxNumber primary key,CustomerID,MovieID). Now i created a form in design view to insert record into Transaction Table. I used the following code in VBA:

Dim T As Integer
Dim C As Integer
Dim M As Integer
Dim conn As ADODB.Connection
Dim myCommand As ADODB.Command
T = TNumber.Value
C = Customer.Value
M = Movie.Value
Set conn = CurrentProject.Connection
Set myCommand = New ADODB.Command
myCommand.ActiveConnection = conn
myCommand.CommandText = "INSERT INTO Transaction([TrxNumber],[CustomerID],[MovieID]) VALUES(" & T & ", " & C & ", " & M & ")"
myCommand.Execute

But it gives me error "Access Run Time error '-2147217900' Syntax error in INSERT INTO statement.

Please help me out!

 

[JANR]

Might be because Transaction is a SQL reserved word, try wrapping it in square brackets.

myCommand.CommandText = "INSERT INTO [Transaction] ([TrxNumber],[CustomerID],[MovieID]) VALUES (" & T & ", " & C & ", " & M & ")"

also sql is picky about spaces around keywords.

JR

 

[AunN]

Thanku so much!!

 

[Millah]

If you are going to use reserved words as names for fields or tables you might want to consider throwing a T_ in front of the table names.