How to connect to database?

[Guest]

I get an Access database named DBFiles with a table DBTable, and it has been
created through the ODBC connection named DBODBC. Within the DBTable, there
are 2 fields, Name and Phone as shown below.

Name Phone
Peter 1234
Mary 3456
John 5678

Does anyone have any suggestions on how to make a connection to table
DBTable and retrieve the data field into excel?
Thank for any suggestions
Eric

 

[Bob Phillips]

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\DBFiles.mdb"

sSQL = "SELECT Name, Phone From DBTable"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you for your suggestions
Could you please tell me how to get all the value into the cells rather than
display in MsgBox?
Thank for any suggestions
Eric

 

[Bob Phillips]

GetRows creates an array. Just drop that array in a range, or loop through
it. My example just MsgBox's one or two elements to show it works.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)