Inserting values starting with zero in access database

Question

I have a field called Product_Id(type string), which has length of 7 and starting with 0. But while inserting through VBA into a table field of type text the zeros is not getting inserted.

This is the insert query:

dbs.Execute "INSERT INTO tablename (PROD_NBR)VALUES (" & prodID & ");"

score 5 · Accepted Answer · edited Oct 21 '14 at 13:53

5

I think I have fixed the error - you need to declare the value in single quotes.

The PROD_NBR is a string type and field in the table is text type, then the inserting variable should be declared inside single quotes then double quotes and between two & symbols:

dbs.Execute "INSERT INTO tablename (PROD_NBR)VALUES ('" & prodID & "');"

edited Oct 21 '14 at 13:53

Olle Sjögren

5,315
3
31
51

answered Oct 21 '14 at 13:51

Cherry

393
1
4
15

...And left yourself wide open to SQL injection attacks – LittleBobbyTables - Au Revoir Oct 21 '14 at 18:51
@LittleBobbyTables, what do you suggest? – Cherry Oct 21 '14 at 19:52
Depending on how you're creating the `prodID` variable, see here: http://stackoverflow.com/questions/6764509/can-vba-in-ms-access-using-parameter-to-prevent-sql-injection – LittleBobbyTables - Au Revoir Oct 21 '14 at 20:03
easy way is to just to check that prodID does not contain special characters and/or follows a specific format. Prepared statements are great when you can afford them. – Alter Oct 24 '14 at 00:18

score 0 · Answer 2 · answered Jan 16 '15 at 17:57

Responding to @Cherry's answer, the following method is less tedious than a parameterized query, aka prepared statement. prodID can safely contain quotes and other special characters.

    With dbs.OpenRecordset("tablename")
        .AddNew
        .Fields("PROD_NBR") = prodID
        .Update
    End With

Regarding the "starting with zero" part of your question, do you want PROD_NBR to always be 7 characters, padded with leading 0's? Then replace prodID with:

    Right("0000000" & prodID, 7)

Inserting values starting with zero in access database

2 Answers2