How to connect to database?
 

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

How to connect to database?
 

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)



"Eric" wrote in message
...
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

How to connect to database?
 

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" wrote:


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)



"Eric" wrote in message
...
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

How to connect to database?
 

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)



"Eric" wrote in message
...
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" wrote:


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)



"Eric" wrote in message
...
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