Using data in Access database in outlook forms
 

Has anyone every accessed a MS Access Database using a combobox in an outlook
form? I am looking to have a combobox display a list of records that are in a
access databse on another server. A standard ADO recordset in the object does
not seem to work.

Using data in Access database in outlook forms
 

You can't use bound controls in an Outlook form, but accessing a recordset
using SQL and ADO and putting the data into a control works just fine. I've
used grid controls many time as well as combo and list boxes. Get the
recordset and iterate it, putting the data into the appropriate rows of the
combo.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Weatherman" wrote in message
...
Has anyone every accessed a MS Access Database using a combobox in an
outlook
form? I am looking to have a combobox display a list of records that are
in a
access databse on another server. A standard ADO recordset in the object
does
not seem to work.

Using data in Access database in outlook forms
 

Thanks Ken, it's something in my connection string. I am opening a recordset
but it has a 0 count. I see that the query and table in access have record
locks and I am not getting any errors in VBA. Outlook can pull data from
access so I can eliminate that a possable cause.

"Ken Slovak - [MVP - Outlook]" wrote:

You can't use bound controls in an Outlook form, but accessing a recordset
using SQL and ADO and putting the data into a control works just fine. I've
used grid controls many time as well as combo and list boxes. Get the
recordset and iterate it, putting the data into the appropriate rows of the
combo.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Weatherman" wrote in message
...
Has anyone every accessed a MS Access Database using a combobox in an
outlook
form? I am looking to have a combobox display a list of records that are
in a
access databse on another server. A standard ADO recordset in the object
does
not seem to work.

Using data in Access database in outlook forms
 

Are you getting any errors when you set up your connection? How are you
doing it, a connect string or a DSN?

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Weatherman" wrote in message
...
Thanks Ken, it's something in my connection string. I am opening a
recordset
but it has a 0 count. I see that the query and table in access have
record
locks and I am not getting any errors in VBA. Outlook can pull data from
access so I can eliminate that a possable cause.

Using data in Access database in outlook forms
 

I'm using a string connection, 'strDBPath = "S:\space2003.mdb"' I had no
errors when in the general declaration of the form and can confirm that it is
opening the database and running the query I am requesting it to. BUT I left
out rs.movelast, movefirst statements. SMALL over sight. I have it in a
module now and get a Rowset Does Not Support Fetching backward or Operation
not available Error if I rem it out. the code is below, I'm missing a step
somewhere.

Public NRTCC As ADODB.Connection

Private Sub cmbRoomList_Click()
On Error GoTo RoomListClickErr
Dim qryRoom As String
Dim RoomIDrs As ADODB.recordset
Dim i As Integer
' Set string path to DB
strDBPath = "\\bach\Center Space\space2003.mdb"
'opens connection
Set NRTCC = New ADODB.Connection
With NRTCC
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With

Set RoomIDrs = New ADODB.recordset
With RoomIDrs
.CursorType = AdOpenForwardOnly
.locktype = adLockReadOnly
.ActiveConnection = NRTCC
End With

' qryRoom declareation and execution
qryRoom = "select * from qryConfRooms"
RoomIDrs.Open qryRoom

RoomIDrs.MoveLast
RoomIDrs.MoveFirst
' ****breaks at this point****
' Set combobox data source

cmbRoomList.Clear

For i = 0 To RoomIDrs.RecordCount - 1
cmbRoomList.AddItem RoomIDrs.Fields("fldBuilding" & "/"
& "fldRoom")
RoomIDrs.MoveNext
Next i



RoomListClickErr:
'basic errmessage
pstrMessage = "Cannot perform operation" & _
Chr(vbKeyReturn) & " Error # '" & _
Err.Number & ": " & Err.Description
MsgBox pstrMessage, vbExclamation, _
"NRTCMS Property & Support Services - Database Error"

End Sub


"Ken Slovak - [MVP - Outlook]" wrote:

Are you getting any errors when you set up your connection? How are you
doing it, a connect string or a DSN?

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Weatherman" wrote in message
...
Thanks Ken, it's something in my connection string. I am opening a
recordset
but it has a 0 count. I see that the query and table in access have
record
locks and I am not getting any errors in VBA. Outlook can pull data from
access so I can eliminate that a possable cause.

