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.
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
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加