CurrentProject.Connection and Password

[mesavage]

My problem is this:
I have an Access Project connected to an SQL Server. The user is required to login when the project is opened. Since the "allow saving password" is unchecked in the connection window (because I want users to have to login), then the CurrentProject.Connection does not have the password in it.

Later, I need to start and commit/rollback a transaction. I was simply creating a new connection, using the CurrentProject.Connection property. However, since the password is not in the connection string, it isn't working. (It all works fine when the "allow saving password" is checked.)

Is there another way to do this? So far, it looks like the user might have to type the password in twice (which seems a bit silly to me). I'm hoping that won't have to be the case!

Thanks,
Miriam

 

[FancyPrairie]

Could you create a dummy user and password on the SQL server side with the necessary privileges and connect using that user name and password?

 

[mesavage]

That is a very good idea. I have four different sets of permissions (with two more coming later). It could get a little messy figuring out who has what permission, but it is better than nothing! )

 

[mesavage]

OK, I later found the solution. The CurrentProject.Connection.ConnectionString property gives the password. So when I create the new connection for the transaction, I just need to add this ".ConnectionString" to the end and it works!

Miriam

 

[mesavage]

OK I fibbed in the previous post. I think that I accidentally did have the "allow save password" on or something.

Anyhow, I went ahead and implemented the idea to have the dummy username and password. However, my recordset is not updateable when I do this. Here is my code to start my transcation:

Set subcnn = New ADODB.Connection
subcnn.Open buildConnString(username, pwd)
Set subrst = New ADODB.Recordset
rststr = "EXEC dbo.DCT_ViewDFW '" + combo_prodfam.value + "'"
subrst.Open rststr, subcnn, adOpenDynamic, adLockOptimistic
subcnn.BeginTrans

Me.ViewDCT_Subform.Form.RecordSource = ""
Me.ViewDCT_Subform.Form.InputParameters = ""
Set Me.ViewDCT_Subform.Form.Recordset = subrst
Me.ViewDCT_Subform.Form.UniqueTable = "dbo.DCT_Firmwares"

When I try to modify the recordset in the subform, I get a message saying that the recordset is not updateable. Any ideas why this is?

Miriam

 

[FancyPrairie]

Not sure what you are trying to do. But the first thing I would do is to write a simple function that opens a recordset using the connection string containing the dummy name and password. This function would than simply change a value in a record and then update the record. If it works, then you know that the dummy user's permissions are set right, else they are not. For example,

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    cnn.Open "Your Connection String"

    Set rst = New ADODB.Recordset
    rst.Open "Select * from Your Table;", cnn, adOpenKeyset, adLockOptimistic

    rst.MoveFirst
    rst.fields(0).value = "A new Value"
    rst.Update