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

Question

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.

I am unsure if my sql query will run and there is a possibility to do this and create column — Praveen KUMAR
– Praveen KUMAR, Commented Jan 30, 2020 at 12:00

Naveen Kumar · Accepted Answer · 2020-01-30 12:12:22Z

1

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

answered Jan 30, 2020 at 12:12

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naveen Kumar Over a year ago

Thanks. Please vote the answer if you find it helpful.

Sola Oshinowo · Accepted Answer · 2020-02-02 15:22:04Z

0

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.

answered Feb 2, 2020 at 15:22

Sola Oshinowo

6297 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related