Solved

Excel Macros with Secured Jet Database

Posted on 2011-09-30
4
238 Views
Last Modified: 2012-05-12
Hello,

I have recently secured our Access 2003 database using the user-level security wizard on Access. This has resulted in the macros which I use on Excel, to write to the database saying that I no longer have the permissions to write to the databse.

How do I enable the macro to write to the database?
Is there a spcific profile that I will be best setting up for such a process (I.E a sepperate user)

Sample code attached,

Thanks in advance

Josh
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
Comment
Question by:bedsingar
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 36894598
You aren't supplying a user id or password in your connection string.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 36894600
Try sometjhign liek this:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=\\ServerName\FolderName\Jet4DB.mdb;
Jet OLEDB:System Database=\\ServerName\Folder2\System_Secured_4.mdw;
User ID=myID;
Password=mypassword;

Open in new window

0
 
LVL 21
ID: 36894609
0
 

Author Closing Comment

by:bedsingar
ID: 36912014
worked when put in one string, thanks :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now