Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Field Size issue
I have been using this old for a while and now I've discovered an
error. What it does is extract data from a Lotus Notes database (*.nsf), which I have no control over, and places the selected info into an Excel file. Problem I'm having is that its truncating the data it's grabbing - So the question, with this code, how do I determine the field size and ensure I "collecting" all of the information within that field? I'm a "good novice" - So any advice would help... -Thanks Steve Public Sub ECN_Status_Script() Dim myServerName As String Dim myDbName As String Dim strSQL As String Dim End_Row As Long Dim i As Integer Dim strTableNames() As Variant Dim oConn As ADODB.Connection Dim rs As ADODB.Recordset strTableNames = Array("ArlManEcn", "Cum1ManEcn", "Cum2ManEcn", "DanManEcn", "ECN", "PipManEcn", "ProdPlan", "ReyManEcn", "RosManEcn", "SalManEcn", "SPWECN") myServerName = "ARLNotes1/USMARINE" myDbName = "ECNWorkf.nsf" 'Connect to a Database Set oConn = CreateObject("ADODB.Connection") oConn.ConnectionString = "DRIVER={Lotus NotesSQL Driver (*.nsf)};SERVER=" & myServerName & ";DATABASE=" & myDbName oConn.Open 'Create a recordset object Set rs = CreateObject("ADODB.RecordSet") rs.ActiveConnection = oConn rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.LockType = adLockPessimistic 'Turn off filter if on. If Application.Sheets("ECN Status").FilterMode = True Then Application.ActiveSheet.ShowAllData End If End_Row = 4 Range("A4").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear DoEvents For i = 0 To UBound(strTableNames) strSQL = "SELECT ACProcessName, EcnNumber, Title, ACOriginator, ACSubmittedDate, ACCurrentApprovers, ACAssignedDate_d " _ & "FROM " & strTableNames(i) & " WHERE (((" & strTableNames(i) & ".ACStatus)='In Process'));" rs.Open strSQL Worksheets("ECN Status").Range("A" & End_Row).CopyFromRecordset rs Range("A65536").Select Selection.End(xlUp).Select End_Row = ActiveCell.Row rs.Close Next Set rs = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Size Issue | Excel Discussion (Misc queries) | |||
Issue with mail merge date field | Excel Programming | |||
Column size issue | Excel Programming | |||
Issue with form field lengths | Excel Programming | |||
pivot field size (column field) | Excel Programming |