MSQuery / VB ... portable data extract methods

[WillR]

Is there a way of saving a query with an excel file so that it is truly portable....
I want a user to be able to hit a macro button in a worksheet that will refresh a query based on some patrameters they have entered in the worksheet - but as the users could be anyone in the company, i'd like the query to move 'with' the file....
query written to a sql database - so
1. can i do this or
2. can i do it via vba and save with the workbook....

Quite happy to paste the sql code here if necessary if option 2 is the only option.

cheers,

 

[MarkHenri]

I've been thinking about this. MSQuery seems to static for my taste. Awhile back, I wrote this little utility for myself called select.exe and you invoke it like this--

>select "mydsnname;login;password" "select name, address, bla, bla, bla from customer where zip code like '92%'"

and it sends it's output to stdout. I've been thinking about writing one like it that was a W32 application that outputs to a text file. Then Excel could pick up the data from that text file and load it in. This would eliminate the need for my to rewrite my entire ODBC class library from C++ to VB or have to write ugly ODBC code into a macro. Am I just being stupid? There's got to be some sort of ActiveX solution for this but I'm not good with VB (I'm a C++ developer).

Other things that I do know how to do would be to write a TCP/IP based data server that delivered the data via XML or any format for that matter. This would elimate the need for ODBC components or data source definitions to be present at the client since everyone's got a TCP/IP stack these days. I'd just use port 80 to get around the firewall and package the request to look like web page gets.

 

[ChrisM]

Maybe I'm oversimplifying this, but a MS Query is always saved with the excel file. We use all kinds of queries here, ran by quite a few users. The only constraint is specifying the location of the database. If a user has a drive mapped to something other than what the query is looking for, they'll get an error.

 

[WillR]

Chris, Henri

Thanks for your replies.... i'd already stayed with MS Query and discovered your point Chris so thanks anyway for your time...

 

[MarkHenri]

It's pretty easy to create an ASP (or PHP) web page that allows a user to enter selection criteria and then presents the results in a spreadsheet. Would this work for you? (This is really simple to do by the way.)

I've got a bunch of questions but first a little discussion of what is going on and what is possible.

I've played with MSQuery a little bit, I can say that it is not very configurable. Also, I like to just write SQL and I don't need a front end to help me get my syntax right. Though I'm a fan of ODBC, I don't like all it's requirements for DSN definitions and that it's installed properly on the system. Mapping to a drive letter where a database is (via Access) is also fraught with configuration issues.

That said, I think that a TCP/IP (web) server approach would in the end, be the cleanest.

Now the questions--

What database are you using?

What middleware approach are you taking (ODBC, mapped drive letter...)?

Does your company have an internal automated web server available?

How secure does the database need to be?

Can you explain why users need to update data in a workbook as opposed to just viewing it on a web page?

Any other details would be helpful

Thanks,

 

[WillR]

On 2002-09-11 10:06, MarkHenri wrote:
It's pretty easy to create an ASP (or PHP) web page that allows a user to enter selection criteria and then presents the results in a spreadsheet. Would this work for you? (This is really simple to do by the way.)

I've got a bunch of questions but first a little discussion of what is going on and what is possible.

I've played with MSQuery a little bit, I can say that it is not very configurable. Also, I like to just write SQL and I don't need a front end to help me get my syntax right. Though I'm a fan of ODBC, I don't like all it's requirements for DSN definitions and that it's installed properly on the system. Mapping to a drive letter where a database is (via Access) is also fraught with configuration issues.

That said, I think that a TCP/IP (web) server approach would in the end, be the cleanest.

Now the questions--

What database are you using?
SQL Database called Joey
What middleware approach are you taking (ODBC, mapped drive letter...)?
if i understand you, it's ODBC
Does your company have an internal automated web server available?
an internal intranet http://joey/mis/ is the URL
How secure does the database need to be?
we have various firewall protection
Can you explain why users need to update data in a workbook as opposed to just viewing it on a web page?
viewing results only & ability to print would be fine
Any other details would be helpful

Thanks,

Mark, currently to post results of various sales reports to the MIS intranet, i create various reports in either Crystal or Excel & convert to pdf format and post to the intranet site.... obviously this can be time consuming and really not ideal. I guess if it IS easy to do, enabling others to input parameters on a 'web' page and retrieve their own data would be a top result.... but i really not sure where to start... I have attmpted to answer your questions, however i fear, not very well.... so any help/guidance would be appreciated. I have now successfully created my queries with parameters in MSQuery, howver as you say, it's not very portable/flexible....

Here's hoping.

 

[MarkHenri]

Good details. I'm working on some examples right now and I'll have them shortly...

 

[MarkHenri]

OK, check out this link to a web page on my site that has an example of creating an Excel spreadsheet from within a web page using client side VBScript.

So with this paradym, you would use the automated server and create PHP or ASP server side scripting code to read from the database and generate this web page with the data embedded in it's VBScript client side code (it all seems very convoluted but it's not too bad to implement).

You could even add parameters to the web page that submits the request for this page so the user could get a spreadsheet back tailored to his department or region etc. Pretty cool, huh?

 

[XL-Dennis]

Mark,

You could even add parameters to the web page that submits the request for this page so the user could get a spreadsheet back tailored to his department or region etc. Pretty cool, huh?

Very nice & cool - Thanks for sharing it

Kind regards,
Dennis

 

[WillR]

Mark, excellent.... thanks for getting me started.. now to see if i can get some query results in to the spreadsheet!