Query Access from another MS application

[RougeDev]

Hi I need to pull data from a MS Access database for use within a combo-box inside an Excell spreadsheet.

How do I instantiate the ADO I need to query??

& Then do I just use a querydef object to query the ADO???

Am just teaching myself VBA so am a littel lost

Thanks

Gavin

 

[RSi2]

look into the FAQ section ... VBA How to : Pulling data form Access to another MS App

Hope it will help you

 

[RougeDev]

Yep that helped heaps.

What I'm doing is allowing a user to select a name from the combo-box and now I want to display the relevant phone number (stored in a database) in a cell (or label whatever is easier).

Any ideas


Thanks

Gavin

(code for combo box follows)

Private Sub ComboBox1_Change()

'In Tools / References include "Microsoft DAO 3.51"
Dim oWorkspace As DAO.Workspace
Dim oDatabase As DAO.Database
Dim oRecordset As DAO.Recordset

Set oWorkspace = DAO.CreateWorkspace("", "admin", "", dbUseJet)
Set oDatabase = oWorkspace.OpenDatabase("c:\LAIP Contracts.mdb&quot
Set oRecordset = oDatabase.OpenRecordset("Contacts&quot

oRecordset.MoveFirst
While Not oRecordset.EOF
'your code here

ComboBox1.AddItem (oRecordset.Fields("name&quot.Value)

'ok, go next
oRecordset.MoveNext
Wend

oRecordset.Close
oDatabase.Close
oWorkspace.Close

End Sub

 

[RSi2]

On the "Onchange" on your ComboBox, (or OnSelChange ...) you can retrive the selected name from your Combo and put the information from the other DB into a Label/Memo via the result of a simple Query ...

 

[RougeDev]

Ok can't find the OnChange (or OnSelChange) you talk about so decided to use the lost focus trigger event as this will be ok

Private Sub ComboBox1_LostFocus()

Dim oWorkspace As DAO.Workspace
Dim oDatabase As DAO.Database
Dim oRecordset As DAO.Recordset
Dim oQdef As DAO.QueryDef


Set oWorkspace = DAO.CreateWorkspace("", "admin", "", dbUseJet)
Set oDatabase = oWorkspace.OpenDatabase("c:\LAIP Contracts.mdb&quot
Set oQdef = oDatabase.CreateQueryDef("&quot

With oQdef

.Sql = "SELECT MobilePhone FROM Contacts " & _
"WHERE Name = '" & _
ComboBox1.Value & "'"

Set oRecordset = oDatabase.OpenRecordset("Contacts&quot
oRecordset.MoveFirst

End With

While Not oRecordset.EOF
'your code here
If (Not oRecordset.Fields("MobilePhone&quot.Value) Then
Label1.Caption = oRecordset.Fields("MobilePhone&quot.Value
End If

'ok, go next
oRecordset.MoveNext
Wend

oQdef.Close
oRecordset.Close
oDatabase.Close
oWorkspace.Close
End Sub


But my If statement spews (type mismatch error???)

I put the if in to avoid "invalid use of null" error

Any ideas??

Thanks so much you've been very helpful so far

Gavin

 

[RougeDev]

Thanks For all your help. Problem Now Solved

Gavin