Connection string to Access db

[davesmith202]

I am writing some code in an Outlook program and need to connect to a MS Access 2003 database table.

What code do I need to do this? Do I need to do an ODBC connection or something? What is easiest?

Thanks,

Dave

 

[Uncle Gizmo]

have a look at Helens site:

Helen Feddema's Website --- Writer Helen provides some excellent MS Access examples and examples for the other "Office Programmes"

You may find something useful there.

 

[modest]

I don't know much about VBA from within Outlook, but this still sounds like an ActiveX thing, or possibly a Data Access thing.

Since I'm leaving work (actually half an hour overdue) I'll post how to do it with DAO first. Make sure to include a reference to Micrsoft's DAO library.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim dbName = "C:\blah blah\DB.mdb"

Set db = DBEngine(0).OpenDatabase(dbName, False, False, DbDriverNoPrompt)
Set rs = db.OpenRecordset ("SELECT * FROM [TABLENAME];")

I just wrote this on the fly, so I'm not sure if it's completely accurate. Plus, as I've said I haven't yet fiddled with VBA inside of outlook.

 

[RoyVidar]

Here's a little ADO thingie

dim rs as adodb.recordset
dim cn as adodb.connection
set cn=new adodb.connection
cn.connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\somepath\myDb.mdb;" & _ 
           "User Id=admin;" & _
           "Password="
cn.open
set rs=new adodb.recordset
with rs
  .activeconnection=cn
  .locktype=adlockoptimistic
  .cursortype=adopenkeyset
  .open "tblYourTable",,,,adcmdtable
  if not .bof and not eof then
    ' recordset is open...

connectionstring is from
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet

The above recordset should be updateable. Would need a reference to Microsoft ActiveX Data Objects 2.# Library

 

[RV]

actually half an hour overdue

Shame on you

RV

 

[davesmith202]

I tried Roy's code and it worked best. Thanks for all your help! I've learned quite a lot about how Outlook VBA works now and I was always so confused before!