Open Access database from command button in Word

[Rich]

I'm trying to open an Access database from a command button in Word
The username is part of the path to the file, so I want to use the Environ("username") variable so I can send the same document to a number of users and the button puts each one in their own directory

So far, I've tried a hyperlink using %username% for that part of the path--but no luck.

Next, I made a hyperlink that runs a batch file with %username% in it. This works, but keeps the command prompt window open until the user closes Access. I would use this method if I could hide the command prompt window

Then, I tried what I thought were several applicaple examples of how to open files from command buttons using VB code I found in the newsgroups, but couldn't get any of them to work

Thanks for your help
-Rich

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

I'm not sure whether your problem is opening Access or just the username
part of it/

Here's a routine that loads a listbox on a user from with data from an
Access table:

Private Sub UserForm_Activate()

'allocate memory for the database object as a whole and for the active
record

Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer

'Open a database

Set myDataBase = OpenDatabase("D:\Access\ResidencesXP.mdb")

'Access the first record from a particular table

Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)

'Get the number of fields in the table

j = myActiveRecord.Fields.Count

'Get the number of Records in the table

'Loop through all the records in the table until the end-of-file marker is
reached

i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close

'Set the number of columns in the listbox

ListBox1.ColumnCount = j

' Define an array to be loaded with the data

Dim MyArray() As Variant

'Load data into MyArray

ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.Fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n

' Load data into ListBox1

ListBox1.List() = MyArray

'Then close the database

myActiveRecord.Close
myDataBase.Close

End Sub

To get the name of the logged on user, see the article “How to get the
username of the current user” at:

http://word.mvps.org/FAQs/MacrosVBA/GetCurUserName.htm


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Rich]

Thanks for looking into this, but that isn't my problem. Once Access is open, there is a process that looks up the person's username from the system and that works fine. My problem is getting the database open from a Word document, either with a command button or a hyperlink.

We are using a split database with the backend in one folder and the users' front-ends distributed to folders with names that are equivalent to their usernames. Unfortunately some people are too dumb to find their own folders, so I want to send an e-mail with a link or button in it that will open the copy of the front end in their folder. That's all it has to do--the database is set to open the proper form and look up the user name to be used on the form

I've had success with a hyperlink to a batch file, but the command prompt window stays open until the user closes the database. This would be ok if the command prompt window was hidden.
I've had success with the Shell command in VB opening MS Access in the right path--but no luck getting the file to open

For example, the user Sam has his front-end in N:\Sam\Front End.md
I want the link or button that I embed in Word (and therefore the e-mail) to open N:\Sam\Front End.mdb. Once the database is opened, I have it set to open the right form, lookup the username and lock out other users' data

Thanks
-Rich

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Use a shortcut to the front end database. If you insert that into a
document, and the user double clicks on it, the database will be opened.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP