Query SQL?

[Linn Kubler]

Hi,

I am wondering if it is possible to write a query in VBA and return the
results in a format that I can use to populate a Word document. Examples
would be great!

I'm using Word 2003 and SQL 2000 Server.

Thanks in advance,
Linn

 

[voudou Joe]

Hi,

I am wondering if it is possible to write a query in VBA and return the
results in a format that I can use to populate a Word document. Examples
would be great!

I'm using Word 2003 and SQL 2000 Server.

Thanks in advance,
Linn

Linn -

Yes, it is possible and it's not really difficult. You can use a
number of methods - search the VBA help and online for ADODB and DAO
You first connect to a db, however. Look at http://www.connectionstrings.com/
to get the right connectin string for SQL Server

The examples below are pretty simple ...

rgds - voudouJoe

Public Function RunSQL(Optional strSQL)
Dim SQLrecordset As ADODB.Recordset

If DBconn Is Nothing Then DBconn_open

Set SQLrecordset = New ADODB.Recordset
SQLrecordset.Open strSQL, DBconn

Set RunSQL = SQLrecordset
Set SQLrecordset = Nothing

End Function

Public Sub DBconn_open(Optional DBLocation, Optional ChooseDB)
Dim fs As Object, DBpath As String, DBFile As String

'create a new db connection; DBconn is public
Set DBconn = New ADODB.Connection

'create a fs object to BUILDPATH and get db settings from registry
'search VBA help for GetSetting and SaveSetting functions
Set fs = CreateObject("Scripting.FileSystemObject")
If IsMissing(DBLocation) Then DBpath = GetSetting(appName,
"Settings", "curDBpath")
If IsMissing(ChooseDB) Then DBFile = GetSetting(appName,
"Settings", "curDBfilename")

'create the final db connection to MS database
With DBconn
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
fs.buildpath(DBpath, DBFile)
End With

End Sub

'close the db when ready
Public Sub DBconn_close()
DBconn.Close
Set DBconn = Nothing
End Sub

'Const DBname As String = "PROD"
'Const DBuid As String = "username"
'Const DBpwd As String = "password"

''################################
''ADODB for ODBC Connections
''################################
''open the db connection
'Sub DBconn_open()
' 'use apublic variable so the connection stays open even if the
vbs instance ends
' Set DBconn = New ADODB.Connection
' DBconn.ConnectionString = "DSN=" & DBname & ";uid=" & DBuid &
";pwd=" & DBpwd
' DBconn.Open
'End Sub
''close the db when ready
'Sub DBconn_close()
' DBconn.Close
'End Sub

 

[Richard Relpht]

I don't want to go "off topic" but Word can probably read the data through
MSQuery and an ODBC connexion without having to write any VB stuff at all.

Excel can do that and autoupdate if you want.
(But if you must use Word, well I'm not sure.)

In Excel there's no need to code.
Just set it up and it'll show you the data and/or graphs, and optionally
update at any given interval, or, optionally, just every you open the sheet.

The incoming data can be filtered, sorted, whatever...

Richard.