CODEWITHSUNDEEP
sqlvbams-access
Praveen KUMAR
Praveen KUMAR

Reputation: 67

MS Access Database insert values on a column using vba and sql query

Is there any way to add a column into an MS Access database using VBA and SQL query and then insert values using formulas from other column.

As an example, I want to create a column employee and it should have calculated values on all the records. I want to use LEFT(col2, 3) in the employee field.

This is my code:

Sub createcolumn()
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")
    Call objAccess.OpenCurrentDatabase("H:\VBA\Array Practice\Database1array.accdb")
    objAccess.CurrentProject.Connection.Execute ("ALTER TABLE Company ADD COLUMN employee CHAR ")
End Sub

Sub insertvalues()
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")
    Call objAccess.OpenCurrentDatabase("H:\VBA\Array Practice\Database1array.accdb")
    objAccess.CurrentProject.Connection.Execute "INSERT INTO Employee (Gross) VALUES LEFT([full_name], 3)"
End Sub

I am able to create a new column but could't insert the value.

Upvotes: 1

Views: 1407

Answers (2)

Sola Oshinowo
Sola Oshinowo

Reputation: 619

You added a column to a table named Company, but you are attempting to insert values to a table named employee.

If the table named company as records then an update statement is what you need to add values into the new column employee, but if the table does not have any record, then an insert statement can be used to add values into the employee field.

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 2006

You can directly update the table using column FULL_NAME instead of inserting. I have tested the following code and it is working.

Sub test()
Dim dbs As Database
Set dbs = OpenDatabase("Database1.accdb")  
dbs.Execute "UPDATE EMPLOYEE SET GROSS=LEFT(FULL_NAME, 3)"
dbs.Close
End Sub

Upvotes: 1

Related Questions