Ok, I have created a module in MS Access that imports specified data
from an AS/400 to an Access table. The sub routine connects to the
AS/400 using an ADODB connection. I am using an SQL select, from, and
where statement to specify what information I need. The routine works
fine in all instances, except the one I need. I need to have an INNER
JOIN between one of the AS/400 tables and an Access table. The Access
table has the values(Account#) that I need certain information for
from the AS/400. Anyway, here is an example of the statement:
"SELECT Account#,msact#,msolnd,msaprn,msssn "
"FROM ccdata.ccmast23(AS/400 Table) INNER JOIN
tblNewOrderEntry_Temp(Access Table) ON msact# = Account# "
"WHERE msact# = Account# "(optional)
The above does not work, it gives me an SQL statement error, to be
more specific it bombs out on my "rsCCMAST.Open cmd.execute"
statement. It will work the following way:
"SELECT msact#,msolnd,msaprn,msssn "
Anyway, I have tried matching the field values in access and the 400,
tried changing the SQL syntax, I have checked my references. I dont
know why the INNER JOIN wont work, but hopefully some of you have some