Solved

EXCEL MYSQL  ODBC Select and display records

Posted on 2014-01-02
7
616 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
  • 5
  • 2
7 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39753443
Then add the ORDER BY

SELECT * FROM tbl ORDER BY some_column LIMIT 0,9;
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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