issue with ODBC connection to DB2

[Avi]

Hi,

I am connecting to DB2 from Excel using IBM OBDC driver. I am able to
connect and getting the data but.. there is a window that pops up
asking me to select the database.. is there a way to get rid of it

here is my code.
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={IBM DB2 ODBC
DRIVER};Hostname=udb_db2t.xxxxx;Port=2001;DB=DB2T;Uid=xxx;Pwd=xxxxx;"
_
, Destination:=Range("A1"))
.CommandText = strQueryText
.Name = strQueryName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.EnableRefresh = True
.EnableEditing = False
.Refresh BackgroundQuery:=False --- this row pops the
window...
End With


I also see the error message in the window "SQL1013N The database
alias name or database name "" could not be found. SQLSTATE=42705"

looks like i am not defining the database properly..

Can someone please help ..

Thanks
Avinash

 

[Jim Thomlinson]

What is the value of strQueryText
in the line
..CommandText = strQueryText

 

[Avi]

Thanks for the reply Jim !

strQueryText is defined as String and I am getting it from the
Spreadsheet cell.

query i am trying to execute is simple - "Select Item from XYZ.ITEM
where dept = 200 "

Here is the complete procedure.
-----------------------------------------------------------------------------
Private Sub CreateQuery(inQueryIdx As Integer)
'
Dim strQueryName As String, strQueryText As String
Dim strServer, strDatabase, strUid, strPwd As String
Dim xSheet As Worksheet

strQueryName = [querylist].Cells(inQueryIdx, 1)
strQueryText = [querylist].Cells(inQueryIdx, 2)

If strQueryName = "" Or strQueryText = "" Then
Exit Sub
End If

strServer = [querylist].Cells(inQueryIdx, 3)
strDatabase = [querylist].Cells(inQueryIdx, 4)
strUid = [querylist].Cells(inQueryIdx, 5)
strPwd = [querylist].Cells(inQueryIdx, 6)

FindActivateSheet (strQueryName)
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={IBM DB2 ODBC
DRIVER};Hostname=udb_db2t.xxx.com;Port=2001;DB=DB2T;Uid=xxx;Pwd=xxx;"
_
, Destination:=Range("A1"))
.CommandText = strQueryText
.Name = strQueryName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.EnableRefresh = True
.EnableEditing = False
.Refresh BackgroundQuery:=False
End With

Set xSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'xSheet.Columns(9).WrapText = True
Set xSheet = Nothing

End Sub

 

[Tom Ogilvy]

Driver={IBM DB2 ODBC
DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234;Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;

is the format I found on the WEB. You don't have a Database=myDatabase
clause in yours.

That would be consistent with the error you are getting I would think.

--
Regards,
Tom Ogilvy

Thanks for the reply Jim !

strQueryText is defined as String and I am getting it from the
Spreadsheet cell.

query i am trying to execute is simple - "Select Item from XYZ.ITEM
where dept = 200 "

Here is the complete procedure.
-----------------------------------------------------------------------------
Private Sub CreateQuery(inQueryIdx As Integer)
'
Dim strQueryName As String, strQueryText As String
Dim strServer, strDatabase, strUid, strPwd As String
Dim xSheet As Worksheet

strQueryName = [querylist].Cells(inQueryIdx, 1)
strQueryText = [querylist].Cells(inQueryIdx, 2)

If strQueryName = "" Or strQueryText = "" Then
Exit Sub
End If

strServer = [querylist].Cells(inQueryIdx, 3)
strDatabase = [querylist].Cells(inQueryIdx, 4)
strUid = [querylist].Cells(inQueryIdx, 5)
strPwd = [querylist].Cells(inQueryIdx, 6)

FindActivateSheet (strQueryName)
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={IBM DB2 ODBC
DRIVER};Hostname=udb_db2t.xxx.com;Port=2001;DB=DB2T;Uid=xxx;Pwd=xxx;"
_
, Destination:=Range("A1"))
.CommandText = strQueryText
.Name = strQueryName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.EnableRefresh = True
.EnableEditing = False
.Refresh BackgroundQuery:=False
End With

Set xSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'xSheet.Columns(9).WrapText = True
Set xSheet = Nothing

End Sub
-----------------------------------------------------------------

What is the value of strQueryText
in the line
.CommandText = strQueryText

 

[Avi]

Hi Tim.

I had used the database parameter before, but was still getting the
popup. How ever you were able to point out what I was missing.
it was Protocol=TCPIP.. my issue was resolved once i added the
Protocol in the connect string.

Thanks for your help,
Avinash

Driver={IBM DB2 ODBC
DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234;Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;

is the format I found on the WEB. You don't have a Database=myDatabase
clause in yours.

That would be consistent with the error you are getting I would think.

--
Regards,
Tom Ogilvy

Thanks for the reply Jim !

strQueryText is defined as String and I am getting it from the
Spreadsheet cell.

query i am trying to execute is simple - "Select Item from XYZ.ITEM
where dept = 200 "

Here is the complete procedure.
-----------------------------------------------------------------------------
Private Sub CreateQuery(inQueryIdx As Integer)
'
Dim strQueryName As String, strQueryText As String
Dim strServer, strDatabase, strUid, strPwd As String
Dim xSheet As Worksheet

strQueryName = [querylist].Cells(inQueryIdx, 1)
strQueryText = [querylist].Cells(inQueryIdx, 2)

If strQueryName = "" Or strQueryText = "" Then
Exit Sub
End If

strServer = [querylist].Cells(inQueryIdx, 3)
strDatabase = [querylist].Cells(inQueryIdx, 4)
strUid = [querylist].Cells(inQueryIdx, 5)
strPwd = [querylist].Cells(inQueryIdx, 6)

FindActivateSheet (strQueryName)
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={IBM DB2 ODBC
DRIVER};Hostname=udb_db2t.xxx.com;Port=2001;DB=DB2T;Uid=xxx;Pwd=xxx;"
_
, Destination:=Range("A1"))
.CommandText = strQueryText
.Name = strQueryName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.EnableRefresh = True
.EnableEditing = False
.Refresh BackgroundQuery:=False
End With

Set xSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'xSheet.Columns(9).WrapText = True
Set xSheet = Nothing

End Sub
-----------------------------------------------------------------