From VBA how can we determine which SQL server a user is accessing?

(0) Share

Report

Posted on by Larry Turner

1,325

On our client machines we do not have separete file folders and client installations for our production and test environments. We have custom Windows / VBA we need to make sure is pointing to production data when the user signed on to GP and selected the production server name from the server list. Likewise when they select the test server from the server list we want to make sure they are pointing to test data on the test server. We would like for the VBA to be able to use some environment variable or table field to determine which server / environment the user selected from the server list.

We have separate ODBC system DSN data sources defined for the production and test SQL servers on the client machines. I looked at SY02100 but it does not contain the SQL server names, just server ID’s . Any help with this question would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Tom Cruse 1,477 on at

Like (0)

Report

Larry are you saying that when a user logs in to GP the companies they're selecting from might be residing in a different server??

That drop-down list displays the Company Names from SY01500 all from the same server and instance.

Is what I mentioned above correct, or there something else that I'm missing?

Was this reply helpful? Yes No
Verified answer

Ian Grieve 22,784 on at

Like (0)

Report

You can use UserInfoGet in VBA to create an database connection to the server the user is logged into without the need for any manual connections; using UserInfoGet eliminates the possibility of your VBA connecting to the wrong server.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ian is right, unless your code do things differently based on the server it connects to

Was this reply helpful? Yes No
Larry Turner 1,325 on at

Like (0)

Report

Tom,

We have a production and test version of the same company on different servers. We need for the VBA to determine which server the user logged into when they signed into GP.

Thank you for your interest and help.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

In my Visual Studio applications I use GPConnNet and you can get the data source name using

Dynamics.Globals.SqlDataSourceName.Value

If this can be used using VBA then if you name your DSN using the server name that should give you what you need.

Was this reply helpful? Yes No
Larry Turner 1,325 on at

Like (0)

Report

Richard,

Thank you for your response. I will ask our VBA developer to let me know which approach they ultimately use so that I can verify the answer here. I'm not a VBA developer myself.

Was this reply helpful? Yes No
Tom Cruse 1,477 on at

Like (0)

Report

Gotcha, looks like Ian was right on :)

I didn't understand your setup, makes sense now.

Was this reply helpful? Yes No