Excel Macros with Secured Jet Database

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

bedsingarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
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
 
aikimarkCommented:
You aren't supplying a user id or password in your connection string.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
0
 
bedsingarAuthor Commented:
worked when put in one string, thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.