Question about Microsoft Office 2003 Basic Edition English (s55-00066) for PC

4 Answers

Running SQL queries on Excel

Hi, Is there a way to run sql queries on Excel 2007? For instance I would like to build a table and select certain rows using sql script. Thanks, D.

Posted by on

  • Dinoza
    Dinoza Aug 15, 2007

    It's true that you can use the excel to do almost anything but since I work with large databases I thought I'd use sql query to increase response time.

    Foe some reason I just don't like to work on access

  • mdringler Feb 26, 2009

    I'm trying to run an SQL statement in Excel 2007 to add items to a combobox in an Excel form. I'm getting this information from an Access database but I can't get my SQL statement to work. It gives me the error "No value given for one or more required parameters.



    Private Sub cboBWContractNumber_Click()
    Dim UsageTracking As ADODB.Connection
    Dim recordset As ADODB.recordset
    Dim i As Integer
    Dim strSQLEquipCommodity As String
    'At this point, cboContractNumber is populated with values _
    When cboBWContractNumber is selected _
    Display cboEquipmentCommodity for that contract number _
    Filter records and fill combo box with selection _
    Create new sub for Get Commodity DSR's

    Set UsageTracking = New ADODB.Connection
    With UsageTracking
    .ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & "\\bsrvfp04\shared\PLM\database\UsageTracking.mdb" & "; "
    .Mode = adModeShareDenyNone
    .Open
    End With


    Application.EnableEvents = True
    If cboBWContractNumber.Value <> "" Then
    frmDSRHeader.cboEquipmentCommodity.Visible = True

    Set recordset = New ADODB.recordset
    With recordset
    .ActiveConnection = UsageTracking
    .Open "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber ORDER BY tblDSRCommodity.COA"
    .Close
    End With

    recordset.MoveFirst

    With Me.cboEquipmentCommodity
    .Clear
    .ColumnCount = 2
    Do
    .AddItem
    .List(i, 0) = recordset![COA]
    .List(i, 1) = recordset![Description]
    i = i + 1
    recordset.MoveNext
    Loop Until recordset.EOF
    End With
    End If
    Set recordset = Nothing
    UsageTracking.Close
    Set UsageTracking = Nothing
    End Sub



    Thank you for your help!

×

Ad

4 Answers

  • Level 1:

    An expert who has achieved level 1.

    Mayor:

    An expert whose answer got voted for 2 times.

  • Contributor
  • 2 Answers

