VBA form in Word to link to Access database

[fiona]

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!

 

[Perry]

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

 

[Perry]

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:
[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
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!

 

[fiona]

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

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:
[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
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!