Solved

EXCEL MYSQL  ODBC Select and display records

Posted on 2014-01-02
7
650 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

630 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