Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-09
5
Medium Priority
?
2,819 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39635408
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
ID: 39635552
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
ID: 39635557
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
ID: 39635558
Thank you
0
 
LVL 85
ID: 39635580
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

609 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