export to secured Access database using ADO

[Wouter]

Hello,

I'm using this code to export entries on an Outlook Form to an secured
Access Database by pressing a button, but it won't work.

***************************************************************
Sub Button_Click()

Set Conn = Application.CreateObject("ADODB.Connection")
Set rst = Application.CreateObject("ADODB.Recordset")

With Conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeReadWrite
.Properties("Jet OLEDB:System Database") = "I:\DATABASE.MDW"
.Open "Data Source=I:\DATABASE.mdb;User ID=user;Password=password;"
End With

Set rst = Conn.Execute("SELECT * FROM tbl_Mail")

With rst
.AddNew
.Subject = Item.Subject
etc
.Update
.Close
End With

Set rst = Nothing
Set Conn = Nothing

End Sub
***************************************************************

The error I recieve is as follows: "Microsoft Outlook - Object or
Provider is not capable of performing requested operation"

The probleem seems to be the "AddNew" command, because I use a simular
piece of code to fill a bunch of comboboxes on the same Outlook form
with data from the same Access database. This piece of code does not
use "AddNew" and works fine.

I came across a Microsoft KB article that stated that ADO has only
read permissions and DAO has read/write permissions, but I couldn't
get the database connection to the secured database working with DAO,
so instead I used ADO.

Any suggestions?
TIA, Wouter

 

[Ken Slovak - [MVP - Outlook]]

This is really an Access question, but how you open your recordset controls
whether or not you can update the records in the recordset or add new
records to it. You might have to use an INSERT SQL command to add a new
record to the database.

ADO can certainly write to a database, I do that all the time. The recordset
can be opened with different cursor types and lock conditions, which
determine whether you can write to the database. A dynamic cursor might be a
good choice if your database supports that type of cursor.

I'd recommend posting in an Access newsgroup for more information.

 

[Guest]

Thanks,

Someone in the Access Newsgroup came up with a solution to
use DAO to connect to the secured database using this code:

Dim wrk As DAO.Workspace
DBEngine.SystemDB = "I:\DATABASE.MDW"
Set wrk = DBEngine.CreateWorkspace("", "User", "Password",
dbUseJet)
Set db = wrk.OpenDatabase("I:\DATABASE.mdb")

But this would be VBA, not VBScript, right? I've tried
several ways to rewrite it to VBScript, but being a newbie
at this, I couldn't get it to work. I think it should look
something like this:

********************************************************
Set appAccess = Item.Application.CreateObject
("Access.Application")
appAccess.DBEngine.SystemDB = "I:\DATABASE.MDW"

Set DAO = Application.CreateObject("DAO.DBEngine.36")
Set wks = DAO.Workspaces(0)

Set wks = DAO.CreateWorkspace("", "user", "password",
dbUseJet)
Set dbs = wks.OpenDatabase("I:\DATABASE.mdb")
Set rst = dbs.OpenRecordset("tbl_Mail")

With rst
etc, etc

********************************************************

I sure hope there's someone who can help me get this right.

Wouter

 

[Ken Slovak - [MVP - Outlook]]

Put a comment indicator before any As clauses, all variables in VBScript are
Variants and you can't declare item types.

Then since it looks like the VBA code was written to run from within Access
you need to declare and instantiate an Access.Application object:
Set appAccess = CreateObject("Access.Application")

That should do the trick.