CODEWITHSUNDEEP
vbaautomationms-access-2010ms-word
user241895
user241895

Reputation: 1

Pull data from Access database to Word document

I am attempting to transfer data from an Access database into a Word document. I have performed a similar procedure using Excel and I need to code it using Access instead of Excel.

The Excel code (for an analogy is this)

Dim myworkbook As Excel.Workbook
Set myworkbook = GetObject("C:\Users\jn\Desktop\trial.xlsm")

then

Dim excelstr As String
Excelstr = Application.Range("A1:A100").Find("aword").Offset(0,1).Value

I can't figure out how to do this with Access where I pull in data by finding a string within a table and using some sort of offset procedure to find a string next to the string that was found.

Upvotes: 0

Views: 5799

Answers (1)

Huy Pham
Huy Pham

Reputation: 493

I don't know what is your table structure in Access... So I will assume like this:

The table will be named Table1... with 2 fields Col1 and Col2... and one of the value that you will search (your example "aword") is in Col1 and the corresponding result will be in Col2...

Here is the code:

Public Function GetAccess(strData As String)
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strDB As String
   Dim strSQL As String
   Dim strResult As String

   'Change Table1, Col1, Col2 to your liking correspondingly
   strSQL = "Select Col2 from Table1 where Col1 = """ & strData & """"
   strDB = "C:\\Users\\jn\\Documents\\Trial.accdb"  'Change it to your database name

   Set db = OpenDatabase(strDB)
   Set rst = db.OpenRecordset(strSQL)
   If rst.RecordCount > 0 Then
      strResult = rst.Fields("Col2") 'Remember to change Col2 to your own Column name
   Else
      strResult = ""
   End If
   rst.Close
   db.Close

   Set rst = Nothing
   Set db = Nothing

   GetAccess = strResult
End Function

So if you want to find the result, here is the code to call the above function:

Dim strResult As String

strResult = GetAccess("aword")

Upvotes: 2

Related Questions