Syntax error in INSERT INTO string



I have moderate experience with VB in its various incarnations, a little experience with Access, and less with SQL. The syntax error I am getting here has me baffled.
I am trying write these eight variables to a table whose name is also a variable. The CREATE TABLE process works fine with the variable table name.
It's when I try to insert the data into the table that the fun begins.
I suppose it's something simple . . . but I have tried all the references I can find and no luck so far. Can someone help please?
(Using Access 2002, and vb 6.3)
=== Variables =====================  
Dim iClient As Integer
Dim iInstruct As Integer
Dim sActivity As String
Dim sACType As String
Dim sRego As String
Dim sDate As Date
Dim gDuration As Single
Dim sNotes As String
Dim sSQL As String
====================================
 
value for sTableName is passed to the subroutine 
other variables are loaded from text boxes and combo boxes on the form
 
 
 ===================================  
 
sSQL = "INSERT INTO " & sTablename & "[ClientNumber], [InstructorNumber], [ActivityType], [AircraftType], [AircraftRego], [Date], [Duration], [Notes] VALUES (" & iClient & "," & iInstruct & ",""' & sActivity & '"",""' & sACType & '"",""' & sRego & '"",""' & sDate & '""," & gDuration & ",""' & sNotes & '"")" 
 
 
CurrentDb.Execute sSQL
 
 
= The error message I get is: ================
 
Syntax error in INSERT INTO statement
 
=====================================

Open in new window

bantamb20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are missing the ( ) around the column namme list :
sSQL = "INSERT INTO " & sTablename & "( [ClientNumber], [InstructorNumber], [ActivityType], [AircraftType], [AircraftRego], [Date], [Duration], [Notes] ) VALUES (" & iClient & "," & iInstruct & ",""' & sActivity & '"",""' & sACType & '"",""' & sRego & '"",""' & sDate & '""," & gDuration & ",""' & sNotes & '"")" 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bantamb20Author Commented:
Thank you. I have been staring at that for about an hour and completely overlooked that mistake. It works now and I can get on with other things. Cheers and have a good week
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.