Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXCEL MYSQL  ODBC Select and display records

Posted on 2014-01-02
7
Medium Priority
?
665 Views
Last Modified: 2014-01-03
Say,
 
I have an ODBC link in place.
How can I select top 10 records from a table and display in an excel Sheet?

Can I use a call statement to an existing query in Mysql which selects the top 10 records, else another method is fine?
tx
0
Comment
Question by:shaunwingin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 39753408
You use the LIMIT keyword in your query

SELECT * FROM tbl LIMIT 0,9;
0
 

Author Comment

by:shaunwingin
ID: 39753433
Will this select the oldest or newest records? Need newest.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39753443
Then add the ORDER BY

SELECT * FROM tbl ORDER BY some_column LIMIT 0,9;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Rob
ID: 39753444
If you were to sort the table manually, just by looking at it, what column would you sort on and would you sort it ascending or descending?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39753460
Regarding your other question about calling a query and displaying in excel.  There are two methods that come to mind but would depend on how your users would use the spreadsheet and how the data is displayed.

Method 1: Re-use an existing query (good for large data sets and pivot tables)
This is a bit long winded, but good for when the layout of the query you're returning doesn't change.  The idea is you create a dummy query to your database and then as you want to show new data you change the sql for that query and tell it to refresh.

Method 2: Manually parse each record (good for straight tables)
This has its downfalls, especially when you may be pivoting data but gives you flexibility on where the data is displayed.  eg The query returns a row of data at a time so essentially you could put that data anywhere in the spreadsheet you want.  You are not tied to a table / pivot layout that you would in Method 1.

Basically I would more or less use Method 1 but there are occasions when Method 2 is ok, usually with small datasets ie few records
0
 

Author Comment

by:shaunwingin
ID: 39753489
Method 2 would work.
assume a simple table with fields
date
time
shiftno

how can I select top 10 records and display in excel sheet result cel A1 downwards?
Would use call query to select the data.
0
 
LVL 43

Accepted Solution

by:
Rob earned 2000 total points
ID: 39753509
So taking the code from your previous question:  http:Q_28327033.html#a39750882

Public Sub myodbc_ado_Click(param1 as String, param2 as String)
	Dim conn As ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim fld As ADODB.Field
	Dim sql As String
	
	' Server settings for database
	Dim myserver as String
	Dim mydb as String
	Dim user as String
	Dim pwd as String
	
	' Parameters
	Dim param1 as String
	Dim param2 as String
	
	'connect to MySQL server using Connector/ODBC
	Set conn = New ADODB.Connection
	
	myserver = "localhost"
	mydb = "test"
	user = "venu"
	pwd = "venu"
	
	' get the parameters from the sheet
	Sheet1.Activate
	
	param1 = ActiveSheet.Range("A1").Value
	param2 = ActiveSheet.Range("B1").Value

	conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};"_
	& "SERVER="&myserver&";"_
	& " DATABASE="&mydb&";"_
	& "UID="&user&";PWD="&pwd&"; OPTION=3"
	conn.Open

	sql = "SELECT date, time, shiftno FROM mytable WHERE param1='"+param1+"' AND param2='"+param2+"' ORDER BY date DESC LIMIT 0,9;"
	
	Set rs = conn.Execute(sql)
	
	' output to sheet named "Report"
	Dim i as Long
	i = 2 ' start at row 2 as the headers will be on row 1

	Sheets("Report").Activate
	
	ActiveSheet.Cells(1,1).Value = "Date"
	ActiveSheet.Cells(1,2).Value = "Time"
	ActiveSheet.Cells(1,3).Value = "Shift No."
	
	Do While Not rs.EOF
		With ActiveSheet
			.Cells(i, 1).Value = rs.Fields(0).Value
			.Cells(i, 2).Value = rs.Fields(1).Value
			.Cells(i, 3).Value = rs.Fields(2).Value
		End With
	  	i = i + 1 ' move to the next row
	Loop
	rs.Close

	conn.Close

End Sub

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

722 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