Exporting data with vba from Excel to an Access 2007 db with password

  • Thread starter Thread starter Jan T
  • Start date Start date
J

Jan T

I will use Excel 2007 and Access 2007 and want to log events in an
*.accdb file. I also want to password protect the database. However,
when trying to add a new record to the db with password, I get an
error? If I remove the password from my db and my vba code in Excel,
it works perfectly. What do I do wrong?
Note: To protect my db I use the button on the ribbon Tab, Database
Tools/Decrypt db with password.

Here is the code I use with and without password (Commented out one
alternativ):

Sub ExcelToAccess(strSub As String)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Users\Jan\Documents\MyDB.accdb;" & _
"Jet OLEDB:Database Password=UniquePsw;"

' cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
' "Data Source=C:\Users\Jan\Documents\MyDB.accdb; " & _
' "Persist Security Info=False;"


' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblLog", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("WinLogin") = UserNameWindows
.Fields("Prosedure") = strSub
.Fields("AddinVersion") = ThisWorkbook.Name
.Fields("dtmStamp") = Now
.Update ' stores the new record
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Does anybody understand why I get an error if I Decrypt the db with a
password? Any other way to password protect the database?

Thank you for any suggestion.

Regards Jan T.
 
The first thing I see here is that you are using JET to provide a
password to ACE. Not sure if this IS the cause for the error, but I
tend to keep all things JET or all things ACE respective to Excel
version.
 
The first thing I see here is that you are using JET to provide a
password to ACE. Not sure if this IS the cause for the error, but I
tend to keep all things JET or all things ACE respective to Excel
version.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thank you, for your comment, Garry.
Yes, I found that code snippet on the internet somewhere, and
I also was wondering about that. I want to have it the right way,
of course, and I am sure someone out there, have an example.
Regards Jan T.
 
Jan T has brought this to us :
Thank you, for your comment, Garry.
Yes, I found that code snippet on the internet somewhere, and
I also was wondering about that. I want to have it the right way,
of course, and I am sure someone out there, have an example.
Regards Jan T.
Click to expand...

You could have found an answer already posted here if you browsed the
subject lines for DB related topics.

I use a version-aware function to set up my connection string according
to which provider is appropriate for the running instance of Excel.
Otherwise, everything else is the same as far as building
queries/recordsets goes.

Example:
Construct a connection string something like this:
If Application.Version => 12 Then
'use ACE provider connection string
Else
'use JET provider connection string
End If

This precludes that you'll have to construct 2 separate connection
strings. You could use constants for this:

Const sProvider As String = "Microsoft.Jet.OLEDB.4.0;"
Const sExtProps As String = "Excel 8.0;"

Const sProvider12 As String = "Microsoft.ACE.OLEDB.12.0;"
Const sExtProps12 As String = "Excel 12.0 Xml;"

If you know the data source beforehand, you could configure your code
something like this:

<aircode>
' Use a var to hold data source
sDataSource = "<FullPathAndFilename>"
If Application.Version => 12 Then
'use ACE provider connection string
sConnect = "Provider=" & sProvider12 & _
"Data Source=" & sDataSource & _
"User ID=" & sUsername & ";" & _
"Password=" & sPassword & ";"
Else
'use JET provider connection string
sConnect = "Provider=" & sProvider & _
"Data Source=" & sDataSource & _
"User ID=" & sUsername & ";" & _
"Password=" & sPassword & ";"
End If
</aircode>

' Construct your SQL statement
sSQL = "SELECT * FROM..."

' Grab the data into a recordset
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText
 
Note:
sDataSource should terminate with a semicolon.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

- - - - - -
Thanks again, Garry. However, I don't see where sUsername
fit in? May be I don't know where in Access to set it?
As I described earlier, I am setting the password in Access
manually.
Note: To protect my db I use the button on the ribbon Tab, Database
Tools/Crypt db with password. (translated from Norwegian).

Or do I simply set Username and password with vba too?

Btw, I made the db in Access 2007 and saved it to a shared folder.
It is going to be a log for different user in our company.

Thank you for your help!

Regards
Jan T.
 
Jan T explained :
- - - - - -
Thanks again, Garry. However, I don't see where sUsername
fit in? May be I don't know where in Access to set it?
As I described earlier, I am setting the password in Access
manually.
Note: To protect my db I use the button on the ribbon Tab, Database
Tools/Crypt db with password. (translated from Norwegian).

Or do I simply set Username and password with vba too?

Btw, I made the db in Access 2007 and saved it to a shared folder.
It is going to be a log for different user in our company.

Thank you for your help!

Regards
Jan T.
Click to expand...

You 'pass' your username/password to the db provider in the connection
string where/as shown. This is not where you 'SET' the
username/password but rather how you get permission to access the db
file via VBA. This is a substitute for the dialog in MS Access that
prompts you for your username/password when opening the file in Access.
Only difference is you're opening the file via an external program
(Excel).

HTH
 
Jan T explained :











You 'pass' your username/password to the db provider in the connection
string where/as shown. This is not where you 'SET' the
username/password but rather how you get permission to access the db
file via VBA. This is a substitute for the dialog in MS Access that
prompts you for your username/password when opening the file in Access.
Only difference is you're opening the file via an external program
(Excel).

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc– Skjul sitert tekst –

– Vis sitert tekst –
Click to expand...

As far as I know, the new Access ACCDB format does not support
Username,
but only Password. You cannot have user level security, only group
security.
To have a User name AND password, you need to use a standard mdb and/
or
mdw, using a workgroup system database.

Tried your code but with a made up Username, that it would not accept.
I
still haven't found a good, working code example, unfortunely.

Have you tried to run your code to open an password protected ACCDB
file
and not a MDB file?

Thank you again, Garry!

Regards
Jan T.
 
As far as I know, the new Access ACCDB format does not support
Username,
but only Password. You cannot have user level security, only group
security.
To have a User name AND password, you need to use a standard mdb and/
or
mdw, using a workgroup system database.

Tried your code but with a made up Username, that it would not accept.
I
still haven't found a good, working code example, unfortunely.

Have you tried to run your code to open an password protected ACCDB
file
and not a MDB file?

Thank you again, Garry!

Regards
Jan T.
Click to expand...

Jan,
I've never used ADODB with db files, only Excel workbooks and plain
text files. That means I would not know what requirements are needed
for accdb files. If, as you say, you don't need a username then delete
that part of the code.

what I was able to find via google is:

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=S:\MyFolder\myDB.accdb;Persist Security Info=False;Jet
OLEDB:Database Password=123"

...which appears to be similar to what you posted, though not 'exactly
as posted'. This suggests that the password methodology is still JET
tech, even though you're using the ACE provider. That also explains why
some legacy Excel addins of mine that use JET only still work in Excel
12 and later. -Obviously, JET is still supported but I don't know that
it works with accdb files. It could be, though, that JET persists to
work only because I use workbooks and/or text files!<?>
 
Back
Top