?
Solved

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

Posted on 2013-11-09
5
Medium Priority
?
2,678 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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