VB issue dragging SQL 2005 db data into excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

VB issue dragging SQL 2005 db data into excel

Author

Topic

markiandean
Starting Member

6 Posts

Posted - 2009-10-12 : 13:24:49

Hi all,

I am having a small issue with the following code in that it only pastes two columns of data into my excel sheet (there are 10 columns in the table)... can anyone tell me why?

Sub QUERYSQLDBANDADDTODB()
Dim fCon As ADODB.Connection
Set fCon = New ADODB.Connection
Dim strSQL As String
fCon.ConnectionString = "Driver={SQL Server Native Client 10.0};Server=CARNA;Database=STOCK_UNIVERSE; Trusted_Connection=yes;"
fCon.Open strSQL
Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
With oRS '
.ActiveConnection = fCon
.Open "SELECT * FROM stock_universe.dbo.Universe"
Workbooks("Manage the database.xls").Sheets("Add a stock to the database").Range("B4").CopyFromRecordset oRS
End With
fCon.Close
Set oRS = Nothing
Set fCon = Nothing
End Sub

My best,

Mark

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-13 : 07:03:47

Is it possible the user executing this query only has access to two columns in the Universe table?
Instead of using SELECT *, try SELECT Column1, column2, column3...

It is good practise to avoid using SELECT * in your code.

markiandean
Starting Member

6 Posts

Posted - 2009-10-13 : 08:13:08

Hi YellowBug,

Thanks for the reply, indeed I tried your suggestion and it worked (or sort of). If I take the asterisk out and put the last seven columns in as you suggested it gives me the last 7 columns ok. If I put the first three columns in, it only gives me the first two. By deduction it is therefore the column [InstrumentFullName] that it does not like, which is an ntext column. It stops working when I refer to this column.

Will try changing the field type to nvarchar or something similar.

Thanks again!

M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources