?
Solved

open Database connection on excel vba example

Posted on 2010-08-23
3
Medium Priority
?
794 Views
Last Modified: 2012-05-10
Hi,
I need help to guide me how could I open connection, recordset from excel vba
what I looking is ODBC connection with recordset and paste records on excel cells....
That's !
Thank you!
0
Comment
Question by:fsze88
[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
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
MSmax earned 2000 total points
ID: 33508071
hi, try below code:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "YourDSN", "YourUsername", "YourPassword"
rst.ActiveConnection = cnn
rst.CursorLocation = adUseServer

rst.Source = "SELECT * FROM TABLE WHERE FIELD='data'"
rst.Open
variable = rst.Fields("FIELD") ' Do whatever you need to the data
rst.Close

Set rst = Nothing
Set cnn = Nothing

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33508185
hi, thank you

but It said User-defined type not defined.

Could you help?
Untitled.png
0
 
LVL 8

Assisted Solution

by:MSmax
MSmax earned 2000 total points
ID: 33508212
That's because you haven't got references to ADO libraries.
To add them go to VBA Editor-Tools-References and chose Microsoft ActiveX Data Objects 2.x Library
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

764 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