Excel and SQL Queries

[JeremyAnderson]

I have a problem that I hope someone can help me with. I need to query multiple tables in a SQL DB from an Excel Spreadsheet with a single query. Here some background information on the issue:

I work for a franchise store of a larger used car dealership. Each franchise and the corporate office has their own in house financing and collections departments. We do not use outside financing companies or banks. The corporate office keeps a database of all accounts for all stores whether they are corporate or franchise. Though I am the systems administrator for my franchise I have no administrative privledges on the corporate systems. Now for the down and dirty, we have a spreadsheet that our collection agents update daily to show things such as the accounts they are responsible for, how many of those are delinquent, the amount of the loan, the amount they are deliquent, etc. All of this information is in the corporate database, but is distributed amungst multiple tables. The coporate office has an internal website they allow us to use with predefined queries and reports. Currently the collectors run about 10 or so differant reports in order to gather all of this information to place it in this spreadsheet. It is taking them about an hour a day each to do this. The CFO and VP of Operations have charged me with automating this spreadsheet to pull this information from the database automatically via a database query. As I have had very little exposure to SQL in the past and am "ify" with Excel, I need as much help as I can get. Also, I am the ONLY IT person working for this franchise. Does any have any clue on how to begin this? Any and all help is appreciated.

 

[SkipVought]

Hi,

Assuming that you have configured an ODBC Driver for your SQL Datbase, sounds as if you could use the canned queries in Excel using MS Query via Data/Get External Data/New Database query....

Or you might check to see if you can get READ access (as opposed to Admin rights) to the tables that you need.

I'd stay away from REPORTS as they will take some scraping to get good data, which will take VBA code.

Once you have inserted a query on a sheet (I'd put only one query per sheet) it's there to stay and you need only REFRESH to get current data.



Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[JeremyAnderson]

I have done this in the past to pull data from the same table, however, it will not allow me to pull from multiple tables as they are not linked or related on the Server side. I apologize that I had not included this information before.

 

[RoyVidar]

If you use ADO and either an SP delivering more than one dataset, or you feed it with multiple dynamic sql statements, you should be able to retrieve multiple recordsets, not with one query, though, but connecting only once.

Here's a quick and dirty (typed) sample using the Northwind sample database, which will drop the result to the immediate pane (ctrl+g) - and posting code in a non-code forum, ouch ... would probably fit better in forum707

[tt]dim cn as adodb.connection
dim rs as adodb.recordset
dim strSql as string

set cn = new adodb.connection
' use relevant connection string, see Connection strings
' to open the connection

strSql = "select * from titles; select * from authors; select * from employee"

set rs = new adodb.recordset
with rs
set .activeconnection = cn
.locktype = adlockoptimistic
.cursortype = adopenkeyset
.open strSql,,,,adcmdtext
end with
do while not rs is nothing
debug.print rs.getstring(adclipstring,,vbtab,vbcrlf)
set rs = rs.nextrecordset
loop[/tt]

But I'm not sure I understand the need to do it all in one query? Surely having for instance some parameterized Stored Procedures to be called from time to time, just providing the parameters couldn't be that bad, could it?

Note, set a reference to ADO (Microsoft ActiveX Data Objects 2.N Library - N: as high as possible - in VBE - Tools | References)

Roy-Vidar

 

[SkipVought]

Tables do not need to be explicitly related in the database.

It is possible, in the MS Query QBE Grid, to link multiple tables.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}