We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Microsoft Access VBA question -- Same problem

Medium Priority
505 Views
Last Modified: 2013-12-06
A question from user Jeanniem has not been actually answered, and i noticed that it is closed.
I'm having the same problem  (I'm using msaccess 2003)

  Can't construct a simple SQL on MS-ACESS VB. I'm using ODBC on an AS400.
   I'm having the same runtime error as Jeanniem.

   For some reason it is not permitting the use of SELECT Clause.

   However it is accepting this string while i'm experimenting.

   SQLStr = "LIB.Table1, LIB.Table2 Where Table1.FLD1 = 1 And Table1.FLD1 = Table2.FLD2"

  This query is accepted. I think it is assuming that it is in (Select *) default. Notice that i put the join in the where clause (a workaround 'And Table1.FLD1 = Table2.FLD2')

   @Jeanniem --> could you try this and give me any feedback? thanks
Set Cnxn = New ADODB.Connection
Set rst1 = New ADODB.Recordset
strCnxn = "Provider='IBMDA400';Data Source='myDSN';User d='';Password='';"
Cnxn.Open strCnxn
 
SQLStr = "Select * From LIB.Table1 Inner join LIB.Table2 On Table1.FLD1 = Table2.FLD2 Where FLD1 = 1"
 
rst1.Open SQLStr, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

Open in new window

Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
1. Is FLD1 a field with the same name in Table1 and Table2? If so, you need to qualify it (Table1.FLD1)
2. Shouldn't it be "Select * From LIB/Table1 Inner join LIB/Table2 ..." for DB2 on AS400?
-chuck wood

Commented:
When I had this problem, it was solved with help from Gary the IT.....

He suggested I do a debug.print on my SQL statement - when I did that it showed me that I didn't have spaces betweeen some of the words and when I fixed that, it worked.  It looks like your SQLstr is correct, but it wouldn't hurt to do a debug.print just in case.
VP Technology / Senior Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
@Jeanniem: Oops, sory i thought your issue was not resolved. though your last post was "Your definately right about the space, but it still throws the following error". sorry

adCmdTable - is indeed the porblem. thanks. my query is working and i can use joins now. thanks a lot

about "myDSN" -  my bad I'm actually using the IP address not DSN

I also prefer using lib/table format though i don't know where to insert the "Naming Convention=1" parameter in my connection string. can you guys help out? is there any reference docs on all the parameters that can be inserted in the connection string?

myConString = "Provider='IBMDA400';Data Source='XX.XXX.X.XXX';User Id='';Password='';"

Notice that I left User Id and password blank. so that it will pop up the login access to AS400 via client access. but when i click the cancel on the popup. I get msgbox that "user cancelled!" or something. how can i catch that error? thanks all in advance
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.