I write Excel to pull data from a DB2 database, but now I have to Insert data
into a temp table before I can run the SQL to get the real report data.

The first few in the series insert data into a DB2 database. (in SQL writing
code program like Rapid SQL returns a message like "13620 row(s) affected.".

Then the real reporting query runs to return data.

I can return data, but I have not figured out how to handle the success
message. I even created a temporay sheet for the 'Success' to post. The SQL
jams trying to bring back the results.

Any hints /code would be greatly appreciated.
Thanks!

On Jul 20, 6:46 pm, Candyman
wrote:
I write Excel to pull data from a DB2 database, but now I have to Insert data
into a temp table before I can run the SQL to get the real report data.

The first few in the series insert data into a DB2 database. (in SQL writing
code program like Rapid SQL returns a message like "13620 row(s) affected.".

Then the real reporting query runs to return data.

I can return data, but I have not figured out how to handle the success
message. I even created a temporay sheet for the 'Success' to post. The SQL
jams trying to bring back the results.

Did you try using the execute method instead of openrecordset? If you
posted a bit of the code it would be easier to see what you are using
and suggest corrections.

Peter Richardson

I fathom that Rapid SQL is the tool you're using to develope your SQL
statements ...

The message you see in Rapid SQL is a warning message, not an error
message, so the message is benign.

If you're getting the message in Excel, I'm not clear on how you're
seeing it. Where does it come from? Are you using MS Query by any
chance?

The first few in the series insert data into a DB2 database. (in SQL writing
code program like Rapid SQL returns a message like "13620 row(s) affected.".

Then:
I can return data, but I have not figured out how to handle the success
message. I even created a temporay sheet for the 'Success' to post. The SQL
jams trying to bring back the results.


We need to see the SQL to get a grip on why our SQL would hang.

Also, note that there is a limit to how long a SQL statement can be
that depends upon your driver. If you're attempting to insert over
1000 records in a batch SQL statement you're probably creating strings
that go beyond what either Excel, the DB2 driver, or DB2 can handle.

Respond with a SQL statement if you want more help.

This is VBA that works with DB2. It might be helpful if your new to
SQL in Excel ...

(DBConnection is a Class Module. See code below.)

Option Explicit

Private Const DEFAULT_COMMAND_TIMEOUT = 2000

Public Function GetConnection(db As DBConnection) As ADODB.connection

Dim cn As ADODB.connection
Set cn = New ADODB.connection

On Error GoTo ErrorHandler
cn.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
cn.Open GenerateConnectionString(db), db.userName, db.password

Set GetConnection = cn
Exit Function

ErrorHandler:
Set cn = Nothing
Set GetConnection = Nothing
Exit Function

End Function

Public Function GenerateConnectionString(db As DBConnection) As String
Dim str As String
str = "Provider=SQLOLEDB.1;Data Source=" & db.server & _
";Initial Catalog=" & db.database & ";User ID=" & db.userName
& _
";Password=" & db.password
GenerateConnectionString = str
End Function


'Fetches data, inserts the data into a Worksheet named sheetName

Public Function SelectData(book As Workbook, db As DBConnection, SQL
As String, sheetName As String) As Integer

Dim connection As ADODB.connection
Set connection = CreateObject("ADODB.Connection")
Dim count As Integer
connection.Open DBUtils.GenerateConnectionString(db), db.userName,
db.password

Dim command As ADODB.command
Set command = CreateObject("ADODB.Command")

Set command.ActiveConnection = connection
command.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
command.CommandType = adCmdText
command.CommandText = SQL


Dim sheet As Worksheet
Set sheet = book.Sheets(sheetName)
sheet.Visible = True
sheet.Cells.Clear

Dim rs As ADODB.Recordset
Set rs = command.Execute

Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field

Dim colCount As Integer
colCount = rs.Fields.count

Dim rowIndex, index As Integer
rowIndex = 2
count = 0
While Not rs Is Nothing And Not rs.BOF And Not rs.EOF
For index = 1 To colCount
sheet.Cells(rowIndex, index).Value = rs(index - 1).Value
Next index
rowIndex = rowIndex + 1
count = count + 1
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

Set command = Nothing
SelectData = count
End Function



''''''''' Put this code in a Class Module named DBConnection '''''''''

'DatabaseConnectionInfo
Option Explicit

Dim propDatabase As String
Dim propServer As String
Dim propPort As String
Dim propPrjName As String
Dim propPassword As String

Public Property Get database() As String
database = propDatabase
End Property

Public Property Let database(newDatabase As String)
propDatabase = newDatabase
End Property

Public Property Get server() As String
server = propServer
End Property

Public Property Let server(newServer As String)
propServer = newServer
End Property

Public Property Get Port() As String
Port = propPort
End Property

Public Property Let Port(newPort As String)
propPort = newPort
End Property

Public Property Get userName() As String
userName = propPrjName
End Property

Public Property Let userName(newUserName As String)
propPrjName = newUserName
End Property

Public Property Get password() As String
password = propPassword
End Property

Public Property Let password(newPassword As String)
propPassword = newPassword
End Property

You are correct. Rapid SQL is a querry tool. I normally use it to develop
the SQL statements. I suppose the use of MS Query could be refered to since
that .refresh statement is used. I have used the same report template for
years. It has a "'SQL'" tab that houses variable date and other parameters
that are passed onto the lines of SQL code in lower rows. ( So the actual
SQL resides in range(a20:a100) ) Actually I look for the term "Start SQL" and
append the cell values to the statement until I run into "End SQL". If the
code jams I export the SQL to word ( to paste into Repid for
troubleshooting).

The SQL will show the warning message in Rapid, but jams in Excel. How do i
run the SQL in Excel without it returning the warning message?

I have a feeling that I need to figure our the connection string. Then run
this with an execute statement? I am currently without examples on both of
these points.





" wrote:

I fathom that Rapid SQL is the tool you're using to develope your SQL
statements ...

The message you see in Rapid SQL is a warning message, not an error
message, so the message is benign.

If you're getting the message in Excel, I'm not clear on how you're
seeing it. Where does it come from? Are you using MS Query by any
chance?

The first few in the series insert data into a DB2 database. (in SQL writing
code program like Rapid SQL returns a message like "13620 row(s) affected.".

Then:
I can return data, but I have not figured out how to handle the success
message. I even created a temporay sheet for the 'Success' to post. The SQL
jams trying to bring back the results.


We need to see the SQL to get a grip on why our SQL would hang.

Also, note that there is a limit to how long a SQL statement can be
that depends upon your driver. If you're attempting to insert over
1000 records in a batch SQL statement you're probably creating strings
that go beyond what either Excel, the DB2 driver, or DB2 can handle.

Respond with a SQL statement if you want more help.

This looks pretty good! Let me give it a try. :)

" wrote:

This is VBA that works with DB2. It might be helpful if your new to
SQL in Excel ...

(DBConnection is a Class Module. See code below.)

Option Explicit

Private Const DEFAULT_COMMAND_TIMEOUT = 2000

Public Function GetConnection(db As DBConnection) As ADODB.connection

Dim cn As ADODB.connection
Set cn = New ADODB.connection

On Error GoTo ErrorHandler
cn.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
cn.Open GenerateConnectionString(db), db.userName, db.password

Set GetConnection = cn
Exit Function

ErrorHandler:
Set cn = Nothing
Set GetConnection = Nothing
Exit Function

End Function

Public Function GenerateConnectionString(db As DBConnection) As String
Dim str As String
str = "Provider=SQLOLEDB.1;Data Source=" & db.server & _
";Initial Catalog=" & db.database & ";User ID=" & db.userName
& _
";Password=" & db.password
GenerateConnectionString = str
End Function


'Fetches data, inserts the data into a Worksheet named sheetName

Public Function SelectData(book As Workbook, db As DBConnection, SQL
As String, sheetName As String) As Integer

Dim connection As ADODB.connection
Set connection = CreateObject("ADODB.Connection")
Dim count As Integer
connection.Open DBUtils.GenerateConnectionString(db), db.userName,
db.password

Dim command As ADODB.command
Set command = CreateObject("ADODB.Command")

Set command.ActiveConnection = connection
command.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
command.CommandType = adCmdText
command.CommandText = SQL


Dim sheet As Worksheet
Set sheet = book.Sheets(sheetName)
sheet.Visible = True
sheet.Cells.Clear

Dim rs As ADODB.Recordset
Set rs = command.Execute

Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field

Dim colCount As Integer
colCount = rs.Fields.count

Dim rowIndex, index As Integer
rowIndex = 2
count = 0
While Not rs Is Nothing And Not rs.BOF And Not rs.EOF
For index = 1 To colCount
sheet.Cells(rowIndex, index).Value = rs(index - 1).Value
Next index
rowIndex = rowIndex + 1
count = count + 1
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

Set command = Nothing
SelectData = count
End Function



''''''''' Put this code in a Class Module named DBConnection '''''''''

'DatabaseConnectionInfo
Option Explicit

Dim propDatabase As String
Dim propServer As String
Dim propPort As String
Dim propPrjName As String
Dim propPassword As String

Public Property Get database() As String
database = propDatabase
End Property

Public Property Let database(newDatabase As String)
propDatabase = newDatabase
End Property

Public Property Get server() As String
server = propServer
End Property

Public Property Let server(newServer As String)
propServer = newServer
End Property

Public Property Get Port() As String
Port = propPort
End Property

Public Property Let Port(newPort As String)
propPort = newPort
End Property

Public Property Get userName() As String
userName = propPrjName
End Property

Public Property Let userName(newUserName As String)
propPrjName = newUserName
End Property

Public Property Get password() As String
password = propPassword
End Property

Public Property Let password(newPassword As String)
propPassword = newPassword
End Property

I am getting a "User defined type not defined" error with the line:
Public Function GetConnection(db As DBConnection) As ADODB.connection
highlighted

I am trying to run a test macro but I am not sure how to envoke the 'db'
part of your function using:

Sub test()
Dim str, book, WSname , myDb As String
Dim x
myDB="DMCC1"
book = ActiveWorkbook.Name
WSname = "TEMP"
SQL = "select * from HEIDS.HE_ACAPS_LOCS"
x= SelectData(book, myDB, SQL, WSname)
End Sub

" wrote:

This is VBA that works with DB2. It might be helpful if your new to
SQL in Excel ...

(DBConnection is a Class Module. See code below.)

Option Explicit

Private Const DEFAULT_COMMAND_TIMEOUT = 2000

Public Function GetConnection(db As DBConnection) As ADODB.connection

Dim cn As ADODB.connection
Set cn = New ADODB.connection

On Error GoTo ErrorHandler
cn.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
cn.Open GenerateConnectionString(db), db.userName, db.password

Set GetConnection = cn
Exit Function

ErrorHandler:
Set cn = Nothing
Set GetConnection = Nothing
Exit Function

End Function

Public Function GenerateConnectionString(db As DBConnection) As String
Dim str As String
str = "Provider=SQLOLEDB.1;Data Source=" & db.server & _
";Initial Catalog=" & db.database & ";User ID=" & db.userName
& _
";Password=" & db.password
GenerateConnectionString = str
End Function


'Fetches data, inserts the data into a Worksheet named sheetName

Public Function SelectData(book As Workbook, db As DBConnection, SQL
As String, sheetName As String) As Integer

Dim connection As ADODB.connection
Set connection = CreateObject("ADODB.Connection")
Dim count As Integer
connection.Open DBUtils.GenerateConnectionString(db), db.userName,
db.password

Dim command As ADODB.command
Set command = CreateObject("ADODB.Command")

Set command.ActiveConnection = connection
command.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
command.CommandType = adCmdText
command.CommandText = SQL


Dim sheet As Worksheet
Set sheet = book.Sheets(sheetName)
sheet.Visible = True
sheet.Cells.Clear

Dim rs As ADODB.Recordset
Set rs = command.Execute

Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field

Dim colCount As Integer
colCount = rs.Fields.count

Dim rowIndex, index As Integer
rowIndex = 2
count = 0
While Not rs Is Nothing And Not rs.BOF And Not rs.EOF
For index = 1 To colCount
sheet.Cells(rowIndex, index).Value = rs(index - 1).Value
Next index
rowIndex = rowIndex + 1
count = count + 1
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

Set command = Nothing
SelectData = count
End Function



''''''''' Put this code in a Class Module named DBConnection '''''''''

'DatabaseConnectionInfo
Option Explicit

Dim propDatabase As String
Dim propServer As String
Dim propPort As String
Dim propPrjName As String
Dim propPassword As String

Public Property Get database() As String
database = propDatabase
End Property

Public Property Let database(newDatabase As String)
propDatabase = newDatabase
End Property

Public Property Get server() As String
server = propServer
End Property

Public Property Let server(newServer As String)
propServer = newServer
End Property

Public Property Get Port() As String
Port = propPort
End Property

Public Property Let Port(newPort As String)
propPort = newPort
End Property

Public Property Get userName() As String
userName = propPrjName
End Property

Public Property Let userName(newUserName As String)
propPrjName = newUserName
End Property

Public Property Get password() As String
password = propPassword
End Property

Public Property Let password(newPassword As String)
propPassword = newPassword
End Property

Yo Candyman,

Yeah, once you're on board with DAO you'll wonder why you ever used MS
Query ....


I am getting a "User defined type not defined" error with the line:
Public Function GetConnection(db As DBConnection) As ADODB.connection
highlighted

You have to determine if Excel can't find DBConnection or ADODB.

1. Confirm that you post the DBConnection information into a class
module.(In your project you should have MS Excel Objects,
Forms,Modules, Class Modules (and DBConnection should be a Class
Module).

2. Confirm that Excel is referencing "Microsoft DAO 3.6 Object
Library" or so 3.51 - whatever version you've got.

I am trying to run a test macro but I am not sure how to envoke the 'db' part of your function using:

Try:

Public Function GetConnectionInfo() as DBConnection
Dim db as New DBConnection
db.server = "yourserver"
db.database = "yourdatabase"
db.userName = "yourusername"
db.password = "yourPassword"
Set GetConnectionInfo = db
End Function

Sub test()
Dim str, book, WSname , myDb As String
Dim myDB as DBConnection
Dim x as Integer
myDB = GetConnectionInfo()
book = ActiveWorkbook.Name
WSname = "TEMP"
SQL = "select * from HEIDS.HE_ACAPS_LOCS"
x= SelectData(book, myDB, SQL, WSname)
End Sub

Thanks for all your help. Sorry for the delayed response..

Have a great day!

" wrote:

Yo Candyman,

Yeah, once you're on board with DAO you'll wonder why you ever used MS
Query ....


I am getting a "User defined type not defined" error with the line:
Public Function GetConnection(db As DBConnection) As ADODB.connection
highlighted

You have to determine if Excel can't find DBConnection or ADODB.

1. Confirm that you post the DBConnection information into a class
module.(In your project you should have MS Excel Objects,
Forms,Modules, Class Modules (and DBConnection should be a Class
Module).

2. Confirm that Excel is referencing "Microsoft DAO 3.6 Object
Library" or so 3.51 - whatever version you've got.

I am trying to run a test macro but I am not sure how to envoke the 'db' part of your function using:

Try:

Public Function GetConnectionInfo() as DBConnection
Dim db as New DBConnection
db.server = "yourserver"
db.database = "yourdatabase"
db.userName = "yourusername"
db.password = "yourPassword"
Set GetConnectionInfo = db
End Function

Sub test()
Dim str, book, WSname , myDb As String
Dim myDB as DBConnection
Dim x as Integer
myDB = GetConnectionInfo()
book = ActiveWorkbook.Name
WSname = "TEMP"
SQL = "select * from HEIDS.HE_ACAPS_LOCS"
x= SelectData(book, myDB, SQL, WSname)
End Sub