I have a a table in Access that holds some information that I need to use in
EXcell for the time being. Is it possible to have Excell work directly with
information held in an Access Table. I currently resolve the problem by
exporting the Table to Excell and then using Vlookup to access the data. This
means I have 2 versions of the same data and that is not good. it would be
good to do something like a lookup from within Excell but looking up
information held in an Access Table rather than another Spreadsheet.
Is this possible? Ammending the info in the Access table from within Excell
would be good but not neccessary.
Thanks RayC

Here is a starter


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:\bob.mdb"

sSQL = "SELECT * From Contacts"
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) & ", " & ary(2, 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)

"Ray C" wrote in message
...
I have a a table in Access that holds some information that I need to use
in
EXcell for the time being. Is it possible to have Excell work directly
with
information held in an Access Table. I currently resolve the problem by
exporting the Table to Excell and then using Vlookup to access the data.
This
means I have 2 versions of the same data and that is not good. it would be
good to do something like a lookup from within Excell but looking up
information held in an Access Table rather than another Spreadsheet.
Is this possible? Ammending the info in the Access table from within
Excell
would be good but not neccessary.
Thanks RayC

Hi Ray

See also
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray C" wrote in message ...
I have a a table in Access that holds some information that I need to use in
EXcell for the time being. Is it possible to have Excell work directly with
information held in an Access Table. I currently resolve the problem by
exporting the Table to Excell and then using Vlookup to access the data. This
means I have 2 versions of the same data and that is not good. it would be
good to do something like a lookup from within Excell but looking up
information held in an Access Table rather than another Spreadsheet.
Is this possible? Ammending the info in the Access table from within Excell
would be good but not neccessary.
Thanks RayC

Thamks Bob, this is great
Regards RayC

"Bob Phillips" wrote:

Here is a starter


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:\bob.mdb"

sSQL = "SELECT * From Contacts"
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) & ", " & ary(2, 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)

"Ray C" wrote in message
...
I have a a table in Access that holds some information that I need to use
in
EXcell for the time being. Is it possible to have Excell work directly
with
information held in an Access Table. I currently resolve the problem by
exporting the Table to Excell and then using Vlookup to access the data.
This
means I have 2 versions of the same data and that is not good. it would be
good to do something like a lookup from within Excell but looking up
information held in an Access Table rather than another Spreadsheet.
Is this possible? Ammending the info in the Access table from within
Excell
would be good but not neccessary.
Thanks RayC

Thanks Ron, this is excelent
RayC

"Ron de Bruin" wrote:

Hi Ray

See also
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray C" wrote in message ...
I have a a table in Access that holds some information that I need to use in
EXcell for the time being. Is it possible to have Excell work directly with
information held in an Access Table. I currently resolve the problem by
exporting the Table to Excell and then using Vlookup to access the data. This
means I have 2 versions of the same data and that is not good. it would be
good to do something like a lookup from within Excell but looking up
information held in an Access Table rather than another Spreadsheet.
Is this possible? Ammending the info in the Access table from within Excell
would be good but not neccessary.
Thanks RayC