Using Macro to connect to ODBC and run SQL script

User avatar
Timelord
4StarLounger
Posts: 504
Joined: 23 Jul 2010, 18:36
Location: Westlake, OHIO

Using Macro to connect to ODBC and run SQL script

Post by Timelord »

I would like a Macro to run in Word that would then connect to an ODBC source, then an SQL script run to capture values from the database. Is this a pipe dream, or is it possible?

Thanks
Who will you Inspire today?

User avatar
Timelord
4StarLounger
Posts: 504
Joined: 23 Jul 2010, 18:36
Location: Westlake, OHIO

Re: Using Macro to connect to ODBC and run SQL script

Post by Timelord »

Sorry, I have more info. I want those values then to fill fields on the Word document.
Who will you Inspire today?

User avatar
HansV
Administrator
Posts: 78039
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using Macro to connect to ODBC and run SQL script

Post by HansV »

That should be doable:
- Create an ADODB connection to the database.
- Open a recordset.
- Get data from the fields of the recordset and use them to populate fields in the Word document.
Best wishes,
Hans

User avatar
Timelord
4StarLounger
Posts: 504
Joined: 23 Jul 2010, 18:36
Location: Westlake, OHIO

Re: Using Macro to connect to ODBC and run SQL script

Post by Timelord »

The script that I want to run would include variables based upon fields captured on an html form. It would be something like:
select alphanum from jkl.running where primaryvalue = [variableprovided]

Then paste the alphanum into a Mail Merge field or some other way of telling the system where it belongs on the letter.

I cannot use a recordset as the values will be changing daily.
Who will you Inspire today?

User avatar
HansV
Administrator
Posts: 78039
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using Macro to connect to ODBC and run SQL script

Post by HansV »

The idea would be to generate a SQL string dynamically in your VBA code, i.e. in runtime.
Then open a recordset on that dynamically generated SQL string.
Best wishes,
Hans

snb
4StarLounger
Posts: 541
Joined: 14 Nov 2012, 16:06

Re: Using Macro to connect to ODBC and run SQL script

Post by snb »

You can link a Word merge master document to any database.
No Macro needed.

User avatar
Charles Kenyon
4StarLounger
Posts: 594
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Using Macro to connect to ODBC and run SQL script

Post by Charles Kenyon »

snb wrote:
01 Oct 2023, 15:34
You can link a Word merge master document to any database.
No Macro needed.
I think Timeford wants this to run without user intervention at that point. Anything needed would be in the macro or the html form.

robertocm
Lounger
Posts: 43
Joined: 07 Jun 2023, 15:34

Re: Using Macro to connect to ODBC and run SQL script

Post by robertocm »

I use this for invoices, with data from SQL Server (see the macro: "Consulta_Factura"):

invoice_template.docm

snb
4StarLounger
Posts: 541
Joined: 14 Nov 2012, 16:06

Re: Using Macro to connect to ODBC and run SQL script

Post by snb »

Charles Kenyon wrote:
01 Oct 2023, 20:50
snb wrote:
01 Oct 2023, 15:34
You can link a Word merge master document to any database.
No Macro needed.
I think Timeford wants this to run without user intervention at that point. Anything needed would be in the macro or the html form.
Nothing simpler without user intervention than a macro:

Code: Select all

With documents.open(G:\OF\master.docx")
   .mailmerge.execute
   .close -1
 end with 
 activedocument.printout

User avatar
Timelord
4StarLounger
Posts: 504
Joined: 23 Jul 2010, 18:36
Location: Westlake, OHIO

Re: Using Macro to connect to ODBC and run SQL script

Post by Timelord »

Thank you all. This is all good information.
Who will you Inspire today?