VBA Application + Passsword

[pcb105]

Hello all,

I am trying to automate a process that we do over and over and run it at 2AM when all of the users are out of the system.

I have written a Accpac VBscript to do it. It runs fine when I run it, but it asks for my password.

I would like to run this script automatically at 2AM. I know how to schedule with VBA etc, but I can't find away around the password issue. Idealy, I would like the application code to be embedded into excel, but I can't seem to get that to work.

I have searched and have tried a lot of different things.

1) thread631-1414817 (I tried to embed this into Excel and it comes up with a dialog box asking for password), but I can't open the database. It says the session is not initialized.
2) I tried to use
Set Session = AccpacCOMAPI.AccpacSession
Session.Init "", "AS", "AS1000", "54A"
Session.Open "ADMIN", "ADMIN", "SAMINC", Date, 0, ""
But it gives me errors saying to check my user name and password. I have checked it several hundred times and I have has 0 luck.

Can someone give me code that enables me to open accpac with the password embedded into the code.

Like I said using the Accpac VBA application the app runs fine, it just needs to have a password manually added and I would like to run all of the code in Excel VBA.

It should be noted that I do not have the SDK. I am using the ACCPAC COM API that I reference to in VBA.

 

[Jay Converse]

Session.Open "ADMIN", "ADMIN", "SAMINC", Date, 0, ""

aka

Session.Open YourLogin, YourPassword, YourComp, Date, 0, ""

and make YourPassword in UpperCase.

 

[pcb105]

tuba2007,

Thanks so much for your reply. I tried capitalizing my password and it now gets by that line. I don't understand why Accpac is case insensitive for password, but I am happy that that is the solution. For completeness, here is my code that puts a message box with the company name.

This code works in Excel 2007 VBA and I assume will work elsewhere very easily. Please note that I have ACCPAC COM API Object 1.0 loaded (Tools ->References)

Sub StartAccpac()
Dim mSession As AccpacCOMAPI.AccpacSession
Set mSession = AccpacCOMAPI.AccpacSession

mSession.Init "", "AS", "AS1000", "54A"
mSession.Open "USER", "PASS", "DATABASE", Date, 0, ""

If mSession.IsOpened Then
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
End If

Dim Company As AccpacCompany
Set Company = mDBLinkCmpRW.GetCompany
mCompanyName = IIf(Trim(Company.Name) <> "", Trim(Company.Name), Trim(Company.OrgID))
MsgBox mCompanyName


mSession.Close



End Sub

 

[pcb105]

I just finished porting my Accpac VBA script to an Excel VBA script and it appears to work properly. Does anyone have ideas on how to protect the password in VBA. I know I can add a password to not allow a user to open the VBA code attached to the Excel sheet without a password, but I would like to add some encyption. Is there a VBA encryption module?

Thanks for the help.

 

[Jay Converse]

No.

 

[DjangMan]

The best you can do is to look up some encrypt/decrypt functions for vb or vba and use those. The issue is that if someone is digging around then they'll be able to bypass your routine to trap the password anyway.