SQL Query giving crazy results when executre from VBA ( Excel )

[Pratts]

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

[SamT]

I wonder why nobody wants to show us all the code on the page when they can't figure out the problem on their own.

[Pratts]

Thansk for your reply SamT,

This is all the code that I have written, Only Server name,Portno and passwords are changed as they are confidential.

Thanks!!
Pratts



I wonder why nobody wants to show us all the code on the page when they can't figure out the problem on their own.

[SamT]

You mean that you haven't yet put "Sub SubName()" at the beginning and "End Sub" at the bottom of your sub?

The best way to post your code is to put the cursor on the code page and press Ctrl+A then press Ctrl+C, then in the VBA message editor, click the # icon on the Menu and press Ctrl+V.

For example, with all the code, If we see that you aren't using Option Explicit at the top of your code page, the first thing we will do is paste your code into a page that uses it and check for typos.

If you don't include the "Sub... End Sub, how can we trust that you didn't leave out something you thought didn't matter as implied by this code:
'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


I'm not real familiar with SQL, so take this for what it's worth.

The first thing I would do is move the "strSQL" and "dbVMFG5.ConnectionString" assignments to before all "Set ... = New ..." assignments.

You have two instances of "snpData.Open" :dunno

You can try adding "DoEvents" after the three ".Open" statements

Finally, use Debug >> Compile and save the book before running the procedure.

[JKwan]

to start, remove the "On Error Resume Next" line, you will never find out what had occured

[Pratts]

It was just because of wrong driver ....
Updated the driver and it worked fine.

Thanks everyone for your help.

Regrads
Pratts




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

[SamT]

Thank you for posting the solution. Many of our future guest may find it helpful.