WORD to query MS-SQL

[Kindly_Kaela]

Hello...

I've just finished building a Proposal Generator in Word. The content and formatting is great, but I have one final step that is giving me nightmares.

I really need this to query an MS-SQL database and pull the data into the 30 page proposal. The data is customer pricing, and would be inserted into a pretty word formatted table.

I'll be honest, I'm what you call a total "NEWB". I just picked up VBA 1 month ago. I'm doing great formatting Word and Excel thanks to this website. But a lot of the code I get from you guys is still kind of confusing (but it works :thumb).

I don't even know where to begin with MS-SQL and the guy who manages our MS-SQL database won't give me the time of day (recent X-Boyfriend). I can go over his head and get access, but where to go from there is my concern.

What do I do?? Where do I start??

Thanks,
Kaela
:ipray:

[matthewspatrick]

Kaela,

What is the nature of the data you have to grab? Are they all contained in one query, or would you need multiple queries to get them? If the former, consider a MailMerge. If the later, you will probably need to use ADO (which a great many people here can help you with).

Also, do you need to do simple SELECT statements or do you need calculations to occur? If you need anything beyond simple joins, it may be useful to get views and/or sprocs set up for you in the SQL Server db.

[mdmackillop]

Hi Kaela
This is a demo utility I created which used Excel to populate a word document. If there are any aspects of this which appear of use, please ask.
Regards
MD

[Kindly_Kaela]

Hi Matthew! A view can be created for me that will allow it to be 1 query with a variable needing to be passed to specify which rows to return (that's what our SQL guy said). And just simple select statements, no calculations necessary (a basic copy/paste). If I did the mailmerge, would the user have to go through the steps everytime, or would I be coding it to work seemlessly for the end-user? With all that said....what next my friend?


Hey MD! Did you mean to attach your file? I don't see your demo.

Thanks for the replies guys!!
Kaela
:mkay

[mdmackillop]

Ooops!
http://vbaexpress.com/kb/getarticle.php?kb_id=184

[Bob Phillips]

I feel really bad about suggesting you go to the Word group, assuring you that the Word guys would looke after you, and what do they do, ignore you.

As such, I have knocked you up a little demo.

A few points to note up-front:

- I have done it in Access against the Northwind database, I don't have SQL Server available at the moment, but SQL is SQL

- my query is relatively complex, but I believe you said you will have a view, so that will be much simpler for you

- the demo makes use of a simple form to show the available customers and let the user pick from that - I assume you will want something similar. My form has a listbox, which I set to 2 columns for id and name, and a button, the only code is Me.Hide behind the button

- I createc a document with two bookmarks, ph_Name and ph_Qty. I assume you will have many more but the principla is the same.

Main logic:

- retreive all customers from the database using ADO
- list them on a form
- pass selected id to query SQL string
- query databse using ADO
- load the data into the bookmarks.

Have a look, any questions, fire away



Option Explicit

Sub Pricing_04()
Dim sText As String
Dim sBookmark As String
Dim aryData

aryData = GetData()

UpdateBookmark bookmark:="ph_Name", val:=aryData(1, 1)
UpdateBookmark bookmark:="ph_Qty", val:=aryData(11, 0)
End Sub

Private Function UpdateBookmark(ByVal bookmark As String, ByVal val As String)
Dim rng As Range
If ActiveDocument.Bookmarks.Exists(bookmark) Then
Set rng = ActiveDocument.Bookmarks(bookmark).Range
rng.Text = val
ActiveDocument.Bookmarks.Add bookmark, rng
Else
MsgBox "Bookmark " & bookmark & " not found."
End If
Set rng = Nothing
End Function

Function GetData() As Variant
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Const sFile As String = "C:\Program Files\Tools\development\Visual Studio\VB98\NWIND.mdb"
Const sSQLCusts As String = _
"SELECT DISTINCT Cust.CustomerID, " & _
" Cust.CompanyName " & _
"FROM Customers AS Cust; "
Const sSQLQuery As String = _
"SELECT DISTINCTROW Orders.CustomerID, " & _
" Customers.CompanyName, " & _
" Customers.Address, " & _
" Customers.City, " & _
" Customers.Region, " & _
" Customers.PostalCode, " & _
" Customers.Country, " & _
" Orders.OrderID, " & _
" Orders.RequiredDate, " & _
" [Order Details].ProductID, " & _
" Products.ProductName, " & _
" [Order Details].Quantity " & _
"FROM Products " & _
" INNER JOIN ((Customers " & _
" INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) " & _
" INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) " & _
" ON Products.ProductID = [Order Details].ProductID " & _
"WHERE Customers.CustomerID = '"
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sSep As String
Dim sCustId As String
Dim i As Long
Dim ary

sSep = Application.International(wdListSeparator)

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQLCusts, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

ary = oRS.getrows

With UserForm1
With .ListBox1
.ColumnCount = 2
.ColumnWidths = "50,200"
For i = 0 To UBound(ary, 2)
.AddItem ary(0, i)
.List(.ListCount - 1, 1) = ary(1, i)
Next i
End With
.Show
sCustId = .ListBox1.Value
End With

oRS.Close

oRS.Open sSQLQuery & sCustId & "';", sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
GetData = oRS.getrows
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Function

[matthewspatrick]

- I have done it in Access against the Northwind database, I don't have SQL Server available at the moment, but SQL is SQL

Bob,

Nice work on the utility! At the risk of being nitpicky on a minor point, there are some syntactical differences in "dialect" between Jet (Access's db engine) and SQL Server. For example, in Access you would use IIf() while in SQL Server you would use CASE.

On the SQL statement you included, you are correct that the syntax is the same in both environments, but that may not always be so.

[Bob Phillips]

Patrick,

I know you are right. But as I said, I didn't have access to SQL Server, so I just knocked up a rough and ready Access query.

Het buddy, you are talking to an old Sybase SQL jock. Transact-SQL is where MS 'stole' it from.

[jdubya]

I feel really bad about suggesting you go to the Word group, assuring you that the Word guys would looke after you, and what do they do, ignore you.

You have to really watch out for that Word group over there..:rotlaugh:

Bob,

That's some impressive code. I've been watching this thread since Kaela posted her question, since I was interested in how the problem could be solved.

[matthewspatrick]

Patrick,

I know you are right. But as I said, I didn't have access to SQL Server, so I just knocked up a rough and ready Access query.

Het buddy, you are talking to an old Sybase SQL jock. Transact-SQL is where MS 'stole' it from.

I had no idea T-SQL started with Sybase :)

[Bob Phillips]

That's some impressive code. I've been watching this thread since Kaela posted her question, since I was interested in how the problem could be solved.

Thanks jdubya, I hope Kaela hasn't switched off.

[Bob Phillips]

I had no idea T-SQL started with Sybase

That's the advantage of being in the groups, the diversity of the people can share all sort of info. I must be one of the few (only) Sybase developers in the group though. Sybase was very big with finan cial institutions back 10 years ago, lost their way a bit now.

Not only did MS steal T-SQL, they also stole NT. They bought a guy from Dec, who had VMS. The core system was so similar, they even used the name (VMS) just shifting the letters Windows NT.

[mileski]

Hi guys, :)

