Hi:
What I want to do is to create a SQL statement programatically, execute it, and insert the results in a Excel worksheet (We are using Informix and Excel 2002 (XP Pro)). This is my primary goal, and it does not must be done the way I am doing it right now. Any other working way is welcome.
The use of VB is not very common to me, and this is what I have done until now:
Sub OpenConnectionX()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim qryTest As QueryDef
Dim rsCursor As Recordset
Dim ws As Object
Dim sQuery As String
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Prod", dbDriverPrompt, True, _
"ODBC;DATABASE=parts;UID=;PWD=;DSN=Production"
' This is a SAMPLE Query.
sQuery = "Select * From Engine Where ENG_LITERS = '5.0L'"
Set qryTest = conPubs.CreateQueryDef("Cons", sQuery)
Set rsCursor = qryTest.OpenRecordset()
Set ws = Sheets(2)
ws.Range("A3".CopyFromRecordset rsCursor
rsCursor.Close
conPubs.Close
wrkODBC.Close
End Sub
The script runs well. The problem is that the information inserted in the worksheet is unreadable. Instead of the data obtained from the DB, there are lots of squares and "t-like" signs (those we used in single margins in MS-DOS programs).
I have tried with the QueryTables.Add and when I use a SQL statement as the source it runs well, but if I use a Recordset as the source I got the same result as in the CopyFromRecordset: unreadable information.
I could use the SQL statement as the source, but since they are a lot of queries that we have to run (again and again and again) it wont be a realiable option, because each and every command asks for information to establish a connection with the DB server.
I want to ask for the DB connection's information at the begining of the script and use that connection throught the rest of the script.
Any help, will be welcome.
Regards,
Gregorio
What I want to do is to create a SQL statement programatically, execute it, and insert the results in a Excel worksheet (We are using Informix and Excel 2002 (XP Pro)). This is my primary goal, and it does not must be done the way I am doing it right now. Any other working way is welcome.
The use of VB is not very common to me, and this is what I have done until now:
Sub OpenConnectionX()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim qryTest As QueryDef
Dim rsCursor As Recordset
Dim ws As Object
Dim sQuery As String
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Prod", dbDriverPrompt, True, _
"ODBC;DATABASE=parts;UID=;PWD=;DSN=Production"
' This is a SAMPLE Query.
sQuery = "Select * From Engine Where ENG_LITERS = '5.0L'"
Set qryTest = conPubs.CreateQueryDef("Cons", sQuery)
Set rsCursor = qryTest.OpenRecordset()
Set ws = Sheets(2)
ws.Range("A3"
.CopyFromRecordset rsCursorrsCursor.Close
conPubs.Close
wrkODBC.Close
End Sub
The script runs well. The problem is that the information inserted in the worksheet is unreadable. Instead of the data obtained from the DB, there are lots of squares and "t-like" signs (those we used in single margins in MS-DOS programs).
I have tried with the QueryTables.Add and when I use a SQL statement as the source it runs well, but if I use a Recordset as the source I got the same result as in the CopyFromRecordset: unreadable information.
I could use the SQL statement as the source, but since they are a lot of queries that we have to run (again and again and again) it wont be a realiable option, because each and every command asks for information to establish a connection with the DB server.
I want to ask for the DB connection's information at the begining of the script and use that connection throught the rest of the script.
Any help, will be welcome.
Regards,
Gregorio
