Connecting to an External db

Jeff,
You've suggested a macro, would you mind assisting with a format. Below is the info I have so far..

assuming the sSql are:

strSQL1 = "UPDATE tblSecurity SET Password = ........................................
strSQL2 = "UPDATE tblPasswords_old SEY Password= ............................
strSQL3 = Cal some function......

and...the code on my mind...to connect to the external db..

Function ConnectDB()
 Dim strDB As String
 Dim qry1 As DAO.QueryDef

    Const strConPath = "C:\Documents and Settings\Bill\Desktop\"

    strDB = strConPath & "KCombo.mdb"
    Set appaccess = CreateObject("Access.Application")
    appaccess.OpenCurrentDatabase strDB
    appaccess.Visible = False

    Note: ... how do I launch "Shell".... /x macroname"    ' <<<<<<<<------------- Take note
  ' .............................
  ' ...........................
 
End Function
' ************

Regards
Bill

Jeff,
I appreciated your suggestion ...I will prefer an "On Click event handler" procedure

Regards
Bill

After building your appAccess object, you can access the Connection to run your SQL:

appAccess.CurrentProject.Connection.Execute <your first sql here>
appAccess.CurrentProject.Connection.Execute <your second sql here>

As far as your function, you could build a macro in your mde that calls it, then use the appAccess object to Run the macro:

appAccess.DoCmd.RunMacro "your macro name"

Or, depending on what your function does, you could possibly move the code over to your external app ...

LSMConsulting,

Thanks for your suggested idea. I find the Macro method very difficult and trickish due to the fact that two functions has to be run at the same time. I tried it..I coudnt figure it out.

For Example, In my db, I usually call this function to reset a module's password as shown below.

Call ResetPassword("billcute", PerformEncryption("Abc123", True), Now())

(a). The ResetPassword function updates the module's password with encrypted Abc123 as password before saving
      into the module.

(b).  The function for encryption is "PerformEncryption"

.......but with Macro, I find it difficult to configure two functions at the same time
.....also with the strSql,
....the currently loaded frmMM is usually used by the currently logged on user.Admin using the txtUser field of the form to update tblSecurity.Password; and tblPasswords_old.Password and tblPasswords_ChengeDate fields.

I will appreciate help in modifying both the strSql and the Call to ResetPassword / PerformEncryption functions.

' *****Here is what I came up with using your suggestions above: Any corrections will be appreciated..******

 Function ConnectDB()
            Dim strSQL1 As String
            Dim strSQL2 As String
            Dim strDB As String

strSQL1 = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & Me.txtUser & "'"
strSQL2 = "UPDATE tblPasswords_Old (Password, ChangeDate) VALUES ('" & Me.txtUser & "','" & sEncrypted & "','" & Now() & "')"
   
    Set db = DBEngine.Workspaces(0).OpenDatabase(strDB)

    Const strConPath = "C:\Documents and Settings\Bill\Desktop\"
    strDB = strConPath & "KCombo.mdb"
        Set appAccess = CreateObject("Access.Application")
            appAccess.OpenCurrentDatabase strDB
            appAccess.Visible = False
            appAccess.CurrentProject.Connection.Execute sSq1
            appAccess.CurrentProject.Connection.Execute sSq2
            appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())
            db.Close
    Set db = Nothing
    End Function
' *************

Regards
Bill'

Hi Bill,

I am not really aware about the total security concept, but let me place two things here, which are not rlated to your direct question, but to the security situation:

1st: why would you be able to connect freely to your db. anybody from the network could do that too and get at the data in the mdb - i recommend to secure this, but it would have effct on your actual question off corse.

2nd: From another post i have noticed that your function PerformEncryption does just a little bit shifting in the characters, so do not see the plain text in the user-table. Anybody, wo has connected to the db from outside(see prvious point) and have a look at the table will be able to deduct the system behind.

if i  missed important additional facts about you security concept, above points may be just hot air.

regards, Franz

FWIW, I agree with Franz regarding this "security" ...

