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

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
jb702Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
jb702Author Commented:
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
 
jb702Author Commented:
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
 
jb702Author Commented:
Thank you
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.