VBA Code for Access password

[Bill]

I am trying to pass some data from a PowerPoint presentation to an Access
database and all works great. However I would like to password protect the
database with a regular "User password", but can't seem to figure out how to
code it in the automation macro I use to access the database.

All versions are XP. Code is as follows (at least the part I need for
this):

===========================

Sub SaveData()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim strDataSource As String
Dim strProvider As String
Dim strRecordSource As String
'this line is new
Dim strPassword As String

'this line is also new
Set strPassword = "testing"

' Setup data source, provider, and table
strDataSource = "Data Source=" & Application.Presentations(1).Path &
"\TrainingData.mdb"
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strRecordSource = "TrainingRecord"

'Open connection
cnn.Open strProvider & strDataSource & strPassword

'Open recordset
rst.Open strRecordSource, cnn, adOpenDynamic, adLockOptimistic

'add record and fill fields from data array
rst.AddNew
rst!firstname = strfirstname
rst!lastname = strlastname
rst!Feedback1 = strFeedback
rst!DateTaken = Now
rst.Update
rst.Close

'other junk here relating to PowerPoint

End Sub

=======================

Everything worked fine until I tried to set a password and access the
database via the coded password. It just hangs in PowerPoint and never
makes it to my msgBox that says all went fine.

Any ideas? TIA!

Bill

 

[Bill James]

Connection string should be in this form:

strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Uid=USERNAME;" & _
"Pwd=PASSWORD;"

-----Original Message-----
I am trying to pass some data from a PowerPoint presentation to an Access
database and all works great. However I would like to password protect the
database with a regular "User password", but can't seem to figure out how to
code it in the automation macro I use to access the database.

All versions are XP. Code is as follows (at least the part I need for
this):

===========================

Sub SaveData()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim strDataSource As String
Dim strProvider As String
Dim strRecordSource As String
'this line is new
Dim strPassword As String

'this line is also new
Set strPassword = "testing"

' Setup data source, provider, and table
strDataSource = "Data Source=" &

Application.Presentations(1).Path &

 

[Bill]

Sorry for the delay and continued ignorance but I tried your suggestion and
seem to be missing something because it still doesn't work. Here is my new
code:

=====Start of code=====

Sub SaveData()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim strDataSource As String
Dim strProvider As String
Dim strRecordSource As String

' Setup data source, provider, and table
strDataSource = "Data Source=" & Application.Presentations(1).Path
&"\TrainingData.mdb"
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Pwd=testing;"
strRecordSource = "TrainingRecord"

'Open connection
cnn.Open strProvider & strDataSource & strPassword

'Open recordset
rst.Open strRecordSource, cnn, adOpenDynamic, adLockOptimistic

'add record and fill fields from data array
rst.AddNew
rst!firstname = strfirstname
rst!lastname = strlastname
rst!Feedback1 = strFeedback
rst!DateTaken = Now
rst.Update
rst.Close

End Sub

=====End of Code=====

Thanks in advance!

Bill