VBA form in Word to link to Access database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fiona - 10 Apr 2007 21:18 GMT

I'm using VBA to manipulate a Word document and desire to link it to a
database, preferably Access. The purpose of this is to present the user with
a login form on opening a document: the username and password shall be
verified with a list of authorised users in the database, and then settings
relative to that user shall be evoked.

Could anybody recommend a tutorial or provide some sample code to solve this
problem? At this stage, even just help establishing the link would be
sufficiect.

Thanks in advance!

Reply to this Message

Perry - 10 Apr 2007 21:42 GMT

From Access VBA, you could do something like:
Example parameters:
[tblUsers] is the table hosting authorized users and you're coding
from a "login" form containing 1 textbox: [Text1] to host username

If nz(dlookup("UserName", "tblUsers", _
"UserName=" & chr(34) & _
me.text1 & chr(34)) <> "" Then

Dim wd as new word.application
dim doc as word.document
set doc = wd.documents.add(c:\path\template.dot")
' <other code to manipulate the document
Else
msgbox "y're not registered as an authorised user"
end if

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> I'm using VBA to manipulate a Word document and desire to link it to a
> database, preferably Access. The purpose of this is to present the user
[quoted text clipped - 10 lines]
>
> Thanks in advance!

Reply to this Message

Perry - 10 Apr 2007 22:10 GMT

Or from Word VBA, do something like

Example parameters:
userform containing 1 textbox (Textbox1) to host username
Access: table [tblusers] containing [UserName] field with authorized users.
Set a reference in Word VBA pointing to: ADO object library and go

Dim cn as new ADODB.connection
Dim rs as new ADODb.recordset

cn.connectionstring = _
"Provider=Microsoft.Jet.oledb.4.0;" & _
"Data Source=d:\data\access\mydatabase.mdb;"

cn.open

rs.open "select username from tblusers where username = " & _
Chr(34) & me.Textbox1 & chr(34), cn

If rs.recordcount < 1 then
Msgbox "y're not registered as an authorized user"
Else
'Access granted and proceed with
'doc manipulation code here
End if

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> From Access VBA, you could do something like:
> Example parameters:
[quoted text clipped - 35 lines]
>>
>> Thanks in advance!

Reply to this Message

fiona - 11 Apr 2007 12:40 GMT

Thanks, the Word VBA example looks like it will work. The confusion was that
I could not find a method in VBA to open the connection, only VB6 and VB2005.

Thanks again!

> Or from Word VBA, do something like
>
[quoted text clipped - 69 lines]
> >>
> >> Thanks in advance!

Reply to this Message