Problems with DAO 3.6 Library 1

[richand1]

Hello all, I have a problem that goes something like this:

I have created a workbook wherein I have this module saved:

Sub DAOTest()

Dim db As Database
Dim rs As Recordset
Dim intColIndex As Integer
Dim mydb As String

Dim FieldName As String
Dim MyCriteria As String
Dim TableName As String

    mydb = "\\Gmukht002s\Data\CommAcc\Pur Com Reports (BLMs)\Data\Pur_Com_Database.mdb"

    Set TargetRange = Range("A1")
    Set db = OpenDatabase(mydb)
    Set rs = db.OpenRecordset("SELECT * FROM " & TableName)
                           
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

This module runs fine and the recordset is as it should be.

If I look in the references for this workbook, I have these references checked in this order:

Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10 Object Library
Microsoft Forms 2.0 Object Library
Microsoft DAO 3.6 Object Library

Now, if I copy this code over to my Personal workbook and try to run the module I get the error message:

Compile Error:

User-defined type not defined

"... db As Database"

is highlighted.

I'm basically trying to get this module to run just from my personal workbook because I don't want to have to keep opening the workbook where the module actually works in order to copy a recordset of 12k+ lines into an Excel workbook.

As soon as I navigate to my personal workbook, the DAO 3.6 Object Library disappears and it won't let me activate the reference. I get this error:

Name conflicts with existing module, project, or object library

Has anyone had this problem before? Can you shed some light on how I can work around it?

Thanks all.
R

 

[combo]

References are linked with VBA project. Together with copying the code you need to add references.
The error message suggests that you have a module or VBProject named 'Database' in 'Personal'.

combo

 

[richand1]

Thanks combo, but I haven't got any modules or projects named "Database".

I'm not sure I understand what you have said about adding references alongwith copying the code. Can you elaborate please?

 

[xlbo]

You probably have an earlier or later version of the DAO reference set in your personal.xls workbook

References are workbook specific so you need to go to your personal.xls module without the other workbook open and check out hat references you have got ticked in there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[richand1]

Thanks Geoff.

I have:

Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10 Object Library
Microsoft Forms 2.0 Object

So, exactly the same references as the other workbook less the DAO reference, but of course it won't let me add the reference.

 

[combo]

In excel VBA project is embedded in a workbook. It's a kind of library. Initially it has four references: vba, excel, ole and office. It means that the code will understand only those types, methods and constants that are declared in referenced libraries.
Projects not referenced don't see each other.
If you try to declare types other than in referencs, you will get an error during compilation ('User-defined type not defined').
A code is just a plain text, when you copy it between projects, the references are not automatically added. They have to be added separately.

In case of the 'Personal', in VBE:
1. see what modules (names) it contains,
2. what is the name of vba project?
3. see what are existing references,
4. add reference to DAO (without copying the code) and try to compile project,
5. copy code ant comment the contents of DAOTest, compile,
6. search contents of vba project for 'database',
7. search the object browser for 'database'.
8. ???

combo

 

[richand1]

Ah. Combo, thanks very much for this.

When you said to search for projects containing "Database" I didn't take into account the "DAOTest" module itself.

So, I removed the "DAOTest" module, added in the DAO 3.6 reference to the library, copied the "DAOTest" module back into Personal and it worked.

Thank you both for your input - much appreciated.

Regards
Rich