Regarding the PerformEncyryption function, I'd remove that call from your code and instead put it in the ResetPassword function, so that you can pass in plain text and the ResetPassword code would perform the encryption. This alleviates the need to call 2 items.

Do you mean that your SQL is pulling data from frmMM.txtUser? If so, you'd need to make sure that form is open:

Function ConnectDB()
            Dim strSQL1 As String
            Dim strSQL2 As String
            Dim strDB As String
            Dim frm As Form

'/find frmMM
For Each frm in appAccess.AllForms
  If frm.Name = "frmMM" then exit for
Next

If frm Is Nothing or frm.IsLoaded=False then
  msgbox "Could not find data."
  Exit Function
End If

'not sure where your sEncrypted comes from ...
strSQL1 = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & frm.txtUser & "'"
strSQL2 = "UPDATE tblPasswords_Old (Password, ChangeDate) VALUES ('" & frm.txtUser & "','" & sEncrypted & "','" & Now() & "')"
   
    Const strConPath = "C:\Documents and Settings\Bill\Desktop\"
    strDB = strConPath & "KCombo.mdb"

'you aren't using the db object, so no reason to open it  
'Set db = DBEngine.Workspaces(0).OpenDatabase(strDB)

        Set appAccess = CreateObject("Access.Application")
            appAccess.OpenCurrentDatabase strDB
            appAccess.Visible = False
            appAccess.CurrentProject.Connection.Execute sSq1
            appAccess.CurrentProject.Connection.Execute sSq2
            appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())
            'note: if the above doesn't work, try building
            'db.Close
            'Set db = Nothing
    appAccess.Quit
    Set appAccess = Nothing
End Function

LSMConsulting / Franz,
Thanks for your suggestion. I will likely explore the possibilities with a new thread once this post has closed.

I tested the above code and commented the strSql for now.
(1).
I received a Run-timr error '424": "Object required"

...and the debug highlighted..

For Each frm in appAccess.AllForms

Although, I placed both ResetPassword and performEncryption routines in my test sample. I am strongly of the opinion that the two functions should be inside the target db...in this case KCombo.db - the reason being that the
ResetPassword function is expected to hold the changed password. Once the target db's backdoor password is reset,
it will be easier to reset other users.

Here is the logic I am trying to achieve.

1. Connect to the target db from a remote location
2. When connected find first function ResetPassword and PerformEncyption
3. When the two functions are found, then reset billcute's password to Abc123

This should do it.

Regards
Bill

You'd have to build the appAccess variable first:

Function ConnectDB()
            Dim strSQL1 As String
            Dim strSQL2 As String
            Dim strDB As String
            Dim frm As Form

Const strConPath = "C:\Documents and Settings\Bill\Desktop\"
strDB = strConPath & "KCombo.mdb"

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDB

'/find frmMM
For Each frm in appAccess.AllForms
  If frm.Name = "frmMM" then exit for
Next

If frm Is Nothing or frm.IsLoaded=False then
  msgbox "Could not find data."
  Exit Function
End If

'not sure where your sEncrypted comes from ...
strSQL1 = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & frm.txtUser & "'"
strSQL2 = "UPDATE tblPasswords_Old (Password, ChangeDate) VALUES ('" & frm.txtUser & "','" & sEncrypted & "','" & Now() & "')"
   

'you aren't using the db object, so no reason to open it  
'Set db = DBEngine.Workspaces(0).OpenDatabase(strDB)

             appAccess.Visible = False
            appAccess.CurrentProject.Connection.Execute sSq1
            appAccess.CurrentProject.Connection.Execute sSq2
            appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())
            'note: if the above doesn't work, try building
            'db.Close
            'Set db = Nothing
    appAccess.Quit
    Set appAccess = Nothing
End Function

LSM,
I tested your last suggestion and I received a Run-time error code '7856':

"Microsoft Office cant open the database because it is missing or it's opened exclusively by another user.

I am positive that the db is not opened in any way and that it is not misspelled.

