Solved

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

Posted on 2013-11-09
5
2,551 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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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