Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access Front / SQL Back - create view

Posted on 2014-07-27
7
80 Views
Last Modified: 2016-06-07
I have an access front end and a SQL back end.  

I have a MailMerge that uses a dynamically Query from the access database

I want to create a button to open the mailmerge document and run it, but so far, when I do, it tells me it cannot run it because the access database is locked.

I figure I can get around this by having the Access front end create a view and run the mailmerge against the view instead of the query.

But I don't know how to create a new view via VBA code in the access database.  Can anyone guide me?

Thanks!
0
Comment
Question by:Lee W, MVP
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40223515
If you get a message that the database is locked, you're unlikely to be able to create a view. Can you select from the tables in question - if so, create a local Access query and use that. Alternately, create a pass-through-query - behaves like a view but you manage it from within Access. You can create the SQL locally (use SQL Server syntax), and set the sql in the same was as a dynamic query in Access.

I'd create a pass through query manually the first time (use properties to create the connection - accept the default connection) and save it - then just edit the sql on the fly from vba as you work.

Kelvin
0
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40223549
Thing is, the Access database opens, I create the Query, then use VBA to open the Word doc.  Or open the word doc manually, But with an access database being accessed through ODBC, I figure that Word shouldn't have a problem opening the view.

I'm not familiar with Pass through Queries.  Will definitely check that out.  My current alternative is to have the query exported to an excel file and then have the word doc use that... not ideal in my opinion, but might be workable.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40223555
I must admit, that use the other way around and have Access open word and push the data into book marks in Word. It's years since I used Word as the driver, for much the reasons that you describe. I just found it too hard. I do try to keep the vba Object related rather than setting a reference to Word and then getting a customer with the wrong version of Word.


Kelvin
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40225203
Personally, I prefer to push data to Word document properties, but there are many ways to merge Access data to Word.  See my old Four Ways to Merge to Word sample database (it still works):

http://www.helenfeddema.com/Files/code24.zip

Even more ways are available in recent versions of Office, as covered in my more recent Working with Word ebook.
0
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40225222
Thanks Helen, but I'm concerned - that's old stuff and the old stuff worked fine for this client... the DDE stuff... The query they had been using in word prompted for two user defined field values... that broke in 2007 or 2010... maybe earlier.  There's a kind of fix in a checkbox in General, but I'm trying to avoid that....
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41640937
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Helen_Feddema (http:#a40225203)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41641496
DDE is very, very old -- pre-VBA Automation.  Any code that uses DDE should definitely be upgraded.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 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