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

 

[kjv1611]

Ok, you asked for it. Here it is (with the actual table, field, database names changed to "protect the innocent." [SMILE]

Option Compare Database
Public strDate As String 'To store the data in yyyymmdd format.
     'I used the Public for this variable, b/c I'll want to use it for another procedure as well.

Private Sub MySub()
On Error GoTo ErrHandle
    Dim strSQL As String
    Dim cn As New ADODB.Connection
    Dim connString As String
    Dim strUserID As String
    
    strDate = Format$(Date, "yyyymmdd")
    connString = "Provider=SQLOLEDB.1;" & _
                      "Integrated Security=SSPI;" & _
                      "Persist Security Info=False;" & _
                      "Data Source=SERVER01"
    cn.ConnectionString = connString
    cn.Open connString
                  
    strSQL = "USE Database1 " & _
             "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " & _
             "SET NOCOUNT OFF "

    DoCmd.RunSQL strSQL

    strSQL = "SELECT  DISTINCT a.RecordID --, a.CatID, b.CreateUID, b.UpdateUID, c.MeInfo " & _
             "INTO    Sandbox.[MYDOMAIN\winlogin].Prefix_" & strDate & " " & _
             "FROM    dbo.ViewB b, dbo.ViewA a, dbo.ViewC c " & _
             "WHERE   (a.RecordID = b.RecordID) AND " & _
                     "(a.RecordID = c.RecordID) AND " & _
                     "(b.InfoDate is NULL) AND " & _
                     "(b.InfoType is NULL) AND " & _
                     "(b.InfoDataType = ('Mickey is a mouse')) AND " & _
                     "(a.MeInfoID <> (12)) AND " & _
                     "(a.CatID in ('1234', '4444', '9876')) AND " & _
                     "(c.MeInfo <> 'ABC') AND " & _
                     "(c.MeInfo <> 'DEF') AND " & _
                     "(c.MeInfo <> 'GHI') AND " & _
                     "(c.MeInfo <> 'JKL') AND " & _
                     "(c.MeInfo <> 'MNO') "

'    Debug.Print strSQL  'Was used earlier for checking the SQL

    DoCmd.RunSQL strSQL

    MsgBox "Operation Complete!"

    cn.Close
Exit Sub

ErrHandle:
    Dim er As ADODB.Error
    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 er
    MsgBox "Error Encountered!" & Chr(13) & Chr(13) & _
                Chr(9) & "Error Number" & Chr(13) & _
                Chr(9) & Err.Number & Chr(9) & Err.Description
    cn.Close

End Sub

I don't know if this will help, but when I put in:
USE DatabaseName, that means on Server01, use that database that I just listed, so I don't have to specify any more information.

When I say:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, that is just a standard statement that the DBs here have tried to teach us to use in order to have less of a chance for one of our ad-hoc queries to cause much server overload, if I remember correctly.

When I say:
SET NOCOUNT OFF, that is just a statement in SQL server that tells it to show you a record count - don't hide it in the results, basically. This is not really important, but I try to use it as standard - picked that up from one of the DBs as well.


Basically, what I want to be able to do is, once I've made the connection, how do I tell the SQL which database to find the tables/views/fields, and make sure I'm using that connection for the SQL I am running.

Thanks!

 

[PHV]

As you use DoCmd.RunSQL I don't understand what do you expect from the ADODB.Connection object ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

What would be the best way to run the SQL? I've never run it in VBA outside of the DoCmd.RunSQL command. Should I use a different connection in the SQL string somehow; or should I use some other way of passing the SQL statement through the ADODB connection?

 

[PHV]

Have a look at the ADODB.Command object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Thanks, PHV, I'll have a look at that. The help file has tons of information in that area, but it all seems like it's a ton of stuff to get what would seem a simple task accomplished. I'll probably have to hold off on looking at that in any depth - at least until next Monday, possibly.

 

[cmmrfrds]

The 3 statements you put in the beginning of the SQL String are properties on the Connection.

Put this code in right after setting up the connection to see what properties you can set. For example.
"Initial Catalog" is the database name

Debug.Print " --------------Connection Properties --------------"
Dim indx As Integer, pp As ADODB.Property
For Each pp In cn.Properties
Debug.Print "The Field Name = "; cn.Properties(indx).Name
indx = indx + 1
Next

As mentioned by PHV get rid of DoCmd.RunSQL strSQL
The string should start with.
"Select Distinct etc..........."

Define a recordset.
Dim rs as New ADODB.Recordset

After you set up the SQL string do.
rs.Open strSQL, cn, 3, 3

If not(rs.EOF and rs.BOF) then
msgbox "no records returned"
End If

 

[PHV]

cmmrfrds, we are talking about a "SELECT ... INTO ..." instruction and thus I don't expect the Recordset to return any record.
This is why I suggested a Command object.
I think you may however Excute the Recordset's CommandText instead of Open it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

PHV, you are right I didn't look far enough at the Select to see the INTO. There is an Execute method on the connection as well as on the Recordset or Command so any should work.

Something like this should work.
Dim recordsaffected as Long

cn.Execute strSQL, recordaffected

Debug.Print "Records inserted = " & recordsaffected

 

[kjv1611]

Alright! I tried the method of:

cn.Execute strSQL, recordsaffected

And that worked perfectly, except I some times get a time-out error, and other times I do not (same query). Is there a way to change the time-out settings? I know with some queries, I have to use Query Analyzer as apposed to Enterprise Manager, b/c EM willl time out whereas QA will not.

 

[kjv1611]

One more thing I found strange:

the recordsaffected returned 1 (when the procedure didn't time out), even though when I pulled up the newly created table in Enterprise Manager, it showed way more than 1 - a couple hundred. Any ideas?

 

[cmmrfrds]

Each object has it's own timeout property, so modify the connection property.
The default time out on the connection is I believe 15 seconds

cn.CommandTimeOut = 300 ''i.e. 5 minutes
cn.Execute strSQL, recordsaffected

Not sure, it could be since it is a "Select ... INTO" that the count is only 1.

 

[kjv1611]

Ok. Now it does not timeout, but the count is still messed up - it says "-1 records inserted into table, " &[NewTableName]

My Messagebox code says:

MsgBox recordsaffected & " records inserted" & Chr(13) & _
               "into new table, WRI_" & strDate & ".", vbInformation, "Operation Complete!"

 

[cmmrfrds]

If you set "NOCOUNT ON" this will disable messages.

Here is some background info on ADO.

http://www.takempis.com/adoobjects.asp

 

[kjv1611]

Well, I tried leaving it as "SET NOCOUNT OFF", changing to "NOCOUNT ON", and taking that line out altogether, but it seems to have no effect.

It is using the variable, recordsaffected, to get the number of records. Should I not use that variable, or does something else possibly cause the value behind that variable to change?

 

[kjv1611]

I did find this much in the VBA helpfile:

RecordsAffected Property

Returns the number of records affected by the most recently invoked Execute method.

Return Values

The return value is a Long from 0 to the number of records affected by the most recently invoked Execute method on either a Database or QueryDef object.

Remarks

When you use the Execute method to run an action query from a QueryDef object, the RecordsAffected property will contain the number of records deleted, updated, or inserted.

When you use RecordsAffected in an ODBCDirect workspace, it will not return a useful value from an SQL DROP TABLE action query.

But that doesn't seem to explain what is happening.

 

[PHV]

Seems you read the DAO help instead of the ADODB !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

That is the DAO helpfile, but it was the only reference to that command at all. I went ahead and read that, b/c it was the closest I could find at the time. Nothing came up for ADODB, or else I just overlooked it.

 

[kjv1611]

Ok, I took a moment, and looked at the link you posted, cmmrfrds, and found what I needed for the record count. You'd think I wouldn't have to do this, but what I did was construct an additional SQL statement for counting the records, and then returned the value of that SQL statement as a recordset, and used that to give the correct number of records updated.

Here is the code:

    Dim rs As ADODB.Recordset
    Set rs = cn.Execute("SELECT COUNT(RecordID) FROM Sandbox.[MYDOMAIN\winlogon].tblName)
    MsgBox rs.Fields(0) & " records inserted" & Chr(13) & _
               "into new table, tblName", vbInformation, _ 
               "Operation Complete!"
    lngRecCnt = rs.Fields(0) [green]'To keep the number of 
                'records for another procedure's use[/green]
    rs.Close
    Set rs = Nothing
    cn.Close

 

[cmmrfrds]

Looks like you found a work around. I agree it should have returned the insert count on the first execute. As I get time, I will try to find out why it didn't work.

 

[PHV]

kjv1611, search you local drives for files named ado*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886