Another way to do it is to use the SQL Drill freeware Excel addin (http://www.sqldrill.com)
hth

Posted on Apr 21, 2009

Ad
  • Level 1:

    An expert who has achieved level 1.

  • Contributor
  • 1 Answer

Or you can go to Data-->Import External Data-->New Data Query-->then choose your table and pull in your Query

Posted on Apr 17, 2008

Ad
  • Level 1:

    An expert who has achieved level 1.

    Corporal:

    An expert that has over 10 points.

    Mayor:

    An expert whose answer got voted for 2 times.

    Problem Solver:

    An expert who has answered 5 questions.

  • Contributor
  • 9 Answers

Yes, there is if you have Access as well. You can declare your Excel spreadsheet a data source in Access and then query using SQL. To do that, create a new DB and select Insert --> Table --> Link Table. Change "File of type" to show all files and select your Excel spreadsheet. Follow the wizard and you'll have a link to the sheet and can query it as if it were a table in access. However I doubt you really need that. Excel's functions should be a match for most queries. Can you give a sample of what you try to query (or why you want the SQL queries?

Posted on Aug 14, 2007

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that got 5 achievements.

    Governor:

    An expert whose answer got voted for 20 times.

    Hot-Shot:

    An expert who has answered 20 questions.

  • Expert
  • 55 Answers

If you just want to do criteria-based queries, you can get a reasonable approximation to a "SELECT ... WHERE ..." kind of query by using the VLOOKUP function in Excel. Then you don't need to use a database, although you may need to specially format your excel data - just read the help file about VLOOKUP.

Posted on Mar 09, 2010

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi there,
Save hours of searching online or wasting money on unnecessary repairs by talking to a 6YA Expert who can help you resolve this issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

Here's a link to this great service

Good luck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

16 Answers

How can I repair a corrupted MDF files and SQL server?


Similar question has been answered here: http://www.fixya.com/support/t24364927-sql_repair_recovery_process_very

Before asking any question in the forum, first you need to search your problem.

May 12, 2014 | Microsoft Database Software A5K-02817 SQL...

2 Answers

Printing syntax


'declare the variables
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL

'define the connection string, specify database driver
ConnString="DRIVER={SQL Server};SERVER=yourServername;UID=yourUsername;" & _
"PWD=yourPassword;DATABASE=yourDatabasename"

'declare the SQL statement that will query the database
SQL = "SELECT * FROM TABLE_NAME"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database

Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("FIRST_FIELD_NAME")
Response.write Recordset("SECOND_FIELD_NAME")
Response.write Recordset("THIRD_FIELD_NAME")
Response.write "<br>"
Recordset.MoveNext
Loop
End If


'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

May 16, 2009 | Microsoft Windows Vista Ultimate Edition

1 Answer

How to create a multiple access query and put altogether into 1 SQL Scrip(.sql file)


Suggest using Union Query. Thus, you can add more queries from many table and the output will be in one (1) query. Bear in mind that all fields must have same Field Name.

For example:
tblOne contains dtDate1 fieldname. Your query will select [tblOne].[dtDate1] As myDate.
tblTwo contains dtDate2 fieldname. Your query will have the following SQL:

Select [tblOne].[dtDate1] As myDate
UNION Select [tblTwo].[dtDate2] as myDate

Thus the output will result dates from tblOne and tblTwo in a column called myDate.

Regards,
Maha

Feb 05, 2009 | Microsoft Computers & Internet

2 Answers

Steps in saving file in excel to database


severals solution :
- save file as text separated by comma and use sqloader from oracle
- use function in excel to concatenate insert order : "insert into TABLE_NAME (field1,...,fieldn) values (A1,B1,...,??n);". Save file as text with sql extension. Connect against roacle db and execute file previously saved.
- use ODBC from MicroSoft

Dec 11, 2008 | Microsoft Computers & Internet

1 Answer

I installed DB2 in my laptop . then i go to all programs->DBCOPY1(default)-> command line tools -> command editor then write down query to create a table but i cant build it it is showing CREATE TABLE...


Hi,

Before you create a table you need to:-

1. create a database
2. create a username and password or use the admin generated userid
3. connect to the database

you can then create a table and use the database

Please post if you do not know how to do this and i'll give you some links

Dec 05, 2008 | IBM DB2 Connect Enterprise Edition for PC,...

1 Answer

Sorting Imported sql data


Are you sure you imported the data or did you create a link to sql. If it is a link then it will not allow you to work with a subset of the data because it would effect the data on the sql database.

Apr 11, 2008 | Microsoft Office Professional 2007 Full...

1 Answer

Query for retrieve the data


so, are you looking for the query statement to get this info...

And, upon entering the 6th digit, your code will execute the sql query, such as:
SELECT cardnumber from <table_name> where cardnumber>='123456'

NOTE: replace <table_name> with the name of your table.

You might have to modify the statement depending upon how your calling it.

Check out more info at http://www.w3schools.com/sql/sql_where.asp

Hope this helps.

Jan 25, 2008 | Computers & Internet

1 Answer

SQL server won't work on my laptop


Two ways to access your server. First, make sure the SQL Server Service is running. Start | Run | Services.msc | Enter. Look for Microsoft SQL Server Service and that it's running. Or Look for the SQL Service Broker icon by the clock and see that it's running. The default instance of SQL Server uses the Hostname. Start | Run | Cmd | Enter. The type hostname. This will be the name to access to server. Then type IPCONFIG, this is then the IP address that you could also use to access it.

SQL Server runs on port 1433, so any firewall has to have it open.

Open Query Analyzer, type in the hostname or ip of the local box, type in "sa" and the password for sa (if setup in mixed mode) or Windows Authentication.

Once you login in, you should now see the databases in the Tree on the left side under the Server | Databases node.

Your description though states "SQL Server won't work on my laptop". TO answer this, the only two SQL Editions that work on anything other than "SERVER" software is SQL Professional and SQL Developer. All others have to be on a "SERVER" platform.

Aug 30, 2007 | Microsoft SQL Server 2000 Standard Edition...

3 Answers

Viewing ms Sql Server database on Access


Are you familiar with "Linking" tables in Access? In Access: File --> Get External Data --> Link Tables. This will bring up a dialog box which allows you to browse to your SQL server and select the table you want.

Aug 23, 2007 | Microsoft SQL Server Standard Edition for...

Not finding what you are looking for?
Microsoft Office 2003 Basic Edition English (s55-00066) for PC Logo

16,030 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

3015 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18425 Answers

Alun Cox

Level 3 Expert

2678 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...