Access Front / SQL Back - create view

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?

LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
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.

Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
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.
Kelvin SparksCommented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Helen FeddemaCommented:
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):

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
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....
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.

Experts-Exchange Cleanup Volunteer
Helen FeddemaCommented:
DDE is very, very old -- pre-VBA Automation.  Any code that uses DDE should definitely be upgraded.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.