Access 2010 - VBA: Name conflicts with existing module, project, or object library

Question

I was working with a new Access 2010 database and copied in VBA code from a 2003 database.  The code did not do what I anticipated and I realized that I did not select the reference "Microsoft DAO 3.6 Object Library".  I selected the reference, but received the error "Name conflicts with existing module, project, or object library".  I searched the Internet and nothing fixed this.  I then created a brand new database with nothing in it.  I created a module, immediately selected "Microsoft DAO 3.6 Object Library", and received the same message.  What is the issue?

Answer 1

What references do you have checked in your database?  The reason you are most likely receiving this error is because in the new .accdb file format the DAO functionality is included in the “Microsoft Office 14.0 Access Database engine object library”.  If you uncheck this reference, you can then check the DAO 3.6 reference if you want to, but the preferred option would be to leave the default reference to the 14.0 database engine library checked.

Best Regards,

Nathan O.

Microsoft Online Community Support

Answer 2

I'm not an experienced Access practitioner.  I came across this problem never having defined any project or indeed used VBA.   I can reproduce the problem in 2010 by having two unconnected forms open for edit in the same database at the same time.  The problem happens with the same Access forms in different (new) databases and on different machines, suggesting that it may not be a registry or Access/Office installation problem.  The two forms in question have been changed multiple times.

I must confess to being disappointed by Access 2010 and 2013.  Both seem to have a tendency to reboot themselves (I'm not using any VBA or external code).   Sadly the answer is frequent backups, and be prepared for restores.

Answer 3

I don't see anything that would cause a problem, but you unchecked the wrong reference. starting with Access 2007, the engine changed from JET to ACE. Use the "Microsfot Office 14.0 Access Database engine object library", not the DAO 3.6 library.

What error are you getting?

Here is how Access sees your SQL string:

SELECT Duns, Supplier_Indicator FROM qry_DivRegSupplierIndicator ORDER BY Duns, SupplierIndicator ASC

Open a new query. Switch to SQL view and paste that in the window. Run the query to see if it works correctly.

Answer 4

I'm not an experienced Access practitioner.  I came across this problem never having defined any project or indeed used VBA.   I can reproduce the problem in 2010 by having two unconnected forms open for edit in the same database at the same time.  The problem happens with the same Access forms in different (new) databases and on different machines, suggesting that it may not be a registry or Access/Office installation problem.  The two forms in question have been changed multiple times.

I must confess to being disappointed by Access 2010 and 2013.  Both seem to have a tendency to reboot themselves (I'm not using any VBA or external code).   Sadly the answer is frequent backups, and be prepared for restores.

Jerry - It sounds like one (or both) form is corrupt. You say they have been changed several times. That can lead to corruption. Did you try compacting the database? Does the crash happen if you create two new forms names being FormA and FormB and have only those two open in design view? 

It really not That Access is unstable. I've been developing in Access for more than 18 years and in 2010 for 4 years and have never had a crash unless I hosed the database by doing something stupid.

Answer 5

You are absolutely right!  I unchecked "Microsfot Office 14.0 Access Database engine object library" and I was able to successfully add DAO 3.6 reference.  However, neither of these references will successfully run the code below that I inherited from another user (I have only basic knowledge of VBA).  I have determined that the code crashes at Set rst = db.OpenRecordset(sSql, dbOpenSnapshot).  The code works very successfully in Access 2003 databases.  Do you have any ideas/suggestions to make this work?

The purpose of the code is explaineded below.  A coworker found it years ago online.

'> Hello,

'> I would Combining values from fields of more than 1 record in 1 field.

'> For example

'>

'> Name     firstname    number

'>

'> Heide    Marcel       1

'> Heide    Marcel       2

'> Heide    Marcel       3

'> Kooring  Yvonne       7

'> Kooring  Yvonne       14

'>

'> Number is a unique number

'>

'> Result should look like

'> Heide    Marcel       1,2,3

'> Kooring  Yvonne       7,14

VBA CODE:

Option Compare Database

Option Explicit

Public Function SupInd() As Boolean

On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSql As String

Dim strColA As String, strColB As String

Set db = CurrentDb()

' Delete Table, if exists

 If DCount("*", "MsysObjects", "[Name]='tbl_SupplierIndicator'") = 1 Then

   DoCmd.DeleteObject acTable, "tbl_SupplierIndicator"

 End If

sSql = "CREATE TABLE tbl_SupplierIndicator (Duns Integer, SupplierIndicator Text(5))"

db.Execute sSql

sSql = "SELECT Duns, Supplier_Indicator FROM qry_DivRegSupplierIndicator " _

       & "ORDER BY Duns, SupplierIndicator ASC"

Set rst = db.OpenRecordset(sSql, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then

  rst.MoveFirst

  strColA = rst!Duns

  strColB = rst!SupplierIndicator

  rst.MoveNext

  Do Until rst.EOF

    If strColA = rst!Duns Then

      strColB = strColB & ", " & rst!SupplierIndicator

    Else

      sSql = "INSERT INTO tbl_SupplierIndicator (Duns, SupplierIndicator)  " _

           & "VALUES('" & strColA & "','" & strColB & "')"

      db.Execute sSql

      strColA = rst!Duns

      strColB = rst!SupplierIndicator

    End If

    rst.MoveNext

  Loop

  ' Insert Last Record

  sSql = "INSERT INTO tbl_SupplierIndicator (Duns, SupplierIndicator)  " _

       & "VALUES('" & strColA & "','" & strColB & "')"

  db.Execute sSql

End If

Set rst = Nothing

Set db = Nothing

End Function