I don't think MS stole T-SQL, they just bought the product from Sybase, and developed it furthermore. :)

Anyway, after a while, Sybase changed their minds, and also developed t-sql separately. But it was too late. :)



Best Regards,
Robert Mileski

[Kindly_Kaela]

Wow, some great replies here. Sorry I didn't reply sooner....I just returned from a 5 day vacation.

XLD (or Bob?): I can't wait to dive into your code!! Thank you thank you thank you!!!! :hug:

[Kindly_Kaela]

I've been playing with XLD's code and have some questions....

1) Do you know the syntex for connecting to a SQL data base?

2) Would this code change much if we used a TextBox instead of a ListBox for the input data? The users would just type one unique value and hit submit. I would then want to display the data based on this value.

3) On the UpdateBookmark section, what does the (# , #) refer to? You have (1, 1) and (11, 0)

Thank you!!
:cloud9:

[Bob Phillips]

1) Do you know the syntex for connecting to a SQL data base?
There are various syntaxes depending upon your server setup. See Carl's (http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer) site


2) Would this code change much if we used a TextBox instead of a ListBox for the input data? The users would just type one unique value and hit submit. I would then want to display the data based on this value.
I used a listbox as I thought it best to retreive all the customers first and let them select from the list. This is a far better method than a textbox, as they may type in the wrong value, and you would need to check the returned data. With a listbox, they can only select from customers on the database.


3) On the UpdateBookmark section, what does the (# , #) refer to? You have (1, 1) and (11, 0)
That is the element within the two-dimensional array that I want to update. In the code, I copied the returned recordset into an array for simpler processing. In reality, you will only pull back the data that you want, so you will use all elements, and you will update a lot more bookmarks.

[Bob Phillips]

Oh, and Bob. xld is just a handle that I used when I first joined. Nowadays I join forums by my proper name.

[Kindly_Kaela]

I hate to admit it, but I'm still clueless. I've been banging my head against the wall trying to understand your code and how to incorporate it with SQL.

I tried this code from Carl's site alone and got a runtime error 424, object required.
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

I'd have to use a text box because we have hundred's of customers. I think that would be too much for a drop down menu.

Maybe I should switch to flipping burgers at McDonalds.

:(

[Bob Phillips]

It is not a question of incorporating it. There are (at least) two things that you need to alter, the connection to the database, and the SQL query.

First off, I would suggest that create some simple test macros to work out how to connect to a SQL-Server database, specifically your SQL_server database, and a simple SQL query against that databse, to get confidence with the commands and their syntax, before adjusting my offering.

Even with hundreds of customers, a combobox is still superior IMO. Remember that you can type in the first letter, second, etc. and the combo will jump to the first item matching, allowing quick drilling down to the correct customer. If you use a textbox, you can get a wrong entry, so you should at least do a simple initial query on just that customer name to validate it, rather than a full data query.

You really need to creat some bridges with your IT/database guys, you need their help. They should be able to get the connection string right, and if they are professsional, they should suggest writing a stored procedure, so that you could run that from your ADO, it will be vastly more efficient.