How to Update a Destination Table using a Source Table using VBS

  • Thread starter Thread starter Carlos Garcia via AccessMonster.com
  • Start date Start date
C

Carlos Garcia via AccessMonster.com

Hello All,

I'd like to ask for some help in how to update a destination table based on
the source table using VBS. I tried some coding that I found over the net
but I couldn't adjust it. Example: I want update all records from table1
into table2
Example:
Table1 (Source):
Fields (ID_Code_Exe, SoftName, Group)

Table2 (Destination):
Fields (ID_Soft, SoftName) where ID_Soft is AutoNumber.
 
Hello All,

I'm still looking for "Help", but I'm sending below what I have done so far
and I am getting error: Join Expression Not Supported, Code: 800A0CE0,
Source: DAO.Database. Thank you in advance for your help.

Sub TableUpdate(MDBFile2, TableSource, TableDestination, TableField)
Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String

Dim strSQLNew ' String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(MDBFile2)

strSQL = "INSERT INTO " & TableDestination & "." & TableField _
& " SELECT " & TableSource & "." & TableField _
& " FROM " & TableDestination & " RIGHT JOIN " & TableSource & " ON " &
TableDestination & "." & TableField _
& " = " & TableSource & " WHERE (((" & TableSource & "." & TableField & ")
Is Not Null));"
oDB.Execute strSQL

SQL's Simple View:
INSERT INTO Software ( device )
SELECT wsSoftware.device
FROM Software RIGHT JOIN wsSoftware ON Software.device = wsSoftware.device
WHERE (((wsSoftware.device) Is Not Null));
 
Hello, I just found a remedy for my problem and here is the code. Thanks.

strSQLNEW = "INSERT INTO " & TableDestination & "(" & TableField & ")
SELECT " & TableSource & "." & TableField _
& " FROM " & TableSource _
& " WHERE " & TableSource & "." & TableField & " Is Not Null GROUP BY " &
TableSource & "." & TableField & ";"
oDB.Execute strSQLNEW
 
Back
Top