Word, VBA and a Database

Hi Guys,

I need to be able to use VBA to connect to a Microsoft Access Database, use SQL to query my database and insert values at bookmarks all within a Microsoft Word Document.

I am sure it can be done but need some information on the references and code etc... I know how to do it in ASP but can't figure this VBA out. I know how to place values into the word document etc using VBA, just not the databse connection etc...

Thanks
Shane

Hi,

This is the way normally i use to connect to my database.
Hope it helps u..

VB Code:

Dim w As Workspace
Dim w As Workspace
Dim db As Database
Dim rst As Recordset
 
Set w = DBEngine.Workspaces(0)
Set db = w.OpenDatabase("c:\mis.mde")
Set rst = db.OpenRecordset("tbl_months")
    If Not rst.RecordCount = 0 Then
        MsgBox rst.RecordCount
    End If

*****You need to refer Microsoft DAO for this in u r VBA..
All the best :thumb:

Thanx & Regards
Anu.

Ok... once I have that... how would I use SQL to query the table, and return a particular record set etc so I can then put particular fields of my record into particular places on my document.

The bookmarks in your document I don't know, but instead of opening a table you could open your query in the recordset:

Set rst = db.OpenRecordset("SELECT fldFoo FROM tblBar WHERE fldFoo = 'Holy Cow'")

How can you specify the databse password when you connect to your database? IE: my access database has a password set on it.

Chk it out ....this will helps u...

VB Code:

Set db = w.OpenDatabase("c:\db11.mdb", False, False, "MS Access;PWD=123")

change u r password over here and place the same in ur code..
Im sure ...It will works...
All the best.. :thumb:


Thanx & Regards
Anu..
(Please rate the post if it works 4 u...)