Use VBA to access SQL database

[Huw]

I am attempting to open a query within a userform that interrogates a SQL
database (password protected) and then return results from other columns for
the same client code as entered by the user in the form.

I set up a link to the database for mail merge within word, but accepted
that I would need to take that a step further to get the interrogation into
the userform. In setting up the link to the server I created and .odc which I
thought would then be accessible within VBA, but I don't appear to be able to
access that data feed within VBA.

Sure I am missing something trivial, but could someone humour me and provide
some ideas please?

Thanks

Huw

 

[Doug Robbins - Word MVP]

With the mail merge main document as the active document, run a macro
containing the following code

With ActiveDocument
.Range.InsertAfter .MailMerge.DataSource.ConnectString
End With

It will insert the connection string at the end of the document.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[Peter Jamieson]

There are really two main ways to access data using /Word/ VBA:
a. use MailMerge.OpenDataSource. If you are already doing that
successfully using your .odc, then the problem you are most likely to
face is getting the data out of Word's object model.
b. use an external object library such as ADODB. That's harder but you
get a lot more control.

If you have to use a username/password combination to access your SQL
database, you also have to decide how to acquire that information, and
the pssobility that it may be stored in the document if you save it.
That may well happen if you use MailMerge.OpenDataSource because it
stores connection details with the Word document. It would /probably/
only happen with an ADODB-based solution if you chose to hardcode the
information or save it in some way (such as in Word Document Variables),
but I couldn't say I was completely sure about that. If you are actually
using SQL Server with "Windows Authentication" then you don't need
username/password info.

Peter Jamieson

http://tips.pjmsn.me.uk