Protection of transferred data

[ishtiaque]

I have a table in my access database named "Customer" and I send its records
as excel (csv format) file for some purposes. The below commands are OK for
it. But I need to make the .csv (or .xls) file protected when it is created
using VBA code in Access so that nobody can change the data in the sheet.

outFNamecustomer = "C:\Excel data\Customer.csv"
DoCmd.TransferText acExportDelim, , "Customer", outFNamecustomer, True

Do you have any solution for this?

 

[Arvin Meyer [MVP]]

Add the Protect property and a password, here's some sample code from the
Access Web, with an added sheet protection. Set a Reference to Excel, in the
Northwind sample database (open from the Help menu)

Sub sCopyFromRS()
'Send records to the first
'sheet in a new workbook
'
Dim rs As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("Customers", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs

.Protect "PasswordRightHere"

End With
End With
End If

 

[ishtiaque]

Thanks a lot. It is working. I would be grateful to you if you please make
the following line for a query that will take a date value (yyyy/mm/dd) from
a form where the command button exists.

Set rs = CurrentDb.OpenRecordset("Customers", dbOpenSnapshot)

I am very new in Access so i'm taking this opportunity. Also I want to save
the sheet as .csv format keeping ".Visible = False" so that the users do not
see the opened sheet.

regards,

Ishtiaque
From Bangladesh

 

[Arvin Meyer [MVP]]

That happens to use the customers table, you can build a query with a date
parameter, but you will need to use the date format #mm/dd/yy# or
#mm/dd/yyyy#
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[ishtiaque]

Dear Arvin :
Thank you for your answer. But

the command line "Set rs = CurrentDb.OpenRecordset("qCustomers",
dbOpenSnapshot)"

is not working for a query "qCustomers" (is OK for a table). In this query I
set a parameter "[Forms]![wzDataExport]![bdate]" in the criteria box. Here
'wzDataExport' is the form and 'bdate' is the text from where the query
should get the parameter value. But the process is not working as some errors
are seen in "Set rs=...." line when I use the "qCustomers" query insted of
"Customers" table.

Again you have suggested to use the format '#mm/dd/yyyy#' for date. Where
should I put it? In the table behind the query or in the query field
property? Should I write anything in the query parameter window?

This will be a great help for me if you please answer on it.

 

[Arvin Meyer [MVP]]

Parameters must be listed for queries like:
--
Dim db as DAO.Database
Dim qdf as DAO.Querydef
Dim rst as DAO.Recordset

Set db = CurrentDb
Set qdf = db.querydefs("qCustomers")

qdf.parameters(0) = [Forms]![wzDataExport]![bdate]

Set rst = qdf.OpenRecordset()
--
Use the date format in the bdate field like: 08/20/2008
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Dear Arvin :
Thank you for your answer. But

the command line "Set rs = CurrentDb.OpenRecordset("qCustomers",
dbOpenSnapshot)"

is not working for a query "qCustomers" (is OK for a table). In this query
I
set a parameter "[Forms]![wzDataExport]![bdate]" in the criteria box. Here
'wzDataExport' is the form and 'bdate' is the text from where the query
should get the parameter value. But the process is not working as some
errors
are seen in "Set rs=...." line when I use the "qCustomers" query insted of
"Customers" table.

Again you have suggested to use the format '#mm/dd/yyyy#' for date. Where
should I put it? In the table behind the query or in the query field
property? Should I write anything in the query parameter window?

This will be a great help for me if you please answer on it.

--
Z. ishtiaque

That happens to use the customers table, you can build a query with a
date
parameter, but you will need to use the date format #mm/dd/yy# or
#mm/dd/yyyy#
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com