Vba to Access record Insert Issue

Question

I want to insert Values to access table by using VBA control is there is any simple way to do this. i try this code but it does not work properly if i run this code it give the error 'variable not set' can anyone help me. thanks in advance

Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
    Dim strSql As String
    Dim lngKt As Long
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String

    Dim myRecordset As New ADODB.Recordset
    Dim SQL As String, SQL2 As String

    dbPath = "table.accdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

   SQL = "INSERT INTO Jun_pre (ProductName,DESCRIPTION,SKU,MT,(mt),MRP,Remark,no_of_units_in_a_case) VALUES (""aa"",""bb"",""test"",""testUnit"",""1"",""2"",,""3"",,""4"");"

        With cnt
        .Open dbConnectStr 'some other string was there
        .Execute (SQL)
        .Close
    End With
End Sub

Answer 1

You are working in Access, so it is best to use DAO as it is native. Consider:

Private Sub CommandButton1_Click()
Dim sSQL As String

sSQL = "INSERT INTO Jun_pre " _
     & "(ProductName,DESCRIPTION,SKU,MT,(mt),MRP,Remark," _
     & "no_of_units_in_a_case) VALUES "
     & "(""aa"",""bb"",""test"",""testUnit"",1,2,Null,3,Null,4);"

CurrentDB.Execute sSQL, dbFailOnError
End Sub

Against:

Private Sub CommandButton1_Click()
    Dim cn As New ADODB.Connection
    Dim strSql As String
    Dim lngKt As Long
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String

    ''You do not need a recordset to execute an SQL statement
    ''Dim myRecordset As New ADODB.Recordset
    Dim SQL As String, SQL2 As String

    dbPath = "table.accdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

    strSQL = "INSERT INTO Jun_pre " _
           & "(ProductName,DESCRIPTION,SKU,MT,(mt),MRP,Remark," _
           & "no_of_units_in_a_case) VALUES "
           & "(""aa"",""bb"",""test"",""testUnit"",1,2,Null,3,Null,4);"

    With cnt
        .Open dbConnectStr 'some other string was there
        .Execute (strSQL)
        .Close
    End With
End Sub

If your ADO example is inserting into the current database, it can be considerably simplified.

Note that:

• It is very unlikely that your table will accept a zero length string (,,), Null is probably better.
• I very much doubt that 2 is goining into a text field, so it does not need quotes.
• SQL is a reserved word for VBA and should not be used to name variables.
• It is not impossible to use SQL injection with Access, though it can be difficult.
• If you give you controls proper names now, it will save grief in the future

Answer 2

Your INSERT statement will not work. Here is the statement you're attempting to execute:

INSERT INTO Jun_pre (
    ProductName,
    DESCRIPTION,
    SKU,
    MT,
    (mt),
    MRP,
    Remark,
    no_of_units_in_a_case)
VALUES (
    "aa","bb","test","testUnit","1","2",,"3",,"4");

Notice you have listed 8 target fields, then 8 plus 2 "nothings" in the VALUES list. That is bound to fail.

Apparently you have a field which you named (mt). If you keep parentheses in the field name, enclose the name in square brackets to avoid confusing Access. It would be better to rename the field without parentheses.

DESCRIPTION is a reserved word. See Problem names and reserved words in Access. If you keep that name, enclose the name in square brackets in your INSERT statement.

Sort this all out by opening table.accdb directly in Access and trying your query there. Still not sure what you're trying to do, so will suggest trying this INSERT as a new query:

INSERT INTO Jun_pre (
    ProductName,
    [DESCRIPTION],
    SKU,
    MT,
    [(mt)],
    MRP,
    Remark,
    no_of_units_in_a_case)
VALUES (
    "aa","bb","test","testUnit","1","2","3","4");