Vba - pull data from Access DB

[Siyanna]

Hi All -

I have some code that usees ADO to pull back data from Access to Excel spreadsheet

I have 2 questions and hoping you can help me with this

1) How can I password protect this access database
2) how can i pull back data from this password protected database

Many thanks

 

[xenou]

You are using an Ace provider:

[COLOR="#FF0000"]Provider=Microsoft.ACE.OLEDB.12.0[/COLOR];Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword;

Have you tried the suggestion you got from the website?

 

[Siyanna]

I was concerned that it said that for access 2016, there could be issues so wanted to ask u guys if there was an alternative connection string

thank you

 

[xenou]

Sorry, I don't know of any other connection strings. That doesn't mean they don't exist. I just don't use passwords with Access.

 

[Siyanna]

Really appreciate all your guys help

if was the 1st i heard of Exclusive and heard other things like front end and back end
alot more learning to do

 

[Siyanna]

Hi Xenou/Norie

you Guys have been so helpful and I can’t thank you enough

I have 1 major problem - the access database only updates if I store in a location if someone had access to the folder

So currently only users who have access to a folder where the Accesd database is stored seems to update but anyone who doesn’t have access to the folder, their info does not get stored

i was thinking of uploading this access file on sharepoint because that way everyone will have access in the background. how can I amend this code below so that it updates the data akin the access database that is stored on sharepoint?

when I step through my VBA code - when it opens the connection string - it creates another file (type of file: Microsoft Access Record-Locking Information (.laccdb)) and once the this code is finished - it closes that file

here is my full code to update this log database which is not protected - my aim is to protect it so no one else can phycally manually open it without knowing the password - thanks

Sub ExportDataToAccess()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strQuery As String
    Dim LogDate As Date
    Dim LogTime As Date
    Dim myDB As String
    
    Application.ScreenUpdating = False
    'Initialize Variables
    LogDate = Date
    LogTime = Format(Now(), "hh:mm:ss")
    UserId = Environ("username")
    
    'myDB = "replace with the fully qualified path to your Access Db"
    myDB = "C:\Users\my.DESKTOP-8BKSK49\Desktop\Access Log Files\LogDatabase.accdb"
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
        .ConnectionString = myDB
        .Open
    End With
    
    On Error GoTo CloseConnection
    
    With rs
        .ActiveConnection = cn
        .Source = "LogTable"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
            
        On Error GoTo CloseRecordset
        .AddNew
        .Fields("Log_Date").Value = LogDate
        .Fields("Log_Time").Value = LogTime
        .Fields("UserID").Value = UserId
        .Update
    End With
    
CloseRecordset:
    rs.Close
    
CloseConnection:
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

 

[xenou]

I don't think you can put an Access database in Sharepoint so people can use it there.