Ecell Use of an Access Table information

[Ray C]

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

 

[Bob Phillips]

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)

 

[Ron de Bruin]

Hi Ray

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

 

[Ray C]

Thamks Bob, this is great
Regards 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]

Thanks Ron, this is excelent
RayC

Hi Ray

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