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