Solved

EXCEL MYSQL  ODBC Select and display records

Posted on 2014-01-02
7
609 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now