Solved

Gather Information from Access.accdb Using Outlook VBA then Insert new Record

Posted on 2013-11-09
5
2,086 Views
Last Modified: 2013-11-09
I have been learning the outlook object model and would like to do the following.

From outlook, have vba code search a table in an access db, locate certain information from various tables, using information from the current email that is selected.

What i can't figure out is how to connect to the database, which most likely will be open but sometimes would need to open it.

Can someone get me started on how to make this connection.

Thanks
0
Comment
Question by:jb702
  • 3
  • 2
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
You can connect to an Access database using DAO or ADO (and the db doesn't have to be open in the Access interface, by the way). To use DAO, first be sure to set a reference to the appropriate DAO library, and then do this:

Dim dbs As DAO.Database
Set dbs = DAO.OpenDatabase("Full path to the database")

Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset("SELECT * FROM SomeTable WHERE SomeField=MyValue")

If Not (rst.EOF and rst.BOF) Then
  '/ found data use the rst("Field") syntax to read data from the recordset's fields:
  Me.YourEmail.Subject = "Data for " & rst("Field1")
End If

ADO is a bit different:

Dim con As New ADODB.Connection
con.Open "Your connection string"

Dim rst as New ADODB.Recordset
rst.Open "SELECT * FROM SomeTable", con

If Not (rst.EOF and rst.BOF) Then
  '/ data was found
End If

"Your Connection String" would be a valid, fully formed connection string like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Persist Security Info=False;

Here's a very good source for different types of connection strings:
http://www.connectionstrings.com/access/
0
 

Author Comment

by:jb702
Comment Utility
i have tried your code but i get an error. see code snippet for error location LIne 26

Any ideas on what i'm missing.

Private Sub TestingConnection()
Dim strSQL As String
Dim strConnect As String
Dim dblCID As Double
Dim strDomain As String
Dim strPath As String, strDB As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
    
strDB = "247Recon_Access.accdb"
strPath = "C:\Users\Jbryan\Documents\3 - Doug\247 Service DB\"

strConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
              "Dbq=" & strDB & ";" & _
                  "DefaultDir=" & strPath & ";" & _
                  "Uid=Admin;Pwd=;"

strDomain = "@invitationhomes.com"
        
strSQL = "Select CustomerID From Customer WHERE DomainName = " & strDomain
    

con.Open strConnect


Rs.Open strSQL, con     '''''  I get error here saying "Object Required"

If Not (Rs.EOF And Rs.BOF) Then
  dblCID = Rs.Fields("CustomerID")
End If

Rs.Close
Set con = Nothing


    
End Sub

Open in new window

0
 

Author Comment

by:jb702
Comment Utility
I got this  to work though...thank you

Public Sub TestingDAO()
Dim dbs As DAO.Database
Dim strDomain As String, dblCID As Double
Dim strPath As String, strDB As String
Dim strSQL As String

strDomain = "'@invitationhomes.com'"
strDB = "247Recon_Access.accdb"
strPath = "C:\Users\Jbryan\Documents\3 - Doug\247 Service DB\"
strSQL = "Select CustomerID From Customer WHERE DomainName = " & strDomain

Set dbs = DAO.OpenDatabase(strPath & strDB)

Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset(strSQL)

If Not (rst.EOF And rst.BOF) Then
  
  dblCID = rst.Fields("CustomerID")
End If
Set rst = Nothing
End Sub

Open in new window

0
 

Author Closing Comment

by:jb702
Comment Utility
Thank you
0
 
LVL 84
Comment Utility
You declared your Recordset variable as "rst":

Dim rst As New ADODB.Recordset

But you then tried to use a variable named "Rs":

Rs.Open strSQL, con     '''''  I get error here saying "Object Required"

If you change "Rs" to "rst", the ADO code would work also.

But generally speaking, it's better to use DAO when working with Access.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Set OWA language and time zone in Exchange for individuals, all users or per database.
Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now