Pulling SQL data into Word Document

[rrd80134]

I'm needing to query an SQL database and pull some information into a Word Document. This is NOT a Mail Merge Document. I'm trying to acomplish this through VBA but not sure how to do it. Couldn't find any resources to help me do it on the Internet. Any help with this would be greatly appreciated.



Thanks for your help

Randy Davis
Denver, CO

 

[SkipVought]

Hi,

It may not be a "mail merge", but WHY NOT?

I have used "mail merge" to customize a children's book. Didn't "mail" a thing! Heck, it gets data into your document exactly where you need it.

What are you trying to do with inserting data? Please be clear, concise and complete with your explanation.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CaptainD]

Another way is to create a function that returns a recordset and then under some event retrieve your data.

First reference ADO (Mine references ADO 2.6)

'In a Module
Function GetADORS(strSql As String) As ADODB.Recordset


Dim objConn As ADODB.Connection

Set objConn = New ADODB.Connection

objConn.Open "Provider=sqloledb.1;data source=YourServersName;Initial catalog=YourDataBaseName;Integrated Security = SSPI;"

'If you want to code in a name and password
'objConn.Open "Provider=sqloledb.1;data source=YourServerName;Initial catalog=YourDatabaseName;User Id=SomeName;Password=SomeNamesPassword;"


Set GetADORS = New ADODB.Recordset
GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly

End Function

Then under some event

'Error trap code here

Dim strSql as String
Dim rs as ADODB.RecordSet

Set rs = GetADORS(strSql)

If (rs.BOF And rs.EOF) = True THEN
  MsgBox "No Records retrieved"
Else
  'Do something with the data
End If

That should help get you started

 

[CaptainD]

Don't forget to close out the recordset when your done by setting it to nothing.

At the bottom of the event

Set rs = Nothing

 

[HarleyQuinn]

And maybe close the recordset before setting it to nothing

rs.Close

If it were possible that the rs might not be open when you try to close it then you would probably check the rs.State before trying to .Close it. It's probably overkill in this scenario though

Regards

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[rrd80134]

Sorry about the lack of details. Sometimes I feel like I give too much information.

I am created a Word Template that, when double-clicked to create a new document the following occurs:

Input Box request an Order Number. Order Number is used to query an SQL database to pull in Customer Information. Then a new row is added at the end of an existing table, filling in CurrentDate, CurrentTime and the user name from Word in the appropriate fields. At this point (before saving the document), the code for the InputBox and guerying the database needs to be disabled, but adding a new row to the table should occur each time the resulting document is opened.

I have some code for adding the table row. Thanks for the code for querying the database. I'll see if I can make it work. How do I go about disabling that part?

Again, thanks for all the replies.


Thanks for your help

Randy Davis
Denver, CO

 

[CaptainD]

Posting your code help us to see what you have and what you have tried which makes it easier for us to help.

If I understand you correctly, you only want to query the database when the "Document" is first created from the template. Once it has been created, you do not want it to be able to query the database but you still want it to "log" when it was open and by whom.

If that is correct then you would need something to distinguish the document from the others when it opens.

You could use the documents unique "Name" but that would require you to prompt for the name when it was first created and you would need to make sure it does not already exist before you name it as well as some other integrity issues you would need to check for.

The nice thing about that, in your "Log" database you would have the documents name along with when and who opened it.

From there you would just do a query to see if it exist, if so, then the code would exit out of the query code.

As far as the code I posted to query the database, it only "reads", I can post a similer function that will "Execute" a query if you need it but you stated you have code to write to the database.