?
Solved

Excel Macros with Secured Jet Database

Posted on 2011-09-30
4
Medium Priority
?
253 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
[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
  • 2
4 Comments
 
LVL 46

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

741 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