[RESOLVED] Add data to Access DB

[lyilaly]

I have this code to add data to a database i n access, but i´m getting an error on the SQL statement, can anyone of you help me.

I´m using ADO code to connect to the database.

Thanks in advance.

Private Sub Command1_Click()
Dim sSQL As String

sSQL = "INSERT INTO PPE (ID, Name, Last_Name, Qty_Cov, Qty_Boot, Coverall, Boot, Hardhat, Glasses, Vest, Rig, Date) "
sSQL = sSQL & "VALUES (#" & Text1.Text & "#,'" & Text2.Text & "','" & Text3.Text & "',#" & Text4.Text & "#,#" & Text5.Text & "#,'" & Combo1.Text & "','" & Combo2.Text & "','" & Combo3.Text & "','" & Combo4.Text & "','" & Combo5.Text & "','" & Combo6.Text & "',#" & Text6.Text & "#)"

cn.Execute sSQL

'======================================================
'CLEAR DATA FROM CONTROL BOXES & SET TODAY´S DATE AGAIN
'======================================================

    Me.Text1.Text = ""
    Me.Text2.Text = ""
    Me.Text3.Text = ""
    Me.Combo1.Text = ""
    Me.Combo2.Text = ""
    Me.Combo3.Text = ""
    Me.Combo4.Text = ""
    Me.Combo5.Text = ""
    Me.Combo6.Text = ""
    Me.Text4.Text = ""
    Me.Text5.Text = ""
    Me.Text6.Text = ""
    Me.Option1.Value = False
    Me.Option2.Value = False
    Me.Option3.Value = False
    Me.Text1.SetFocus
Me.Text6.Text = Format(Now(), "MMM-DD-YYYY")
rs.Close
End Sub

[RhinoBull]

Few things:

- try not to use reserved words like Name, Date to name any of your data objects (tables, fields, etc)
- use the "#" (number sign) only for Date data type (and you did use for numeric types as well); but even that isn't necessary - you can use single quotes
- try not to use default control name - give each some meaningfull name instead (txtLastName, txtQtyBoot, etc) or you will get lost at some point...

Try sql below instead of yours:

    sSQL = "INSERT INTO PPE" & vbNewLine
    sSQL = sSQL & "    (" & vbNewLine
    sSQL = sSQL & "     ID," & vbNewLine
    sSQL = sSQL & "     Name," & vbNewLine
    sSQL = sSQL & "     Last_Name," & vbNewLine
    sSQL = sSQL & "     Qty_Cov," & vbNewLine
    sSQL = sSQL & "     Qty_Boot," & vbNewLine
    sSQL = sSQL & "     Coverall," & vbNewLine
    sSQL = sSQL & "     Boot," & vbNewLine
    sSQL = sSQL & "     Hardhat," & vbNewLine
    sSQL = sSQL & "     Glasses," & vbNewLine
    sSQL = sSQL & "     Vest," & vbNewLine
    sSQL = sSQL & "     Rig," & vbNewLine
    sSQL = sSQL & "     Date" & vbNewLine
    sSQL = sSQL & "    ) " & vbNewLine
    sSQL = sSQL & "VALUES" & vbNewLine
    sSQL = sSQL & "    (" & vbNewLine
    sSQL = sSQL & "      " & Text1.Text & "," & vbNewLine
    sSQL = sSQL & "     '" & Text2.Text & "'," & vbNewLine
    sSQL = sSQL & "     '" & Text3.Text & "'," & vbNewLine
    sSQL = sSQL & "      " & Text4.Text & "," & vbNewLine
    sSQL = sSQL & "      " & Text5.Text & "," & vbNewLine
    sSQL = sSQL & "     '" & Combo1.Text & "'," & vbNewLine
    sSQL = sSQL & "     '" & Combo2.Text & "'," & vbNewLine
    sSQL = sSQL & "     '" & Combo3.Text & "'," & vbNewLine
    sSQL = sSQL & "     '" & Combo4.Text & "'," & vbNewLine
    sSQL = sSQL & "     '" & Combo5.Text & "'," & vbNewLine
    sSQL = sSQL & "     '" & Combo6.Text & "'," & vbNewLine
    sSQL = sSQL & "     #" & Text6.Text & "#" & vbNewLine
    sSQL = sSQL & "    )"

