UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Oledb Data Source In Access, Office 2013    
 
   
David92595
post Mar 14 2017, 04:39 PM
Post#1



Posts: 522
Joined: 27-June 11
From: Temecula, California


I currently have a working mail merge macro in word, complete with button in the ribbon. my users will open the document they wish to merge and click the button that merges and auto saves the document to the correct location based on coding in the macro. I'm trying to integrate this into my access FE, but am running into an error I don't know how to fix. The below code works perfectly in word, but gives me an error in Access. The error is: Error 438, Description: Object doesn't support this property or method.

What am I doing wrong?!?!
The only think I changed was in SQLStatement1, I changed the reference to the current ID to use the local form in access.


CODE
ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\davidK\Documents\My Data Sources\Mail Merge Complaint.odc" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, _
         Connection:= _
        "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DatabaseName;Data Source=SQLServerName;" _
        , SQLStatement:="SELECT * FROM "" Mail Merge Complaint""", _
        SQLStatement1:="WHERE ID =" & ME.ID, SubType:=wdMergeSubTypeOther


Any help would be amazing!
Please note, I am specifically trying to use SQL OLDEB or ODBC. I have an .odc document already set up that will continue to work if my users save the merged template for later use and don't have Access open.
Go to the top of the page
 
LPurvis
post Mar 15 2017, 04:26 AM
Post#2


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Given that you're providing the connection information in the connection parameter, do you even need a saved odc file?
Aside from that, given that this is running in Access now, you'll want to fully qualify your Word objects.
i.e. instead of:
CODE
ActiveDocument.MailMerge.OpenDataSource Name:= _ ...

You'd have more like:
CODE
Dim objWord As Word.Application

Set objWord = CreateObject("Word.Application")
...
objWord.ActiveDocument.MailMerge.OpenDataSource Name:= _ ...


Any aside from that... you're running this in the form's module?
It definitely has a control or field on it named "ID" then? 100% definitely?

Cheers

--------------------
Go to the top of the page
 
David92595
post Mar 21 2017, 04:11 PM
Post#3



Posts: 522
Joined: 27-June 11
From: Temecula, California


Hi LPurvis,

sorry for the late response, I took an extended weekend.

Good catch, I do have the word object coding, but forgot to include it in my last post.

CODE
With objWord
    .Visible = True 'Make it visible

   .Documents.Open (strDir & strDocName)
  
ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\davidK\Documents\My Data Sources\Mail Merge Complaint.odc" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, _
         Connection:= _
        "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DatabaseName;Data Source=SQLServerName;" _
        , SQLStatement:="SELECT * FROM "" Mail Merge Complaint""", _
        SQLStatement1:="WHERE ID =" & ME.ID, SubType:=wdMergeSubTypeOther
    
End With


My users save the merged document to an ID related folder for future reference and for file specific alterations to the document. They also print it to PDF.
I've built several word merges thus far and this way is the only way I've been able to keep the data source working, if my users open it the first time from within Access, but at a future point in time need to make modifications to the document and re-merge it with current information from outside Access.

To your other questions:
I've tried running this from the forms module and from a Modules module (hope that makes sense). I can not get either working.

ID is defiantly a field on the current form and on the mail merge documents.

So everything looks right then???
Go to the top of the page
 
David92595
post Mar 21 2017, 05:31 PM
Post#4



Posts: 522
Joined: 27-June 11
From: Temecula, California


Not sure what I did, but I got it working.

It was either
1. moving the Open.Document line to outside of my With End coding
or
2. Changing the spacing in the SQL string I was using.

Either way, I'm elated.
Go to the top of the page
 
PhilS
post Mar 22 2017, 04:19 AM
Post#5



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
2. Changing the spacing in the SQL string I was using.

That most likely solved the problem. The leading space in "" Mail Merge Complaint"" was probably incorrect.
You can use square brackets around table and column names with spaces instead of the quotes. That is much more readable in VBA-Strings in my opinion.
E.g.:
CODE
... , SQLStatement:="SELECT * FROM [Mail Merge Complaint] ", _ ...

--------------------
Go to the top of the page
 
LPurvis
post Mar 22 2017, 05:01 AM
Post#6


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Glad you've got the source working.
Just to mention:
>> I do have the word object coding, but forgot to include it in my last post
CODE
With objWord
    .Visible = True 'Make it visible

   .Documents.Open (strDir & strDocName)
  
ActiveDocument.MailMerge.OpenDataSource Name:= _

You're still (seemingly) not using it consistently though.
Specifically, the ActiveDocument object is unqualified.
A simple thought test is "would it compile if you were using Late Binding and didn't have a Word reference added"?

Personally, I'd always look to have a Document object variable, such as:
CODE
Set objDoc = .Documents.Open (strDir & strDocName)
objDoc .MailMerge.OpenDataSource Name:= _

If you create new documents as a result of the mailmerge, then you still have a handle on the original and can save and close it or whatever else you need to do.
Failing that, you can just whack a dot in front of your ActiveDocument method. ;-)

Cheers

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 12:29 AM