debug highlighted...

appAccess.OpenCurrentDatabase strDB


...besides

(2). Is there any way to have your code do a llokup for the two functions mentioned earlier in my previous post and
      when found utilize the function to saves changes to the stored code in the module ?

ResetPassword..it's a function that uses DB_TEXY to store billcute's password in the module. Moreover,

(3).   how do I pass specific password say Abc123 to replace what is in the target module of "KCombo.mdb"

Regards
Bill

LSMConsultin,
Per your other question on ..'not sure where your sEncrypted comes from ...
' *****
 Dim sPassword As String
 Dim sEncrypted As String
 sEncrypted = PerformEncryption("sPassword", True)

...now for the strSql:

strSQL1 = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & frm.txtUser & "'"
strSQL2 = "UPDATE tblPasswords_Old (Password, ChangeDate) VALUES ('" & frm.txtUser & "','" & sEncrypted & "','" & Now() & "')"

Regards
Bill

<I am positive that the db is not opened in any way and that it is not misspelled.>

If your db isn't open, how do you plan on getting information from a form contained within it? Once it's opened, how does frmMM populate? Does this happen when the database loads? If so, you may very well have some timing issues to deal with ...

Did you check your processes in the Task Manager? Everytime I code automation stuff, invariably I end up with several different instances of msaccess.exe (since the code bombs out and the process isn't closed).

Your strSQL2 is attempting to set 3 values, and only 2 columns are declared. Those values must match - if you're setting 3 values, you need to declare 3 columns which will receive the data.

What exactly does your ResetPassword routine do?

On a side note:

Are you trying to build a utility that will reset user passwords externally? If so, I'm not sure of the reasoning for putting the code for that utility in the db being changed. Typically a "tool" such as what you're trying to build would have all it's code internal ... and would open a connection to your database (via ADO or DAO, not through automation) OR simply link the table, and then manipulate the tables directly. If ALL you're doing is resetting the password, then that's the easiest way to do this. Move the code to your "tool" and then set the values from that tool. You can put your encryption and password reset routines and such in the "tool" and even link to the datatables needed.

LSMConsulting,
I understood your point..so I have desided to narrow down my question only to change the password in the  ResetPassword function of KComboDb.mdb to Abc123 from an external mdb..

so the code you assisted earlier with will be...

Function ConnectDB()
/ Note that opening frmMM is no longer required..in KComboDB.mdb....they are only important for the strSQL..

            Dim strSQL1 As String
            Dim strSQL2 As String
            Dim strDB As String
            Const strConPath = "C:\Documents and Settings\Bill\Desktop\"
            strDB = strConPath & "KCombo.mdb"

             Set appAccess = CreateObject("Access.Application")
             appAccess.OpenCurrentDatabase strDB  
             appAccess.Visible = False
             appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())
            'note: if the above doesn't work, try building
            'db.Close
            'Set db = Nothing
    appAccess.Quit
    Set appAccess = Nothing
End Function
' ********

ResetPassword function utilizes the DB_TEXT to store user.billcute and password Abc123 in it's module.

If you can assist just with the process of changing billcute's password in the ResetPassword from a remote Db, I can handle the rest.

Regards
Bill

Do you still get the error when trying to open the database here:

appAccess.OpenCurrentDatabase strDB

No I have not tested it yet...my major concern is being able to fix the line below...

appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())

once connected to KComboDB.mdb, I want to change billcute's password to Abc123 something like.

Dim strPassword As String
strPassword = Abc123

appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())

I am sure...,my coding is wrng...need the right expression..

Regards
Bill

okay,
I have just tested the code with these two lines added

Dim strPassword As String
strPassword = Abc123

....and I received a Run-time error '7866'

"Microsoft Office cant open the database because it is missing or it's opened exclusively by another user.

debug highlighted...

appAccess.OpenCurrentDatabase strDB
' ******

...when I removed the two added lines above...I received.. a compile error
ByRef..Argument type mismatch

