Solved

SQL Data Connection from Excel 2007

Posted on 2007-12-06
4
3,133 Views
Last Modified: 2012-06-27
I am now connecting to SQL tables from inside Excel using Data -> External Connections.  Previously, those SQL tables were FoxPro tables.  The SQL connection text which worked with the VFP tables (using the VFP format for external connections) does not work with SQL tables directly.

This is one of the former connection texts which worked with VFP:

SELECT (alltrim(intran.fpartno)+" / "+alltrim(intran.fcpartrev)), Sum(intran.fqty*(-1))
FROM intran intran
WHERE (intran.type = 'I') AND (intran.fdate>={d '2005-12-31'})
GROUP BY intran.fpartno,intran.fcpartrev

What I need to do now is connect from inside Excel to the external SQL table and execute SQL commands that will do what the ones above did when connected to the VFP table.  I am using MS SQL Server 2005. The intran table has over 500,000 records in it. The only Excel-to-dbf utility I have will not work with Excel 2007 (xlsx) files--longer than 65K rows. What SQL commands do I enter in the command text tab to accomplish this when connected to a SQL table?

Glenn
0
Comment
Question by:glennes
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20422257
This command should work

SELECT (ltrim(rtrim(intran.fpartno))+" / "+rtrim(ltrim(intran.fcpartrev))), Sum(intran.fqty*(-1))
FROM intran
WHERE (intran.type = 'I') AND (intran.fdate>= '2005-12-31')
GROUP BY intran.fpartno,intran.fcpartrev
0
 

Author Comment

by:glennes
ID: 20422538

This is the default Connection String showing in the Connection Properties/Definition Tab (after I've selected SQL as the connection and selected m2mdata03 as the database and intran as the table from the list of of choices in the drop-down selection box):

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=m2msever;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NM-MXL7140JJ7;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=m2mdata03

This is the default Command Text showing in the Connection Properties/Definition Tab (double-quotes are part of the text):

"m2mdata03"."dbo"."intran"

If I accept this default text, Excel will import the entire intran table just fine (unmodified by the trim statements, of course, which isn't the way I want the data import to Excel to work!).  If I replace the Command Text with your command text, Excel tells me "Table does not exist."

From a purely SQL standpoint, your command statements look correct; they just don't work in the Excel 2007 data connections process.  What would you suggest I try in consideration of this?

Thanks!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20422736
SELECT (ltrim(rtrim("m2mdata03"."dbo"."intran"."fpartno"))+" / "+
  rtrim(ltrim("m2mdata03"."dbo"."intran"."fcpartrev"))), Sum(intran.fqty*(-1))
FROM "m2mdata03"."dbo"."intran"
WHERE ("m2mdata03"."dbo"."intran"."type" = 'I') AND ("m2mdata03"."dbo"."intran"."fdate">= '2005-12-31')
GROUP BY "m2mdata03"."dbo"."intran"."fpartno","m2mdata03"."dbo"."intran"."fcpartrev"

Are you using the Query builder, the GUI tool?
0
 

Author Comment

by:glennes
ID: 20423512
I have the Query Builder tool that's part of SQL Server 2005.  I also have SQL Manager for SQL Server, a 3rd party tool that has a good query builder in it.

I'm still getting the same "Table does not exist." message. Evidently that Command Text box does not want SQL statements like it puts there when the table being connected to is a VFP table.  I suppose I should try building the SQL query using the Query Builder tool and save the resulting SQL table as the one I connect to from Excel. I'm not real sure what to try next!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
A Table within a User Form 24 42
Need help in debugging a UDF results 7 19
vba to flip column 14 17
Excel Sheet Data Finding 14 11
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question