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:
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.
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