SQL Syntax in ADODB Data Transfer from AS/400 to MS Access

SQL Syntax in ADODB Data Transfer from AS/400 to MS Access

Post by Eric Krau » Fri, 05 Oct 2001 01:18:43

Hi all,

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 "
"FROM ccdata.ccmast23

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


Eric Krauss