Problems with RIGHT JOIN and VBS

[Carlos Garcia via AccessMonster.com]

Hello,

I'm looking for some help, I'm using the code below and It seems that VBS
cannot execute RIGH Join on a SQL statement. Thank you in advance for any
help. Thanks.

Sub TableUpdate(MDBFile, 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(MDBFile)

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

End Sub

 

[JohnFol]

VBS is not executing it, JET is.

What is the value of strSQL just before the execute? What happens if you
paste this into the query SQL view and execute it manually?
Also, what error do you get?

 

[JaRa]

Maybe the variables contain special chars or blanks so first try this (see
below)
And also you code missed a blank just after the right JOIN.

To avoid this problems build your statement in a query builder and the
replace according your needs.

- Raoul

Sub TableUpdate(MDBFile, 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(MDBFile)

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

End Sub

 

[Carlos Garcia via AccessMonster.com]

Thank you for your reply. I have now a simple query and it solved the
problem that I was having. Raul: I have tried your solution but I got
sintax error on the SQL. I am using TextPad to write my VBS code. Which
Query Builder do you use? Please, let me know. Thanks.

Sub TableUpdate(MDBFile, TableSource, TableDestination, TableField)

Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQLNEW ' String

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

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

End Sub

 

[JaRa]

I use mainly access itself

- Raoul