[RESOLVED] Add data to Access DB
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.
Code:
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
Re: Add data to Access DB
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:
Code:
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 & " )"
Re: Add data to Access DB
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
Re: Add data to Access DB
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, "'", "''")
Re: Add data to Access DB
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')
Re: Add data to Access DB
If you'd used sample I posted the "printed" sql would be munch easier to read:
Code:
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...
Re: Add data to Access DB
Is it giving a data-type mismatch error?
Re: Add data to Access DB
get rid of the quotes wherever the datatype is integer
Re: Add data to Access DB
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#
)
Re: Add data to Access DB
So, what's the problem now?
Re: Add data to Access DB
Same problem. Same error. cn.execute sSQL highlighted.
Run Time error '-2147217900 (80040e14)':
Sintax error on INSERT TO Statement.
Re: Add data to Access DB
Can you poste data fields types (for each field)?
Re: Add data to Access DB
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.
Re: Add data to Access DB
Try checking your sql in access using the query builder, that would tell you exactly what is wrong in your sql.
Re: Add data to Access DB
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).
Re: Add data to Access DB
Thanks for your help everybody!!!!!, Problem Solved, it was the reserved word issue.
Re: Add data to Access DB
Quote:
Originally Posted by lyilaly
...Problem Solved, it was the reserved word issue.
Which was pointed out 3 days ago in post #2. ;)
