Reputation: 67
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
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
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