Using data in Access database in outlook forms
 

..CursorType = AdOpenForwardOnly

That means you can only move forward in the cursor. Set a different cursor
type or only move to the start of the cursor, not the end.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Weatherman" wrote in message
...
I'm using a string connection, 'strDBPath = "S:\space2003.mdb"' I had no
errors when in the general declaration of the form and can confirm that it
is
opening the database and running the query I am requesting it to. BUT I
left
out rs.movelast, movefirst statements. SMALL over sight. I have it in a
module now and get a Rowset Does Not Support Fetching backward or
Operation
not available Error if I rem it out. the code is below, I'm missing a
step
somewhere.

Public NRTCC As ADODB.Connection

Private Sub cmbRoomList_Click()
On Error GoTo RoomListClickErr
Dim qryRoom As String
Dim RoomIDrs As ADODB.recordset
Dim i As Integer
' Set string path to DB
strDBPath = "\\bach\Center Space\space2003.mdb"
'opens connection
Set NRTCC = New ADODB.Connection
With NRTCC
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With

Set RoomIDrs = New ADODB.recordset
With RoomIDrs
.CursorType = AdOpenForwardOnly
.locktype = adLockReadOnly
.ActiveConnection = NRTCC
End With

' qryRoom declareation and execution
qryRoom = "select * from qryConfRooms"
RoomIDrs.Open qryRoom

RoomIDrs.MoveLast
RoomIDrs.MoveFirst
' ****breaks at this point****
' Set combobox data source

cmbRoomList.Clear

For i = 0 To RoomIDrs.RecordCount - 1
cmbRoomList.AddItem RoomIDrs.Fields("fldBuilding" & "/"
& "fldRoom")
RoomIDrs.MoveNext
Next i



RoomListClickErr:
'basic errmessage
pstrMessage = "Cannot perform operation" & _
Chr(vbKeyReturn) & " Error # '" & _
Err.Number & ": " & Err.Description
MsgBox pstrMessage, vbExclamation, _
"NRTCMS Property & Support Services - Database Error"

End Sub

Using data in Access database in outlook forms
 

Thanks, keyset works.

"Ken Slovak - [MVP - Outlook]" wrote:

..CursorType = AdOpenForwardOnly

That means you can only move forward in the cursor. Set a different cursor
type or only move to the start of the cursor, not the end.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Weatherman" wrote in message
...
I'm using a string connection, 'strDBPath = "S:\space2003.mdb"' I had no
errors when in the general declaration of the form and can confirm that it
is
opening the database and running the query I am requesting it to. BUT I
left
out rs.movelast, movefirst statements. SMALL over sight. I have it in a
module now and get a Rowset Does Not Support Fetching backward or
Operation
not available Error if I rem it out. the code is below, I'm missing a
step
somewhere.

Public NRTCC As ADODB.Connection

Private Sub cmbRoomList_Click()
On Error GoTo RoomListClickErr
Dim qryRoom As String
Dim RoomIDrs As ADODB.recordset
Dim i As Integer
' Set string path to DB
strDBPath = "\\bach\Center Space\space2003.mdb"
'opens connection
Set NRTCC = New ADODB.Connection
With NRTCC
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With

Set RoomIDrs = New ADODB.recordset
With RoomIDrs
.CursorType = AdOpenForwardOnly
.locktype = adLockReadOnly
.ActiveConnection = NRTCC
End With

' qryRoom declareation and execution
qryRoom = "select * from qryConfRooms"
RoomIDrs.Open qryRoom

RoomIDrs.MoveLast
RoomIDrs.MoveFirst
' ****breaks at this point****
' Set combobox data source

cmbRoomList.Clear

For i = 0 To RoomIDrs.RecordCount - 1
cmbRoomList.AddItem RoomIDrs.Fields("fldBuilding" & "/"
& "fldRoom")
RoomIDrs.MoveNext
Next i



RoomListClickErr:
'basic errmessage
pstrMessage = "Cannot perform operation" & _
Chr(vbKeyReturn) & " Error # '" & _
Err.Number & ": " & Err.Description
MsgBox pstrMessage, vbExclamation, _
"NRTCMS Property & Support Services - Database Error"

End Sub