Fellow Progress Users
I am new in the ODBC arena. We have recently upgraded from Progress 8.2C to Progress 9.1D. Prior to upgrade we were running an Excel Macro using ODBC to pull data from the database. After upgrading the database to 9.1D, I installed Merant 3.60 32 Bit Progress SQL92 v9.1D on our Windows 2000, which is where we had ODBC set up before. I can do a test connect, but can not run the old macro. I get
Run-time error '1004'
General ODBC Error
lThe debugger shows the error at the
trotherquery.Refresh (NO)
I know nothing about sql, but this is what my current sql text looks like.
' Builds a matirx from data on various sheets
Dim connstring As String
Dim trotherquery As QueryTable
Dim trqryparam As Parameter
Dim trSrcRange As Range
Dim trTgtRange As Range
Dim sqlstring As String
Dim cellcont As String
Dim ltest As Boolean
Dim srcRow As Integer 'row location of prod names
Dim srcCol As Integer 'column location of prod names
Public Sub main()
srcRow = 1
srcCol = 1
connstring = "ODBC;DSN=nxtlive;UID=;PWD=;Database=nxt"
sqlstring = "select stndcost from icsw where cono = " + Trim(Str(Worksheets("sheet1").Cells(1, 2))) + " and whse = '" + Trim(Worksheets("sheet1").Cells(2, 2)) + "' and prod = ''"
Worksheets("Sheet1").Activate
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A5"), Sql:=sqlstring)
End With
Set trotherquery = ActiveSheet.QueryTables(1)
trotherquery.Sql = "select stndcost from icsw where cono = " + Trim(Str(Worksheets("sheet1").Cells(1, 2))) + " and whse = '" + Trim(Worksheets("sheet1").Cells(2, 2)) + "' and (prod = ?)"
Set trqryparam = trotherquery.Parameters.Add("prod Parameter", xlParamTypeVarChar)
For srcRow = 1 To 27
For srcCol = 1 To 12
With Worksheets("Sheet2")
Set trSrcRange = Range(.Cells(srcRow, srcCol), .Cells(srcRow, srcCol))
End With
Worksheets("SHEET1").Range("A5", "A6").Clear
Worksheets("sheet1").Cells(4, 1) = srcRow
Worksheets("sheet1").Cells(4, 2) = srcCol
With Worksheets("Sheet3")
Set trTrgRange = Range(.Cells(srcRow + 1, srcCol + 1), .Cells(srcRow + 1, srcCol + 1))
trTrgRange.ClearContents
End With
Call sqltext
If Trim(cellcont) <> "" Then
' trotherquery.Destination = trTrgRange
trqryparam.SetParam xlRange, trSrcRange
trotherquery.Refresh (NO)
On Error Resume Next
Err.Clear
Worksheets("Sheet3").Cells(srcRow + 1, srcCol + 1) = _
Worksheets("Sheet1").Cells(6, 1)
End If
Next
Next
Worksheets("Sheet3").Activate
End Sub
Sub sqltext()
cellcont = Worksheets("Sheet2").Cells(srcRow, srcCol).Value
sqlstring = "select stndcost from icsw where cono = 999 and whse = '1' and prod = '" + Trim(cellcont) + "'"
End Sub
Would appreciate any help someone could give me.
Thanks,
WJindra
ARS
wjindra@ars-net.com
I am new in the ODBC arena. We have recently upgraded from Progress 8.2C to Progress 9.1D. Prior to upgrade we were running an Excel Macro using ODBC to pull data from the database. After upgrading the database to 9.1D, I installed Merant 3.60 32 Bit Progress SQL92 v9.1D on our Windows 2000, which is where we had ODBC set up before. I can do a test connect, but can not run the old macro. I get
Run-time error '1004'
General ODBC Error
lThe debugger shows the error at the
trotherquery.Refresh (NO)
I know nothing about sql, but this is what my current sql text looks like.
' Builds a matirx from data on various sheets
Dim connstring As String
Dim trotherquery As QueryTable
Dim trqryparam As Parameter
Dim trSrcRange As Range
Dim trTgtRange As Range
Dim sqlstring As String
Dim cellcont As String
Dim ltest As Boolean
Dim srcRow As Integer 'row location of prod names
Dim srcCol As Integer 'column location of prod names
Public Sub main()
srcRow = 1
srcCol = 1
connstring = "ODBC;DSN=nxtlive;UID=;PWD=;Database=nxt"
sqlstring = "select stndcost from icsw where cono = " + Trim(Str(Worksheets("sheet1").Cells(1, 2))) + " and whse = '" + Trim(Worksheets("sheet1").Cells(2, 2)) + "' and prod = ''"
Worksheets("Sheet1").Activate
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A5"), Sql:=sqlstring)
End With
Set trotherquery = ActiveSheet.QueryTables(1)
trotherquery.Sql = "select stndcost from icsw where cono = " + Trim(Str(Worksheets("sheet1").Cells(1, 2))) + " and whse = '" + Trim(Worksheets("sheet1").Cells(2, 2)) + "' and (prod = ?)"
Set trqryparam = trotherquery.Parameters.Add("prod Parameter", xlParamTypeVarChar)
For srcRow = 1 To 27
For srcCol = 1 To 12
With Worksheets("Sheet2")
Set trSrcRange = Range(.Cells(srcRow, srcCol), .Cells(srcRow, srcCol))
End With
Worksheets("SHEET1").Range("A5", "A6").Clear
Worksheets("sheet1").Cells(4, 1) = srcRow
Worksheets("sheet1").Cells(4, 2) = srcCol
With Worksheets("Sheet3")
Set trTrgRange = Range(.Cells(srcRow + 1, srcCol + 1), .Cells(srcRow + 1, srcCol + 1))
trTrgRange.ClearContents
End With
Call sqltext
If Trim(cellcont) <> "" Then
' trotherquery.Destination = trTrgRange
trqryparam.SetParam xlRange, trSrcRange
trotherquery.Refresh (NO)
On Error Resume Next
Err.Clear
Worksheets("Sheet3").Cells(srcRow + 1, srcCol + 1) = _
Worksheets("Sheet1").Cells(6, 1)
End If
Next
Next
Worksheets("Sheet3").Activate
End Sub
Sub sqltext()
cellcont = Worksheets("Sheet2").Cells(srcRow, srcCol).Value
sqlstring = "select stndcost from icsw where cono = 999 and whse = '1' and prod = '" + Trim(cellcont) + "'"
End Sub
Would appreciate any help someone could give me.
Thanks,
WJindra
ARS
wjindra@ars-net.com
