Running Stored Procedures in GP VBA

[mach5]

I'm trying to run a Stored Procedure from the GP VBA. It will be used for data entry validation so it will be run a lot (hundreds of times per day).

Using ADO, I can create a connection and get a recordset back, but as far as I can tell this requires that I either (a) hardcode a userid and password for the connection or (b) get the user to enter a login and password every time it runs. The first option is a fairly big security risk and the second is just a real drag for the person that would have to type in the password hundreds of times a day.

My questions:
Is there some way I can run a stored procedure using the existing connection that GP has with the SQL server, or some way of using the login/password data the user enters when they logon to Great Plains?
If not, Is there any other way I can run the stored procedure without the login/password (perhaps using the NT user's existing network connection or a specific ODBC connection)?

Thanks,
Mark Handford

 

[MentatDragon]

What you need is a DLL from Great Plains called RetrieveGlobals.dll

If you go into Partnersource or CustomerSource you can download the file, and the instructions to install it. I think it has to be on every user's PC.

Shown below is a VBA sample on how to call the DLL.

'Before this code can be used, you must go to Tools>References and mark
'the RetrieveGlobals reference. This sample is better for performance.

Dim userinfo As New RetrieveGlobals.retrieveuserinfo
Dim luserid As String
Dim lsqlpassword As String
Dim lintercompanyid As String
Dim lsqldatasourcename As String

luserid = userinfo.retrieve_user()
lsqlpassword = userinfo.sql_password()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()
'MsgBox (luserid + " " + lsqlpassword + " " + lintercompanyid + " " + lsqldatasourcename)
Set userinfo = Nothing

 

[celeron895]

Think twice about using that dll. Unless they changed it, the dll can be used to bring back the SA password in plain text. Of course, who would expect security from Micro$oft.

 

[winthropdc]

The current version does not return the 'sa' password anymore..... which is a pain in the butt as it means the VBA does not work for 'sa'.

However, as the 'sa' password is normally not encrypted by Great Plains, it is a security risk to bring it back.

David Musgrave
Senior Development Consultant
Asia Pacific Professional Services

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

mailto:dmusgrav@nospam-microsoft.com

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.