Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
issue with ODBC connection to DB2
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=DB2 T;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 ----------------------------------------------------------------- On Aug 2, 11:36 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: What is the value of strQueryText in the line .CommandText = strQueryText -- HTH... Jim Thomlinson "Avi" wrote: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
issue with ODBC connection to DB2
Driver={IBM DB2 ODBC
DRIVER};Database=myDataBase;Hostname=myServerAddre ss;Port=1234;Protocol=TCPIP;Uid=myUsername;Pwd=myP assword; 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 "Avi" wrote: 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=DB2 T;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 ----------------------------------------------------------------- On Aug 2, 11:36 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: What is the value of strQueryText in the line .CommandText = strQueryText -- HTH... Jim Thomlinson "Avi" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
issue with ODBC connection to DB2
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 On Aug 2, 2:02 pm, Tom Ogilvy wrote: Driver={IBM DB2 ODBC DRIVER};Database=myDataBase;Hostname=myServerAddre ss;Port=1234;Protocol=TCPIP;Uid=myUsername;Pwd=myP assword; 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 "Avi" wrote: 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=DB2 T;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 ----------------------------------------------------------------- On Aug 2, 11:36 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: What is the value of strQueryText in the line .CommandText = strQueryText -- HTH... Jim Thomlinson "Avi" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC Connection | Excel Programming | |||
ODBC connection question | Excel Programming | |||
ODBC Connection | Excel Programming | |||
ODBC connection by udl | Excel Programming |