ACCESS VBA SQL 3

[3pmilk]

Hi all,

here's my problem: When I execute my sql againast Oracle server via sql navigator, it works well. However, when executing the same query in Access, the JET engine doesn't like the Oracle reserved key words such as START WITH, CONNECT BY PRIOR, and ORDER SIBLINGS BY. Any ideas how I can convert these keywords into a language that Jet engine can parse?

Another solution I came up with which didn't work quite exactly what I want, was to write a pass-thru query in access - it worked - however I can't by pass the dialog box asking me which "machine data source" to choose from before I can execute the sql. Any ideas?

Thanks all for your help

 

[Crowley16]

jet cannot handle those functions, so if you want a access query, you'll have to change your sql to not include them. It's usually a good idea to keep to ANSI standard when connecting between different db platforms

if you want to go the pass through query route, in the query properties, you can set and save the connection string, needed. Once you've saved the connection string, including the usr + pswd details, it shouldn't ask you again

--------------------
Procrastinate Now!

 

[appelq]

Just my two cents:
The Pass Through Query will likely perform better as well.

To set the ODBC connect string, Open (in desgn view) the Pass Through query you created, and right cllick on the blue header, and select Properties.
In the ODBC Connect Tring field enter your string (Or modify what's there) to include:
...;UID={username};PWD={password};...

This not recommended for security reasons. The PWD value is not encrypted, so if you use this meathod, be sure to limit the rights of the UID to the bare minimum.

 

[3pmilk]

Thank you, appelq. I took your advice and it kinda worked. Unfortunately, the connection string doesn't STAY saved. The connection would only work at the time it was saved. subsequent times when executing the sam query, it would generate an ODBC failure error and I would have to re-set and save the connection.

Actually, what I'm really trying to do is to execute a pass-thru query using ado connection object. Any idea?

 

[cmmrfrds]

Are you binding the query to a Form or Report. If not, then you can send the ANSI Oracle SQL directly through ADO and bring the data back as a recordset. This is fairly staightforward and the login and password is part of the connection string.

 

[3pmilk]

Hi cmmrfrds,

I'm not binding the query to anything. I have a pass-thru query (with oracle reserved key words) in Access 2003, so I can't covert it into sql statements in VBA programmatically, because there's no direct equivalent in T-SQL for Oracle's START WITH...CONNECT BY.

My first problem is that I can't save the ODBC connection for the pass-thru query permanently. I may need to save it via VBA programmatically but don't know how.

I managed to save the ODBC connection via Access "View" -> "properties." It would work at the time it was saved. However, when executing the same query at later times, it would generate an "ODBC failure error" and wrould prompt me to re-set the property.

My second problem is that how can I execute the pass-thru query through ado connection?

here's the sample code that's failing saying "execute method fail"

set rs = conn.execute("pass-thru query name", , adCmdText)

I came up with an alternative half-baked soultion to my problem. I was thinking maybe I can use docmd.openquery in Access to open my pass-thru query and somehow save the result set as a sql statements and pass it to a variable...once I have the
variable containing the sql statements then I can
use the connection object to execute it..but i'm not sure how i can convert the result of the openquery to a variable either.

 

[cmmrfrds]

You can pass Oracle SQL directly throught ADO.

First make sure your connection string works.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the Oracle OLEDB Provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[cmmrfrds]

Here is an example of bringing back a recordset from SQL Server. Done the same way in Oracle with a different connection string.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"

Set cn = CurrentProject.Connection

sql1 = "select * from firsttab"
Debug.Print sql1
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Debug.Print rs.RecordCount
Debug.Print rs.EOF
Debug.Print rs.GetString

rs.Close
Set rs = Nothing

 

[3pmilk]

Thanks again, cmmrfrds, for your help. I will give it a try tomorrow.
Quick question: can recordset's open method execute a pass-thru query object in access? If it can't then I'm not sure how to implement your advice into the works, because
I have tried to convert my "pass-thru" query into
sql statment, but it keeps failing on me, because the ACCESS Jet engine can't parse Oracle keywords.

Here's how my sql1 looks, which is equivalent to your sql1 in your example, but mine has oracle reserved keywords.

sql1 = "select * from firsttab "& _
" START WITH firsttab.child is null" & _
"CONNECT BY PRIOR SYBLINGS "

 

[3pmilk]

Hi cmmrfrds,

Thank you very much for your advice. I followed your steps of creating the connection and the connection was successful.
However, I'm running into other problems. Somehow I can load the data into a recordset. Here's my code:

When I execute this code, it stopped at set rs = conn.Execute(strSql, , adCmdText)line and says "
operation is not allowed when the object is closed

And if I put conn.open after the conn.ConnectionString line,
it says " multipule-step OLE DB operation generated errors. Check each OLE
DB status vaule, if available. No work was done.

Andy idea?

Sample code

Dim conn As adodb.Connection
Set conn = New adodb.Connection
Dim strSql As String
Dim rs As adodb.Recordset


conn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=prtp1225;Persist Security Info=True;" & _
"User ID=privuser;Initial Catalog=C:\Manufacturing and Material_Wendy.mdb;Data Source=A019PROD" &_

strSql = "SELECT ADMUSER_PROJWBS.PROJ_ID, ADMUSER_PROJECT.PROJ_SHORT_NAME, ADMUSER_PROJWBS.WBS_NAME " & vbNewLine & _
"FROM ADMUSER_PROJECT, ADMUSER_PROJWBS, EVMS_T_PROJECT_MASTER " & vbNewLine & _
"WHERE ADMUSER_PROJWBS.WBS_SHORT_NAME = ADMUSER_PROJECT.PROJ_SHORT_NAME AND " & vbNewLine & _
"ADMUSER_PROJWBS.PROJ_ID = ADMUSER_PROJECT.PROJ_ID AND " & vbNewLine & _
"ADMUSER_PROJWBS.PROJ_ID = EVMS_T_PROJECT_MASTER.BASELINE_PROJ_ID " & vbNewLine & _
"ORDER BY ADMUSER_PROJECT.PROJ_SHORT_NAME"

Set rs = conn.Execute(strSql, , adCmdText)

' Load the data.
Do While Not rs.EOF
Set list_item = BellListView.ListItems.Add(, , _
rs!proj_id)
list_item.SubItems(1) = rs!proj_short_name
list_item.SubItems(2) = rs!Wbs_Name

' Get the next record.
rs.MoveNext
Loop

 

[cmmrfrds]

Try using the Open method instead of the Execute method.

Set rs = conn.Execute(strSql, , adCmdText)

Use instead.
rs.Open strSQL, conn,3,3

' Load the data.
Do While Not rs.EOF
Set list_item = BellListView.ListItems.Add(, , _
rs!proj_id)
list_item.SubItems(1) = rs!proj_short_name
list_item.SubItems(2) = rs!Wbs_Name

' Get the next record.
rs.MoveNext
Loop

 

[cmmrfrds]

I noticed the Open connection was missing.

After setting up the connection string.

conn.Open

 

[3pmilk]

Hi cmmrfrds,

when I use

rs.Open strSQL, conn,3,3

it says "operation is not allowed when the object is closed"


And if I put conn.open after my conn.connectionstring line

it says "
" multipule-step OLE DB operation generated errors. Check each OLE DB status vaule, if available. No work was done."

Thank you for your help again.

 

[cmmrfrds]

Here is normally how I do it. Maybe you need to feed the connection string to the open like this example.

Dim cn As New ADODB.Connection
Dim connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString


Also, it is a good idea to put some error handling code in your function or subroutine.

Public Function yourfunction()
On Error GoTo Errhandler
''''' your code here
Exit Function
Errhandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function

 

[3pmilk]

Hi cmmrfrds,

Thanks again for your help. I implemented your advice exactly, but I'm still getting an error that says "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

And the offending code is at conn.open connectionstring.

The funny thing was, if I were to use the following connectionstring the code would work.

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Manufacturing and Material_Wendy.mdb;"

Basically, I have linked tables in Access from Oracle database, and I'm trying to execute several sqls againast the jet engine, one of which wouldn't work because it has Oracle reserved keywords, that's the reason I need to change
my connectionstring to oracle ole db provider so that it can by pass the jet engine and directly parse by the Oracle server.

 

[cmmrfrds]

When you used the UDL Wizard to setup the connection string did you press the test to see if the connection was working. Did you paste in the connection string that worked with the test?

This does not look correct.
conn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=prtp1225;Persist Security Info=True;" & _
"User ID=privuser;Initial Catalog=C:\Manufacturing and Material_Wendy.mdb;Data Source=A019PROD"

The intial catalog should be an Oracle Schema.

Paste in the connection string from the UDL wizard.

 

[kjv1611]

cmmrfrds,

That udl wizard has got to be one of the coolest tricks I've learned in quite a while. I had no clue that was even an option! Have a star!

My question was posted at thread705-1247094

 

[3pmilk]

Hi cmmrfrds,

It worked! THANK YOU..THANK YOU..THANK YOU...SO MUCH.

You're a genius

 

[kjv1611]

cmmrfrds,

I've got the connection working fine, but typically, I use this statement structure sort of as an introduction in my SQL statements, but Access keeps giving me errors with it.

Here is the code:

USE DatabaseName
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF

And here is the error message:

Error Encountered!

Error Number
3129 Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I used the custom error code from this post, as well.

Any ideas with that?

Thanks!

 

[cmmrfrds]

Typically through ADO is sent DML or DDL where the statements you show are session level statements. I don't have sql server at work only Oracle, so, I can't test your syntax. If I am on my PC tonight and remember I can run a quick test.

Anyhow, separate statements must be dilimited by ; (semi colon) so you could try that. Otherwise paste in your code so that we can give it a look.