Solved

Late binding DAO

Posted on 2007-11-29
22
864 Views
Last Modified: 2012-05-05
I am using DAO and querydef... what would be the correct late binding?
I keep getting this erro:

ActiveX component can't create object

My code is below.. i think it is crashing on:

Set db = CurrentDb()

what would be the late binding for that?
Dim db As Database

Dim rs As Recordset

Dim qdf As QueryDef
 

Set db = CurrentDb()

Set qdf = db.QueryDefs("qryRptWeeklyEmail")

Open in new window

0
Comment
Question by:michael1174
  • 9
  • 9
  • 2
  • +2
22 Comments
 
LVL 75
ID: 20377928
Try this:


Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim qdf As Dao.QueryDef
 
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20377931
Try explicitly defining these as DAO, and make sure your references are set properly  (tools -> references, make sure DAO x.x is checked):

Dim db As DAO.Database
Dim rs As DAO.Recordset
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20377932
what about this (early binding):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
 
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryRptWeeklyEmail")

late binding:

Dim db As Object
Dim rs As Object
Dim qdf As Object
 
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryRptWeeklyEmail")



0
 
LVL 75
ID: 20377944
Be sure you have a reference set to DAO 3.xx in Tools>>References ....

mx
0
 
LVL 2

Author Comment

by:michael1174
ID: 20377957
I'm giving my client the mde and i dont want to have to specify the reference.....
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20377965
Then angeliii's second suggestion should work for you.
0
 
LVL 75
ID: 20378002
Why not just:

Set qdf = CurrentDb.QueryDefs("qryRptWeeklyEmail")

0
 
LVL 57
ID: 20378022

  If this is an Access app (which it sounds like since you said your giving the client a MDE) then I would not use late binding.  There is a substantial performance hit in doing so.  Besides, there is not a ton that can go wrong with DAO as a reference.  It's considered part and parcel of Access (actually JET).

JimD
0
 
LVL 2

Author Comment

by:michael1174
ID: 20378036
databaseMX, I'll try that as well...it works fine on my computer, but doesnt on my client's computer...
0
 
LVL 75
ID: 20378060
"but doesnt on my client's computer..."

Why not ? It should work on any computer.

mx
0
 
LVL 2

Author Comment

by:michael1174
ID: 20378084
my client is still getting the error... maybe its the late binding on my outlook procedure....does it look right?
Private Sub EmailActivityReport(strClientName, strEmail)

Dim objOutlook As Object

Dim objOutlookMsg As Object

Dim objOutlookRecip As Object

Dim objOutlookAttach As Object

Dim arrEmail

Dim l_counter

Const olTo As Long = 1

Const olMailItem As Long = 0
 

' Create the Outlook session.

Set objOutlook = CreateObject("Outlook.Application")
 

' Create the message.

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
 
 

With objOutlookMsg

    arrEmail = Split(strEmail, ",")
 

    For l_counter = 0 To UBound(arrEmail)

        Set objOutlookRecip = .Recipients.Add(arrEmail(l_counter))

        objOutlookRecip.Type = olTo

        Set objOutlookRecip = Nothing

    Next
 
 

   ' Set the Subject, Body, and Importance of the message.

   .Subject = "Invoice Activity Summary For " & strClientName

   .Importance = 2 'high importance

   .HTMLBody = strReport

   
 

   ' Resolve each Recipient's name.

   For Each objOutlookRecip In .Recipients

       objOutlookRecip.Resolve

   Next
 

   .Send
 

End With

Set objOutlook = Nothing

Set objOutlookMsg = Nothing

Set objOutlookRecip = Nothing

Set objOutlookAttach = Nothing

End Sub

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Author Comment

by:michael1174
ID: 20378118
does anything look wrong here:
Public Sub CreateEmailActivityReport()

Dim rs As Object

Dim qdf As Object

Dim strEmail As String

Dim intClientID As Integer

Dim intLastClientID As Integer

Dim curTotalOriginalBalance As Currency

Dim curTotalRevisedBalance As Currency

Dim curTotalTotalCredits As Currency
 

Set qdf = CurrentDb.QueryDefs("qryRptWeeklyEmail")
 

qdf!pClientID = Forms!frmReports!cboClientID

qdf!pInvoiceFromDate = Forms!frmReports!txtInvoiceFromDate

qdf!pInvoiceToDate = Forms!frmReports!txtInvoiceToDate

qdf!pCarrierID = Forms!frmReports!cboCarrierID

qdf!pAuditorID = Forms!frmReports!cboAuditorID
 
 

Set rs = qdf.OpenRecordset
 

If rs.EOF Then

    MsgBox "Please assign email addresses to clients before running this report", vbInformation, strCompany

    Exit Sub

End If
 

.....

Open in new window

0
 
LVL 75
ID: 20378147
What line of code does that error occur on ?

mx
0
 
LVL 2

Author Comment

by:michael1174
ID: 20378182
in my original post, it was occuring on the :

Set db = CurrentDb()

but once i changed
Dim db As Database

to

Dim db As Object

it was fine on my computer...

that was only way i could duplicate the error... nothing else has given me the error, so i'm not sure where it exactly is occuring on my clients computer...
0
 
LVL 75
ID: 20378194
Do you have a reference set to DAO for sure ? When you create a new MDB and go into code, the default reference (sadly) is ADO.

mx
0
 
LVL 2

Author Comment

by:michael1174
ID: 20378217
I don't have a reference to DAO.. should I? if I am giving them an mde.. also, sorry, this is an access 2007 accde.

I've defined it as:


Dim rs As Object

Dim qdf As Object
 

Set qdf = CurrentDb.QueryDefs("qryRptWeeklyEmail")
 

Set rs = qdf.OpenRecordset

Open in new window

0
 
LVL 75
ID: 20378246
I believe OpenRecordset in Set rs = qdf.OpenRecordset is going to require a Reference to DAO.  Can you try that?

mx
0
 
LVL 2

Author Comment

by:michael1174
ID: 20378270
ok, i try and add it and i get this error:

name conflicts with existing module, project, or library

i have checked:

visual basic for applications
microsoft access 12.0 object library
ole automation
microsoft office 12.0 access database engine object library
microsoft activex data objects 2.8 library
0
 
LVL 2

Author Comment

by:michael1174
ID: 20378288
i just removed:

microsoft office 12.0 access database engine object library

and it allowed me to add it...

gonna see if it works at my clients cpu...
0
 
LVL 75
ID: 20378321
ok ... you're in A2007, right?

I guess microsoft office 12.0 access database engine object library is the DAO reference ... I'm not at my A2007 machine right now.

Typically that reference would look like:

Microsoft DAO 3.6 Object Library ....

mx
0
 
LVL 75
ID: 20396718
soooo, what was the problem ?

mx
0
 
LVL 2

Author Comment

by:michael1174
ID: 20397093
It actually had something to do with either the outlook automation call or the dao call, and my client didn't have the windows automatic update on, so my version of access 2007 was newer than his version.. the MSO numbers were different... once he downloaded microsoft updates, the code worked like a charm.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 43
Before insert Image (OLE) resize image 11 43
Create Form using Wizard 14 39
MS Access Tables Linking 6 43
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now