Executing a SQL View from Excel using VBA
-
I have a need to get data out of SQL but it is a multiple table database with many JOINS tables to connect to. There is a SQL View that does exactly what I want that works fine in SQL Management Studio and has 1000 plus records as a response.
I have searched endlessly for a response and tried a number of things but nothing works.
I can execute a query " select * from table" and get a recordset back or a stored procedure that contains "select * from table" and also get the recordset back in Excel, but you can not use the same code to execute a SQL View.
"select * from view" does not work
The view in question also references at least one, probably more, view(s).
I have spent hours looking and testing alternatives and no luck.
Any assistance would be greatly appreciated.
Not sure if it matters but I have a virtual machine on my laptop that I am running this against.
Excel is on the laptop, not on the VM. All items that have worked do are actually communicating with the SQL DB on the VM as I am getting accurate results.
Thanks in advance for any "View" in to a resolution.
Brent
-
I just tried to connect from Excel 2007 to a view in SQL 2005. It worked without any issues.
Maybe the connection you're using to from Excel doesn't have permission to select from that view?
-
I am logging in as SA so may have wrongly assumed that SA had permissions to all.
I will check this out shortly.
Did you just do a "select * from viewname"
Thanks
-
If all you want to do is return the records from an existing SQL Server view into Excel, you can certainly do it without VBA. And pretty quickly too.
Set up an ODBC connection to your SQL Server.
In your Excel workbook, pick a cell. In Excel 2007, you'd choose "Data", then in the "Get External Data" section on the Ribbon, select "From Other Data Sources".
Choose "From Microsoft Query", and select your ODBC data source from the "Databases" tab. It will open up a Query Wizard. Scroll to your particular view, step through all the choices. Save the Microsoft Query with a useful name such as "myParticularView from myODBC".
-
Yes I have been using this to test but I am trying to build dynamically to attach to different databases as required.
I think I had a corrupt data base and that was my problem. I tried another this morning and now it is magically working.
Thanks.
-
..and now it is magically working.
Merlin strikes again!
Sometimes you just have to shrug your shoulders and get on with it rather than try to figure out why. I doubt that it was actually 'corrupt database' though. SQL Server is pretty robust.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply