VB to ACCESS headache!!!

[TriniBeauty]

i have been struggling with a SQL statement that is supposed to save data entered via the VB interface to a MS access database. This is the code that i a using and i am receiving an "syntax error in insert into statement"
any assistance will be greatly appreciated



[CODE=vb]Private Sub cmdsaverec_Clic k()
Dim sql As String
Dim SQL1 As String
Dim cn As New ADODB.Connectio n


' First check to see whether the record already exists in the database

SQL1 = " Select * from Personal where Acctrefno = '" & txtrefno.Text & "'"

' Execute save command if the record does not exist

sql = "Insert into Personal value ('" & txtlastname.Tex t & "','" _
& txtfirstname.Te xt & "','" & txtothername.Te xt & "', '" & txtaddress.Text & "','" _
& txtcontact.Text & "', '" & txtage.Text & "')"


' Create a connection to the MS Acess MACS database

cn.ConnectionSt ring = "DSN=MACS"


' Open the connection to the MACS database
Dim rs As ADODB.Recordset

cn.Open

Set rs1 = cn.Execute(SQL1 )
If rs1.EOF Then
Set rs = cn.Execute(sql)
MsgBox ("Record Saved")
Else
MsgBox ("Account already exists!")


End If
cn.Close

End Sub[/CODE]

If u can think of anything please help out

[QVeen72]

Hi,

It should be "Values" not Value..

sql = "Insert into Personal Values ('" & txtlastname.Tex t & "','" _
& txtfirstname.Te xt & "','" & txtothername.Te xt & "', '" & txtaddress.Text & "','" _
& txtcontact.Text & "', '" & txtage.Text & "')"

Regards
Veena

[debasisdas]

Instead of using SELECT * try to use COUNT(fieldname ).

Performance wise that will be faster.

[TriniBeauty]

It should be "Values" not Value..

sql = "Insert into Personal Values ('" & txtlastname.Tex t & "','" _
& txtfirstname.Te xt & "','" & txtothername.Te xt & "', '" & txtaddress.Text & "','" _
& txtcontact.Text & "', '" & txtage.Text & "')"

Hey and thanks for the response.
Here is an update.
I made the changes but now I am getting a runtime error, too few parameters, expected 1.
Please assist.
This is how the code looks now.
[CODE=vb]Private Sub cmdsaverec_Clic k()
Dim sql As String
Dim SQL1 As String


' First check to see whether the record already exists in the database

SQL1 = " Select * from Personal where acctrefno = '" & txtrefno & "'"

' Execute save command if the record does not exist

sql = "Insert into Personal values ('" & txtlastname & "','" _
& txtfirstname & "','" & txtothername & "', '" & txtaddress & "','" _
& txtcontact & "', '" & txtage & "' '" & txtrefno & "')"


' Create a connection to the MS Acess MACS database

Dim cn As New ADODB.Connectio n
cn.ConnectionSt ring = "DSN=MACS"


' Create a record set

Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset


'Open the connection

cn.Open

Set rs1 = cn.Execute(SQL1 )

If rs1.EOF Then
'Execute the statement

Set rs = cn.Execute(sql)
MsgBox ("Record Saved")
Else
MsgBox ("Account already exists!")


End If
cn.Close

End Sub[/CODE]

[TriniBeauty]

Instead of using SELECT * try to use COUNT(fieldname ).

Performance wise that will be faster.

Thanks a lot!
I have been really struggling with this