...on strPassword..

Try using this:

appAccess.Run ResetPassword, "billcute",  PerformEncryption(strPassword, True), Now()

Note the difference in the way it's called ... the Run method accepts up to 30 arguments, but they MUST be comma separated ... not sure how it will work with your variables,

You may also need to breakout your PerformEncryption call and place the value in a variable

Function ConnectDB()
/ Note that opening frmMM is no longer required..in KComboDB.mdb....they are only important for the strSQL..

           Dim strEncryptedPassword
           Dim strDB As String
            Const strConPath = "C:\Documents and Settings\Bill\Desktop\"
            strDB = strConPath & "KCombo.mdb"

             Set appAccess = CreateObject("Access.Application")
             appAccess.OpenCurrentDatabase strDB  
             appAccess.Visible = False
             '/first get the encrypted password
             strEncryptedPassword = appAccess.Run "PerformEncyryption", strPassword, "True"
             '/now reset
             appAccess.Run "ResetPassword", "billcute", strEncryptedPassword, "Now()"
             appAccess.Quit
             Set appAccess = Nothing
End Function

You set a string variable like this:

Dim strPassword As String
strPassword = "Abc123"

Also, please post the value of your strDB variable ...

the line below was highlighted in red..

strEncryptedPassword = appAccess.Run "PerformEncyryption", strPassword, "True"

Try enclosing strPassword in quotes:

strEncryptedPassword = appAccess.Run "PerformEncyryption", "strPassword", "True"

May need to do the same in the second call to Run ...

...I also noticed in your last posted code that "user.billcute" has no password defined - i.e Abc123.
I wanted to reset user.billute's password to Abc123

You will, of course, have to change the code I provide to match your table/field names, as well as the propery syntax you need ... if I add "billcute" and you need it to be "user.billcute", just change the name to match your own. i have no idea what your nameing conventions and such are ...

okay I your latest suggestion.....it did not resolve the "red" line error...

I now receive a compile error..."Expected: end statement

on..

"PerformEncyryption",

from:
strEncryptedPassword = appAccess.Run "PerformEncyryption","strPassword", "True"


I also tried  this..

strEncryptedPassword = appAccess.Run PerformEncyryption("strPassword", True)    ...or
strEncryptedPassword = appAccess.Run PerformEncyryption(strPassword, True)

....it did not make any difference...the line is still in red.

"user.billcute",   is what I am using as a figure of speech....the correct syntax remains as is... no changes.

appAccess.DoCmd.RunCode ResetPassword("billcute", PerformEncryption(strPassword, True), Now())

strEncryptedPassword = appAccess.Run ("PerformEncyryption","strPassword", "True")

LSMConsulting ,

I have also tested this very last suggested code...and I am still getting the Run-time error 7866.

Regards

LSMConsulting, Bill has asked me to have a look, so please don't feel Im hijacking this thread.,

Bill

LSMConsulting is on the right path here, I guess its how one can implement it. But one thing to note, I don't know if this works on an MDE or not.

First create a public function. I am using dummy code so as to give you an idea


Public Function PswdReset()

    DoCmd.RunSQL "INSERT INTO Table1 ('Test')"
   
End Function


Next create a macro, lets call it PReset
This has just one line in it. Its of type RunCode and it calls          PswdReset ()

Now in your calling code, all u do is run the Macro


e.g.

    Dim acc As Access.Application
   
    Set acc = New Access.Application
    acc.OpenCurrentDatabase ("C:\EE\Temp\ExcelAuto.mdb")
    acc.DoCmd.RunMacro "PReset"
    acc.CloseCurrentDatabase
    Set acc = Nothing



This is as the other experts have said


Now in the function PswdReset, what I have done is just created a dummy insert statement.
What you need to do is add your password reset code in here. Currently though, using this approach, it is fixed to one password.
But you should try something like this just to get you going.

Can I suggest as a test, you create a dummy table called Table1, and it has just the one text field in it called Txt.
Then try it using that insert. If it works, the u know your on the right track. You can then add in your code that resets the password



