Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I protect spreadsheet after using VBA to get my Access into Excel

Status
Not open for further replies.

SherWat

Technical User
Feb 12, 2016
216
CA
I am able to use VBA to get data from an access database into an excel spreadsheet but now I would like to protect the data in excel from being changed. Changes to the data should only be made in access and not in excel. How would this be accomplished?

Using VBA 7.1 - Access 2013 - Excel 2013

Below is my code that works:
What can I insert to prohibit the ability of data being changed in the excel spreadsheet?


Sub test()
'
' test Macro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\swatier\Documents\Staff1.accdb;Mode=Share Deny " _
, _
"Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
, _
"gine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB" _
, _
":New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on" _
, _
" Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB" _
, _
":Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Cell Assignments")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Users\*******\Documents\Staff1.accdb"
.ListObject.DisplayName = "Table_Staff1.accdb"
.Refresh BackgroundQuery:=False
End With
End Sub​

Thank you kindly.
 
Add password to the worksheet after adding and configuring query table:
Code:
    .Refresh BackgroundQuery:=False
    .Parent.Protect Password:="YourPassword"
End With
End Sub

combo
 
Please format your code with TGML tags, it is a lot easier to read.

1. Open Excel with some data
2. Click on View - Macros - Record macro
3. Follow steps from this place: How to Lock Cells and Protect Worksheets in Excel
4. Stop recording your Macro
5. Press Alt-F11 to see the code Excel created for you

Any questions about the code - ask here :)


---- Andy

There is a great need for a sarcasm font.
 
I have used Data > Get External Date > From Access... many times and from many differenr databases over the past 20 years or so.

Usually its more than a one time effort. If that’s the case then doing what your code represents need only be done ONE TIME. No VBA required! You just need to Refresh your query.

WARNING: Running this code multiple times on the same sheet will add multiple ListObject/QueryTable objects to your sheet, and can cause unexpected problems.

So I never code an ADD ListObject/QueryTable object. Rather I might use code to change the Connection or the CommandText and Refresh.


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thank you all for responding.
Sorry about the formatting of the code - this is my first foray into VBA and posting to this forum. Will certainly use the TGML tags for future postings.
Maybe I don't need to use VBA code?
The excel spreadsheet needs to be updated once a month.
The Access database will be updated through out the month.
So maybe I just need to get the external data --> from access whenever I need the current information.
Is it possible to create these steps on a command button?
Thanks.
 
It depends how much automation you need. Having fixed table/query in access, you create manually connection to access once. Next time you only refresh data. It is possible to configure connection so that data is refreshed every time you open the file. With VBA, you can code Workbook_Open event with additional tests for refreshing data. Moreover, with VBA you can protect workbook with UserInterfaceOnly parameter set to True, so only the code can interact with worksheet (needs to be applied every time you open workbook).

combo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top