Access 2007 combo Box library woes

[bobdydd]

Hi Everybody

I am currently updating a 2000.mdb to a 2007.accdb and I am trying to
use the 3.6 DAO Object Library in the tools>references in the vb
editor. This produces an error "Name conflict with an Existing
Library" and yet I need this to run the following code.

I have a combo property set to "Limit to List" and on the "not in list
property" the following code needs to run

Any help gratefully received

Dim strMsg As String
#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Method") Then
Response = acDataErrDisplay
Else
#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("xtblCustomerPaidMethod")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="xtblCustomerPaidMethod", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If
rst.AddNew
rst("Data") = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If

[Allen Browne]

Access 2007 automatically uses DAO 3.6 when you open an MDB, and ACE when
you open an ACCDB. However, both libraries are known as DAO within VBA code.
Therefore your code will work without the need to force it.

You can verify that by opening an ACCDB. Open the Immediate Window (Ctrl+G),
and enter:
? References("DAO").FullPath

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bobdydd" <re**************@yahoo.co.ukwrote in message
news:11*********************@p15g2000hsd.googlegro ups.com...

>
Hi Everybody

I am currently updating a 2000.mdb to a 2007.accdb and I am trying to
use the 3.6 DAO Object Library in the tools>references in the vb
editor. This produces an error "Name conflict with an Existing
Library" and yet I need this to run the following code.

I have a combo property set to "Limit to List" and on the "not in list
property" the following code needs to run

Any help gratefully received

Dim strMsg As String
#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Method") Then
Response = acDataErrDisplay
Else
#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("xtblCustomerPaidMethod")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="xtblCustomerPaidMethod", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If
rst.AddNew
rst("Data") = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If

[Douglas J. Steele]

In Access 2007, DAO comes from acedao.dll, not dao360.dll.

In other words, you shouldn't need to add a DAO reference: Microsoft came to
their senses in Access 2003, and DAO is back by default.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"bobdydd" <re**************@yahoo.co.ukwrote in message
news:11*********************@p15g2000hsd.googlegro ups.com...

>
Hi Everybody

I am currently updating a 2000.mdb to a 2007.accdb and I am trying to
use the 3.6 DAO Object Library in the tools>references in the vb
editor. This produces an error "Name conflict with an Existing
Library" and yet I need this to run the following code.

I have a combo property set to "Limit to List" and on the "not in list
property" the following code needs to run

Any help gratefully received

Dim strMsg As String
#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Method") Then
Response = acDataErrDisplay
Else
#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("xtblCustomerPaidMethod")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="xtblCustomerPaidMethod", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If
rst.AddNew
rst("Data") = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If

[bobdydd]

On 2 Apr, 17:46, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.comwrote:

In Access 2007, DAO comes from acedao.dll, not dao360.dll.

In other words, you shouldn't need to add a DAO reference: Microsoft came to
their senses in Access 2003, and DAO is back by default.

Hi All

The addition of "Microsoft Activex Data Objects 2.8 Library" did the
trick

Thanks guys

[David W. Fenton]

"bobdydd" <re**************@yahoo.co.ukwrote in
news:11**********************@n76g2000hsh.googlegr oups.com:

The addition of "Microsoft Activex Data Objects 2.8 Library" did
the trick

Then you weren't using DAO in the first place -- that's the ADO
library.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[bobdydd]

Doh!!

I am gonna drop the decaff ................it's making me sluggish

I was pretty proud of myself getting to grips with 2007 and then fell
down

Thanks for picking me up

Bob

The addition of "Microsoft Activex Data Objects 2.8 Library" did
the trick

Then you weren't using DAO in the first place -- that's the ADO
library.

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/