Hello all,
    this must be something simple:
    I have an Access 2000 database through which I'm linking to SQL server 7.
    I have a simple table which includes an autonumber field as the primary key:

CREATE TABLE [dbo].[tblUsers] (
 [UserID] [int] IDENTITY (1, 1) NOT NULL ,
 [Username] [varchar] (50) NULL ,
 [Password] [varchar] (50) NULL
) ON [PRIMARY]

An insert statement
"insert into tblusers(username,password) values('x1','x2')"
works fine in the query window for both SQL server and Access but when I try to run the following code it falls over with "error:-2147217900 . Syntax error in INSERT INTO statement."

Code Listing:

'*****************************8
Option Explicit
Public oConn As ADODB.Connection
Sub main()
    Dim strSQL As String

    Set oConn = New ADODB.Connection
    oConn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\design_v1.1.mdb"
    strSQL = "insert into tblusers(username,password) values('x1','x2')"
    oConn.Execute strSQL
    oConn.Close
    Set oConn = Nothing
End Sub
'*********************

End code listing

Presumably it's something to do with the connection string I'm using, but could someone tell me what?

Thanks in advance

Hamish

First thing that comes to mind is that "username" and "password" are reserved words; perhaps not by SQL or Access, but maybe by ADO. Try changing the column names to "uname" and "pword" and see if the code executes without an error.

  Hello all,
      this must be something simple:
      I have an Access 2000 database through which I'm linking to SQL server 7.
      I have a simple table which includes an autonumber field as the primary key:

  CREATE TABLE [dbo].[tblUsers] (
   [UserID] [int] IDENTITY (1, 1) NOT NULL ,
   [Username] [varchar] (50) NULL ,
   [Password] [varchar] (50) NULL
  ) ON [PRIMARY]

  An insert statement
  "insert into tblusers(username,password) values('x1','x2')"
  works fine in the query window for both SQL server and Access but when I try to run the following code it falls over with "error:-2147217900 . Syntax error in INSERT INTO statement."

  Code Listing:

  '*****************************8
  Option Explicit
  Public oConn As ADODB.Connection
  Sub main()
      Dim strSQL As String

      Set oConn = New ADODB.Connection
      oConn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\design_v1.1.mdb"
      strSQL = "insert into tblusers(username,password) values('x1','x2')"
      oConn.Execute strSQL
      oConn.Close
      Set oConn = Nothing
  End Sub
  '*********************

  End code listing

  Presumably it's something to do with the connection string I'm using, but could someone tell me what?

  Thanks in advance

  Hamish

Thanks Steven, that's exactly what it was.

Hamish

  First thing that comes to mind is that "username" and "password" are reserved words; perhaps not by SQL or Access, but maybe by ADO. Try changing the column names to "uname" and "pword" and see if the code executes without an error.