Solved

Microsoft Access VBA question -- Same problem

Posted on 2009-07-08
6
449 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

0
Comment
Question by:belbenj
6 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 24812268
Remove the adCmdTable argument and see what happens.

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24813743
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
0
 

Expert Comment

by:Jeanniem
ID: 24814462
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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

Accepted Solution

by:
Gary Patterson earned 350 total points
ID: 24816563
The IBMDA400 provider defaults to the SQL naming convention (lib.table).  You can switch to System naming convention (lib/table) if you like by adding the "Naming Convention=1" option to your connection string.  It defaults to "0", so your table names are formatted OK here.

As observed by LMSConsulting, adCmdTable option needs to go.  This option causes the ADO provider to generate a query for you, and is not compatible with an SQL statement being provided in the SOURCE parameter.

Now, what is the exact error message that you are getting?

A few additional notes:

1) IBMDA400 is an OLE DB provider.  OLE DB Providers do not use ODBC data sources, so if "myDSN" is an ODBC data source name, you need to change it to the name of your AS/400 (IP address, FQDN, etc.).

2) I don't know if you have any duplicated field names between your tables (are these really the table and field names, or did you substitute generic names?  Post the real field and table names if possible), but it is a good practice to get in the habit of using correlation names for your tables:

Select * From LIB.Table1 as T1 Inner join LIB.Table2 T2 On T1.FLD1 = T2.FLD2 Where T1.FLD1 = 1

Note that each field reference is qualified with the correlation name.

3) If you continue to have trouble, drop down to adOpenStatic and adLockReadOnly and see if your query works.  Post back if you need to change these parameters to make it work (are you planning to update, and do you plan to move bback and forth in your results in a way where you need to see records that change while you are running your application?

4) Test your SQL using the Navigator program that comes with IBM Client Access, navigate to your AS/400 -> Databases -> right click your database, and select Run SQL Scripts.  Test your query here until it works - you'll get easier access to detailed error messages.  Use Connection -> JDBC Setup to set your preferred connection settings (like the naming format).  You can also use the green-screen utility STRSQL to test SQL statements (that is my preferred tool).
 
- Gary Patterson

0
 

Author Comment

by:belbenj
ID: 24820126
@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
0
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 350 total points
ID: 24825945
Put it anyplace you like in the connection string.  Order doesn't matter.

OLE DB Driver documentation is included as part of the Help that comes with Client Access Programmer's Toolkit - an optionally-installable Client Access component.

You handle this error like any other in VBA:  Either let the default handler catch it (that is what is happening now), or provide your own error handling.  See the example below.

- Gary Patterson









Set Cnxn = New ADODB.Connection

Set rst1 = New ADODB.Recordset

strCnxn = "Provider='IBMDA400';Data Source='myAS400';User id='';Password='';Naming Convention=1;"
 

On Error Resume Next    'We will handle our own errors

Cnxn.Open strCnxn

If Err.Number = 0 Then

    MsgBox "Connection succeeded"

    Cnxn.Close

Else

    MsgBox "Connection failed with error: " & Err.Number & " - " & Err.Description

    Err.Clear

End If

On Error GoTo 0        ' Switch back to default error handler

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now