Now in this DB of yours that is doing the calling, does it have PerformEncryption in it? The reason why I suggested just creating one function that does all the work is that you don't have to copy any of the other functions used by that remote db.

rockiroads,
Thanks for your input. Here is how I used your suggestion.

I created a module (basResetPwsd) and the plave this call in it...

Public Function PswdReset()
Call SavePassword("billcute", EncryptDecrypt(PerformEncryption("Abc123", True)), Now())
End Function

(2). I created a macro (ResetPwsdMacro)
       selected RunCode from the list and supplied function to run

         PswdReset()

I created a form and placed your suggested code in it as follows:

  Private Sub btnLinkFE_Click()
Dim acc As Object
   Dim strFullFilePath As String
'
    Set acc = CreateObject("Access.Application")
   
    acc.OpenCurrentDatabase "C:\Development\Queensdata_Fe.mdb"   'specify path of remote DB
    Call SavePassword("billcute", EncryptDecrypt(PerformEncryption("Abc123", True)), Now())

    acc.DoCmd.RunMacro "ResetPwsdMacro"    'run macro MyMacro
    acc.DoCmd.runcode
    Set acc = Nothing

End Sub
' ***********

Your suggested code opened the target mdb...but did not reset the password...

I received a Run-time Error '2001':
You cancelled the previous operation.


on..

acc.DoCmd.RunMacro "ResetPwsdMacro"    'run macro MyMacro

Am I doing something wrong?

Regards
Bill
       

Thanks rocki, I've been quite busy lately and (sorry billcute) simply forgot about this thread ... please continue <g> ...

LSMConsulting,
Thanks

Regards
Bill

rockiroads,
Sorry, I actually forgot the line code in error when posting my comment...I had tried all options before providing you a feedback.

My comment above still stand.. I tested again after removing the extra code..I still received..

I received a Run-time Error '2001':
You cancelled the previous operation.

on..
acc.DoCmd.RunMacro "ResetPwsdMacro"    'run macro MyMacro

Regards
Bill

Bill, when u run this code on the remote db


Public Function PswdReset()
    Call SavePassword("billcute", EncryptDecrypt(PerformEncryption("Abc123", True)), Now())
End Function


does it work?

I don't have the code to SavePassword hence my INSERT statement as a test.

also try running your macro  

ResetPwsdMacro

confirm that they both work

rockiroads,
Brilliant idea - it didnt even cross my mind to run that test at first.
(1)
I first doubleclick on the macro in my Front End and tested billcute's password...it was reset to Abc123

(2). I also tried ran function PwsdReset from and On Click Event of the same db...it also reset the password.

I assume the problem is the fact that the Remote DB first opens the db before attempting to reset password...

If it is possible to link to the Front End from the remote location then launch the macro without having to first open the Front end db might resolve the impasse.

Regards
Bill

rockiroads,
Thanks..your last suggested tweak to your code it worked...

Private Sub btnLinkFE_Click()
Dim acc As Object
   Dim strFullFilePath As String
    Set acc = CreateObject("Access.Application")
    acc.OpenCurrentDatabase "C:\Development\Queensdata_Fe.mdb", True  'specify path of remote DB
    acc.DoCmd.RunMacro "ResetPwsdMacro"    'run macro MyMacro
    Set acc = Nothing
'Shell "C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE c:\Development\Queensdata_Fe.mdb /x ResetPwsdMacro"
End Sub

Is it possible to connect the target db without opening the db?

Regards
Bill

I want to thank all experts here for their contribution.

I will also to say that I will be opening  a followup on this same topic..and will appreciate further assistance.

Regards
Bill

Note:
As you would notice in my last post, I also tried "JeffWilley" suggestion and it worked but I prefer rockiroads version for two reasons:
(a). Version setting of Ms. Access is not required
(b). User may be running on the run-time version of an Ms.Access

In this case, I will be splitting the points.

Regards
Bill