• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 809
  • Last Modified:

open Database connection on excel vba example

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
fsze88
Asked:
fsze88
  • 2
2 Solutions
 
MSmaxImplementation ConsultantCommented:
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
 
fsze88Author Commented:
hi, thank you

but It said User-defined type not defined.

Could you help?
Untitled.png
0
 
MSmaxImplementation ConsultantCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now