ADO, Access and an SQL statment 
Author Message
 ADO, Access and an SQL statment

Having a slight problem with this. I'm tring to use ADO to
insert username, password and level into and Access db.
The form attached is the one I'm using, only other file
used for this part is a db called Useraccess.mdb with a
table called User with fields username, password and level.

The form runs ok, except for when adding a record. It
manages the select statement (when there are records in
the table), but it falls down with the insert, returning
an error:
     Run-time error '-2147217900(80040e14)':
     Syntax error in INSERT INTO statemnt.

I have tried using a similar form of statemnt in MS Query,
and it worked ok, so I was wondering if it was anything to
do with the way I'm trying to input the values:

SQL = "INSERT INTO User" & _
    " VALUES(" & _
    "'" & username & "'," & _
    "'" & password & "'," & _
    "'" & level & "'" & _
    ")"
    Conn.Execute SQL

It falls down somewhere there. Password has had simple
encryption written on it, username is taken direct from
username.text in a text box, and level is selected by if
statements on option buttons. (that's just in case you
don't run the form)

Any help would be greatly appreciated

Will

  frmuseradmin.frm
7K Download


Fri, 06 May 2005 17:58:22 GMT  
 ADO, Access and an SQL statment
Hi Will,

"User" is a reserved word. To be able to use it as a field's or table's name
you need to wrap it into [] or rename it. In that case your SQL statement
will look like

SQL = "INSERT INTO [User]" & _
     " VALUES(" & _
     "'" & username & "'," & _
     "'" & password & "'," & _
     "'" & level & "'" & _
     ")"

--
Val Mazur
Microsoft MVP


Quote:
> Having a slight problem with this. I'm tring to use ADO to
> insert username, password and level into and Access db.
> The form attached is the one I'm using, only other file
> used for this part is a db called Useraccess.mdb with a
> table called User with fields username, password and level.

> The form runs ok, except for when adding a record. It
> manages the select statement (when there are records in
> the table), but it falls down with the insert, returning
> an error:
>      Run-time error '-2147217900(80040e14)':
>      Syntax error in INSERT INTO statemnt.

> I have tried using a similar form of statemnt in MS Query,
> and it worked ok, so I was wondering if it was anything to
> do with the way I'm trying to input the values:

> SQL = "INSERT INTO User" & _
>     " VALUES(" & _
>     "'" & username & "'," & _
>     "'" & password & "'," & _
>     "'" & level & "'" & _
>     ")"
>     Conn.Execute SQL

> It falls down somewhere there. Password has had simple
> encryption written on it, username is taken direct from
> username.text in a text box, and level is selected by if
> statements on option buttons. (that's just in case you
> don't run the form)

> Any help would be greatly appreciated

> Will



Fri, 06 May 2005 20:34:54 GMT  
 ADO, Access and an SQL statment
Hi Will,
You might also need to add the following to the statment
if it still doesn't work. What Val wrote should work, but
Access can be stuborn sometimes.
SQL = "INSERT INTO [User](username, password," & _
      " level)" & _

Quote:
>     " VALUES(" & _
>     "'" & username & "'," & _
>     "'" & password & "'," & _
>     "'" & level & "'" & _
>     ")"

HTH
Ed Voss
MCP

Quote:
>-----Original Message-----
>Hi Will,

>"User" is a reserved word. To be able to use it as a

field's or table's name
Quote:
>you need to wrap it into [] or rename it. In that case
your SQL statement
>will look like

>SQL = "INSERT INTO [User]" & _
>     " VALUES(" & _
>     "'" & username & "'," & _
>     "'" & password & "'," & _
>     "'" & level & "'" & _
>     ")"

>--
>Val Mazur
>Microsoft MVP


message

>> Having a slight problem with this. I'm tring to use
ADO to
>> insert username, password and level into and Access db.
>> The form attached is the one I'm using, only other file
>> used for this part is a db called Useraccess.mdb with a
>> table called User with fields username, password and
level.

>> The form runs ok, except for when adding a record. It
>> manages the select statement (when there are records in
>> the table), but it falls down with the insert,
returning
>> an error:
>>      Run-time error '-2147217900(80040e14)':
>>      Syntax error in INSERT INTO statemnt.

>> I have tried using a similar form of statemnt in MS
Query,
>> and it worked ok, so I was wondering if it was
anything to
>> do with the way I'm trying to input the values:

>> SQL = "INSERT INTO User" & _
>>     " VALUES(" & _
>>     "'" & username & "'," & _
>>     "'" & password & "'," & _
>>     "'" & level & "'" & _
>>     ")"
>>     Conn.Execute SQL

>> It falls down somewhere there. Password has had simple
>> encryption written on it, username is taken direct from
>> username.text in a text box, and level is selected by
if
>> statements on option buttons. (that's just in case you
>> don't run the form)

>> Any help would be greatly appreciated

>> Will

>.



Sat, 07 May 2005 05:10:58 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. SQL statment using ADO and Access

2. Help with SQL statment with Access # 2

3. Help with SQL statment and access

4. SQL AND and OR statments the wrong way round in Access using VB

5. Case Statment in Stored Procedure Creates ADO Error

6. Build a SQL statment dynamically

7. Year 2000 problem in SQL statments written in VBA

8. WHERE clause in my SQL statment keeps blowing up

9. SQL statment within VB

10. fields from a sql statment

11. SQL Statment

12. SQL statment within VB

 

 
Powered by phpBB® Forum Software