JohnT
JohnT

Reputation: 49

Retrieve value from Access table in Excel

I have an Excel file that exports data into Word. It includes a cover page and grabs the user name ("First.Last") and changes it to "First Last" but I also need to include the user's professional title. This information is housed in an Access table. It has a field called Name and a field called Title. The Name field match exactly to User with no duplicates.

I have tried about eight different methods I've found online to grab this value from the table. The table will never happen to be open so I can't use "CurrentDB()".

I just need to be able to reach into the table in a database, grab the "title" value given that the value of the field "Name" is equal to the value of User (user name from the environment - the person using the excel file).

If it helps, I can provide examples of the different chunks of code I've used so far.

User is the username from the environment tblPickName is the table I am trying to open Path is the directory and file where the table is located

tblPickName has 2 fields, Name and Title I need to grab the Title from this table and set it to my variable "Title" as long as Name equals User. Then I can export the username and title to Word along with the rest of the data.

Dim Path As String
Dim User As String
Dim Title As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

User = Environ("UserName")
User = Replace(User, ".", " ")
User = StrConv(User, vbProperCase)

Path = "Directory\Name.mdb"

Set db = DBEngine.OpenDatabase(Path)
Set rs = db.OpenRecordset("SELECT tblPickAnalyst.Title FROM tblPickAnalyst WHERE [Analyst]='" & User & "'")

Title = rs!Title

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

docWord.Bookmarks("AnalystName").Range.Text = User
docWord.Bookmarks("AnalystTitle").Range.Text = Title

Upvotes: 0

Views: 1570

Answers (3)

JohnT
JohnT

Reputation: 49

Here is the final solution. Thank you to everyone who helped!

Dim Path As String
Dim User As String
Dim Title As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

User = Environ("UserName")
User = Replace(User, ".", " ")
User = StrConv(User, vbProperCase)

Path = "Directory\FileName"

Set db = DBEngine.OpenDatabase(Path)
Set rs = db.OpenRecordset("SELECT tblPickAnalyst.Title FROM tblPickAnalyst WHERE [Analyst]='" & User & "'")

Title = rs!Title

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

docWord.Bookmarks("AnalystName").Range.Text = User
docWord.Bookmarks("AnalystTitle").Range.Text = Title

Upvotes: 0

bp_
bp_

Reputation: 432

Try this:

Public Sub JohnTayloristheBest()
Dim conAccess As ADODB.Connection
    Set conAccess = New ADODB.Connection
Dim rdsAccess As ADODB.Recordset
Dim strTitle as String

With conAccess
    .ConnectionString = "Data Source= **insert filepath here** "
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open
End With

With rdsAccess
   .Open "SELECT tblPickName.Title FROM tblPickName WHERE tblPickName.Name = '" & Environ("username") & "';", conAccess
   If Not .EOF And Not IsNull(.fields(0)) Then
        strTitle = .fields(0)
   Else
        Msgbox "Error: No record in the database for " & Environ("username")
   End If
   .Close
End With

conAccess.Close: conAccess = Nothing
End Sub

Be sure to select the correct references by doing this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms677497(v=vs.85).aspx

Also, this is my first answer ever written on this site (or any other for that matter), so be kind.

Upvotes: 1

Falo
Falo

Reputation: 371

Try this:

Public Function getTitle(name As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset

  Set db = DBEngine.OpenDatabase("E:\Temp\Database.mdb")
  Set rs = db.OpenRecordset("SELECT Title FROM tblPickName WHERE Name='" & name & "'")
  If rs.RecordCount > 0 Then
    getTitle = rs!Title
  End If
  rs.Close
  db.Close

End Function

Ensure read access on table tblPickName (for Administrator)

Upvotes: 0

Related Questions