Pratts
07-01-2015, 11:28 PM
Hello Everyone,
Greeting of the Day!!
I am into a strange situation as my VBA code is able to connect to Sybase v 15.7 but the result generated from the query are strange.For 164 rows it is picking up correct results but for 165th row it is picking half correct result anf half junk and no more results there on .
Below is the code if someone could help.
Dim dbVMFG5 As ADODB.Connection
'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String
Dim strserver As String
Dim snpData As ADODB.Recordset
Set snpData = New ADODB.Recordset
'***
Dim i As Integer
Dim intResult As Integer
Dim strSQL As String
Dim lngRow As Long
On Error Resume Next
strDatabase = "Servername:Portno"
strUserName = "username"
strPassword = "password"
strserver = "dbinstance"
Set dbVMFG5 = New ADODB.Connection
dbVMFG5.ConnectionString = "Provider=ASEOLEDB;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Initial Catalog=" & strserver & ";Password=" & strPassword & "; Charset=iso_1;EnableBulkLoad=1;FetchArraySize=10050;ChunkSize=100000;FetchS ize=20000;"
dbVMFG5.ConnectionTimeout = 240
dbVMFG5.CursorLocation = adUseClient
dbVMFG5.Open
If (dbVMFG5.State <> 1) Or (Err <> 0) Then
intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
Else
snpData.Open "Set row count 80000", dbVMFG5
strSQL = " SELECT " & vbCrLf
strSQL = strSQL & "CONVERT(VARCHAR,A.name)+ '.' + CONVERT(VARCHAR,B.name) + '.' + CONVERT(VARCHAR,C.name) " & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " sysobjects A" & vbCrLf
strSQL = strSQL & "INNER JOIN" & vbCrLf
strSQL = strSQL & " syscolumns B" & vbCrLf
strSQL = strSQL & "on" & vbCrLf
strSQL = strSQL & " A.id = B.id" & vbCrLf
strSQL = strSQL & "INNER JOIN" & vbCrLf
strSQL = strSQL & " systypes C" & vbCrLf
strSQL = strSQL & "on" & vbCrLf
strSQL = strSQL & " C.type = B.type" & vbCrLf
strSQL = strSQL & "AND" & vbCrLf
strSQL = strSQL & " C.usertype = B.usertype" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " A.type = 'U'" & vbCrLf
snpData.Open strSQL, dbVMFG5
'Add a new worksheet to the new workbook, add after the last sheet
ActiveWorkbook.Sheets.Add
ActiveWorkbook.ActiveSheet.Name = "Sheet"
ActiveWorkbook.ActiveSheet.Range("A1").Value = strSQL
If snpData.State = 1 Then
lngRow = 0
For i = 0 To snpData.Fields.Count - 1
ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name
Next i
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset snpData
'Auto-fit up to 26 columns
ActiveSheet.Columns("A:" & Chr(64 + snpData.Fields.Count)).AutoFit
End If
snpData.Close
dbVMFG5.Close
End If
Thanks!!
Pratts
Greeting of the Day!!
I am into a strange situation as my VBA code is able to connect to Sybase v 15.7 but the result generated from the query are strange.For 164 rows it is picking up correct results but for 165th row it is picking half correct result anf half junk and no more results there on .
Below is the code if someone could help.
Dim dbVMFG5 As ADODB.Connection
'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String
Dim strserver As String
Dim snpData As ADODB.Recordset
Set snpData = New ADODB.Recordset
'***
Dim i As Integer
Dim intResult As Integer
Dim strSQL As String
Dim lngRow As Long
On Error Resume Next
strDatabase = "Servername:Portno"
strUserName = "username"
strPassword = "password"
strserver = "dbinstance"
Set dbVMFG5 = New ADODB.Connection
dbVMFG5.ConnectionString = "Provider=ASEOLEDB;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Initial Catalog=" & strserver & ";Password=" & strPassword & "; Charset=iso_1;EnableBulkLoad=1;FetchArraySize=10050;ChunkSize=100000;FetchS ize=20000;"
dbVMFG5.ConnectionTimeout = 240
dbVMFG5.CursorLocation = adUseClient
dbVMFG5.Open
If (dbVMFG5.State <> 1) Or (Err <> 0) Then
intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
Else
snpData.Open "Set row count 80000", dbVMFG5
strSQL = " SELECT " & vbCrLf
strSQL = strSQL & "CONVERT(VARCHAR,A.name)+ '.' + CONVERT(VARCHAR,B.name) + '.' + CONVERT(VARCHAR,C.name) " & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " sysobjects A" & vbCrLf
strSQL = strSQL & "INNER JOIN" & vbCrLf
strSQL = strSQL & " syscolumns B" & vbCrLf
strSQL = strSQL & "on" & vbCrLf
strSQL = strSQL & " A.id = B.id" & vbCrLf
strSQL = strSQL & "INNER JOIN" & vbCrLf
strSQL = strSQL & " systypes C" & vbCrLf
strSQL = strSQL & "on" & vbCrLf
strSQL = strSQL & " C.type = B.type" & vbCrLf
strSQL = strSQL & "AND" & vbCrLf
strSQL = strSQL & " C.usertype = B.usertype" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " A.type = 'U'" & vbCrLf
snpData.Open strSQL, dbVMFG5
'Add a new worksheet to the new workbook, add after the last sheet
ActiveWorkbook.Sheets.Add
ActiveWorkbook.ActiveSheet.Name = "Sheet"
ActiveWorkbook.ActiveSheet.Range("A1").Value = strSQL
If snpData.State = 1 Then
lngRow = 0
For i = 0 To snpData.Fields.Count - 1
ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name
Next i
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset snpData
'Auto-fit up to 26 columns
ActiveSheet.Columns("A:" & Chr(64 + snpData.Fields.Count)).AutoFit
End If
snpData.Close
dbVMFG5.Close
End If
Thanks!!
Pratts