[lyilaly]

Thanks for your reply and advises RhinoBull,

Nut i´m still getting the same error as before. The cn.Execute sSQL is highlighted and error it´s stil the same

[RhinoBull]

Print actual statement to debug before you execute and see what's wrong.
If any of string type values contain single quote character you will have to replace it with two single quotes:

Replace(text1.text, "'", "''")

[lyilaly]

This is the print of the Sql insert statement. Can anyone tell me what´s wrong.

Thanks in advance.


INSERT INTO PPE (ID, Name, Last_Name, Qty_Cov, QTY_Boot, Coverall, Boot, Hardhat, Glasses, Vest, Rig, Date) VALUES ('13931955','Lionelo','Espina','2','1','36','37','Green New','Dark','Used','P-103','mar-12-2007')

[RhinoBull]

If you'd used sample I posted the "printed" sql would be munch easier to read:

INSERT INTO PPE 
	(
	ID, 
	Name, 
	Last_Name, 
	Qty_Cov, 
	QTY_Boot, 
	Coverall, 
	Boot, 
	Hardhat, 
	Glasses, 
	Vest, 
	Rig, 
	Date
	) 
VALUES (
	'13931955',
	'Lionelo',
	'Espina',
	'2',
	'1',
	'36',
	'37',
	'Green New',
	'Dark',
	'Used',
	'P-103',
	'mar-12-2007'
	)

Since you did not use what I suggested I will have to ask you the following:

- are ID, Qty_Cov, QTY_Boot fields numeric?
If you answer YES then get rid of quotaions (just like posted for you)

- are Coverall, Boot fields numeric too?
If YES the do the same...

[sc0ttr0]

Is it giving a data-type mismatch error?

[onlyGirl]

get rid of the quotes wherever the datatype is integer

[lyilaly]

Sorry RhinoBull,

I pasted wrong because i was trying something else. Hers´s the print when i use your statement.


INSERT INTO PPE
(
ID,
Name,
Last_Name,
Qty_Cov,
Qty_Boot,
Coverall,
Boot,
Hardhat,
Glasses,
Vest,
Rig,
Date
)
VALUES
(
13931955,
'Lionelo',
'Espina',
1,
1,
'36',
'38',
'Green New',
'Dark',
'Used',
'P-110',
#mar-13-2007#
)

[RhinoBull]

So, what's the problem now?

[lyilaly]

Same problem. Same error. cn.execute sSQL highlighted.

Run Time error '-2147217900 (80040e14)':

Sintax error on INSERT TO Statement.

[RhinoBull]

Can you poste data fields types (for each field)?

[lyilaly]

As set on th MS Access Table "PPE":

Line: Autonumeric.
Id: Numeric
Name: Text
Last_Name: Text
Rig: Text
Coverall: Text
Qty_Cov: Numeric
Boot: Text
Qty_Boot: Numeric
Hardhat: Text
Glasses: Text
Vest: Text
Date: Date
Closed: Boolean
cls_date: date

But the only ones i want to change (at this point) are the mentioned in the statement.

[onlyGirl]

Try checking your sql in access using the query builder, that would tell you exactly what is wrong in your sql.

[si_the_geek]

The problem is that Name and Date are reserved words (as can bee seen here, explanation in this FAQ article), so you should change those field names to something different.

If you don't have the option of changing the field names, you can put square brackets around the names in your SQL statement - but this will not eliminate all problems that using reserved words can cause (one example is at the top of the MS article).

[lyilaly]

Thanks for your help everybody!!!!!, Problem Solved, it was the reserved word issue.

[RhinoBull]

...Problem Solved, it was the reserved word issue.

Which was pointed out 3 days ago in post #2.