berlinhammer
Board Regular
- Joined
- Jan 30, 2009
- Messages
- 187
Hello All,
I am using the following code to establish an ODBC connection from Excel 2003 to an Oracle database and return an ADO recordset to the workbook.
My connection string is a DSN as you can see.
I am having trouble because one of the fields contains a long text entry and googling leads me to believe that the character limit for ADO recordsets is 255, way short of what I need. Limiting the string length to < 255 is stopping my macro from crashing but I would prefer to increase the limit and receive all off my text as I am losing a lot of information at the moment.
Very grateful for any help!
Jon
I am using the following code to establish an ODBC connection from Excel 2003 to an Oracle database and return an ADO recordset to the workbook.
My connection string is a DSN as you can see.
I am having trouble because one of the fields contains a long text entry and googling leads me to believe that the character limit for ADO recordsets is 255, way short of what I need. Limiting the string length to < 255 is stopping my macro from crashing but I would prefer to increase the limit and receive all off my text as I am losing a lot of information at the moment.
Very grateful for any help!
Jon
Code:
Option Explicit
Private Function GenerateSQLString() As String
Dim l As Long
Dim rng As Range
Dim str As String
Set rng = Sheets("SQL").Range("rngSQL")
For l = 1 To rng.Cells.Count
If Len(rng.Cells(l).Value) > 0 Then
str = str & rng.Cells(l).Value & Chr(10)
End If
Next l
GenerateSQLString = str
End Function
Public Sub GetData()
On Error GoTo ErrHandler:
Dim myFile, fnum
Dim conn As New ADODB.Connection
Dim rsRecords As New ADODB.Recordset
Dim connString As String
Dim sqlString As String
Dim iCols As Long
Dim iRows As Long
connString = "DSN=intra;Uid=*****;Pwd=******
sqlString = GenerateSQLString
conn.ConnectionTimeout = 30
conn.CommandTimeout = 0
conn.Open connString
rsRecords.CursorLocation = adUseClient
rsRecords.CursorType = adOpenStatic
rsRecords.Open sqlString, conn
If conn.State = adStateOpen Then
On Error Resume Next
ThisWorkbook.Names("rngPivotData").Delete
On Error GoTo 0
Sheets("Data").Cells(10, 2).CurrentRegion.ClearContents
iRows = rsRecords.RecordCount
Worksheets("Data").Range("K2").Value = Now()
Worksheets("Data").Range("L2").Value = iRows
Worksheets("Data").Range("B10").CopyFromRecordset rsRecords
For iCols = 0 To rsRecords.Fields.Count - 1
Worksheets("Data").Range("B9").Cells(1, iCols + 1).Value = rsRecords.Fields(iCols).Name
Next
ThisWorkbook.Names.Add "rngPivotData", Sheets("Data").Cells(10, 2).CurrentRegion
Else
MsgBox "no connection"
End If
rsRecords.Close
Set rsRecords = Nothing
conn.Close
Set conn = Nothing
ErrHandler:
If conn.Errors.Count > 0 Then
MsgBox conn.Errors(0).Description
End If
End Sub