Please help with SQL string using ADO

deadseasquirrels

Board Regular
Joined
Dec 30, 2004
Messages
232
I am getting a little bit closer to getting my macro to connect to my database. I can get it to connect using ADO and a short SQL string, but when I try to enter in a long string like below:
Code:
Private Sub SQLTest()

    Dim dbsNDF As New ADODB.connection
    Dim connString As String
    Dim dbRS As Object
    Dim sqlString As String
    
    connString = "Provider=MSDAORA.1;Password=[pwd];User ID=[id];Data Source=DKARCH1.WORLD;Persist Security Info=True"
    dbsNDF.connectionString = connString
    dbsNDF.Open

    sqlString = "SELECT  SO.PURCHASE.STATUS, SO.STUDENT.ENTRY_DATE, SO.STAFF.USER_NAME, " & _
        "SO.PURCHASE.CODE, SO.STUDENT.FIRST_NAME,SO.STUDENT.LAST_NAME " & _
        "FROM SO.STUDENT, SO.PURCHASE"

    MsgBox (sqlString)
    Set dbRS = dbsNDF.Execute(sqlString)
    
    Cells(106, 1).CopyFromRecordset dbRS
    
    If dbRS.EOF And dbRS.BOF Then
        MsgBox "End of File"
        GoTo CleanUpAndClose
    Else

        'If dbRS.Supports(adMovePrevious) Then
        '    MsgBox ("wilson, in adMoveFirst")
        'End If
        dbRS.MoveFirst
        Do While Not dbRS.EOF
            If dbRS.RecordCount = 0 Then
                MsgBox ("empty")
            End If
            MsgBox (dbRS.Fields(1).Value)
            dbRS.MoveNext
        Loop
    End If
    
CleanUpAndClose:
    dbRS.Close
    dbsNDF.Close
    Set dbRS = Nothing
    Set dbsNDF = Nothing
End Sub
I get an error. So I know everything else works, I just can't figure out why my string doesn't work. My actual string is actual much longer than this one but I cut it down hoping it would help me figure out what was wrong, but I still can't figure it out from this part. If anybody can help, I would greatly appreciate it. Thank you.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

kieran

Active Member
Joined
Oct 27, 2002
Messages
429
The SQL statement has no join between SO.STUDENT and SO.PURCHASE. YOu will therefore get a cartesian join. THis may not be supported by the database.
Try adding a join in a WHERE clause and see if it makes a difference.

eg

SELECT SO.PURCHASE.STATUS, SO.STUDENT.ENTRY_DATE, SO.STAFF.USER_NAME, " & _
"SO.PURCHASE.CODE, SO.STUDENT.FIRST_NAME,SO.STUDENT.LAST_NAME " & _
"FROM SO.STUDENT, SO.PURCHASE"
 

deadseasquirrels

Board Regular
Joined
Dec 30, 2004
Messages
232
I'll try to do that, but i had a WHERE statement in there originally and I took it out for simplification reasons. I'll put it back in but I don't think that is the problem because I used this string

Code:
SELECT v_intg_status_all.DATABASE, v_intg_status_all.DK_BOX, v_intg_status_all.INT_THRU, v_intg_status_all.BOB_INT_THRU" & Chr(13) & "" & Chr(10) & "FROM so.v_intg_status_all
and it worked just fine. I'll try it though and see how it goes.
 

deadseasquirrels

Board Regular
Joined
Dec 30, 2004
Messages
232
Well I added the WHERE function as shown below:
Code:
Private Sub SQLTest()

    Dim dbsNDF As New ADODB.connection
    Dim connString As String
    Dim dbRS As Object
    Dim sqlString As String
    
    connString = "Provider=MSDAORA.1;Password=[pwd];User ID=[uid];Data Source=DKARCH1.WORLD;Persist Security Info=True"
    dbsNDF.connectionString = connString
    dbsNDF.Open

    sqlString = "SELECT  SO.PURCHASE.STATUS, SO.STUDENT.ENTRY_DATE, SO.STAFF.USER_NAME, " & _
        "SO.PURCHASE.CODE, SO.STUDENT.FIRST_NAME,SO.STUDENT.LAST_NAME " & _
        "FROM SO.STUDENT, SO.PURCHASE " & _
        "WHERE   SO.STUDENT.ENTRY_USER_ID = SO.STAFF.ID " & _
        "AND     SO.PURCHASE.PRODUCT_ID = SO.PRODUCT.ID" & _
        "AND     SO.PRODUCT_GROUP.ID = SO.PRODUCT.PROD_GROUP_ID" & _
        "AND     SO.STUDENT.SCHOOL_ID = SO.SCHOOL.ID"

    MsgBox (sqlString)
    Set dbRS = dbsNDF.Execute(sqlString)
    
    Cells(106, 1).CopyFromRecordset dbRS
    
    If dbRS.EOF And dbRS.BOF Then
        MsgBox "End of File"
        GoTo CleanUpAndClose
    Else

        'If dbRS.Supports(adMovePrevious) Then
        '    MsgBox ("wilson, in adMoveFirst")
        'End If
        dbRS.MoveFirst
        Do While Not dbRS.EOF
            If dbRS.RecordCount = 0 Then
                MsgBox ("empty")
            End If
            MsgBox (dbRS.Fields(1).Value)
            dbRS.MoveNext
        Loop
    End If
    
CleanUpAndClose:
    dbRS.Close
    dbsNDF.Close
    Set dbRS = Nothing
    Set dbsNDF = Nothing
End Sub
but still it doesn't work. The error says "The command is not properly ended. What now, I feel like I'm really close but I can't finish it off.

The strange thing is, I use this same exact script in another program (Golden) and I hit the same database and it works fine.
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top