Get setting in sql server from the SaveSetting by vba

Hi
i would like to know whether its possible to get the values set be SaveSetting through vba in sql server procedure. actually, i would like to set a public value by access vba should be accessible by sql server.

Thanks in advance

Joseph
LVL 1
Joseph KrauszCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Joseph,

If by SaveSetting  you are referring to writing to the Windows registry then NO. AFAIK, SQL Server can not read the Windows registry on a client Machine.

The way I handle this is to use a a table in the back end database,  in your case SQL server, that I use to store preferences and settings similar to the way the Windows Registry works. I have been doing this in my software since the late 1980s.  I like storing stuff in a database and not on the local workstation. Makes roaming and disaster recovery a lot easier.
0
Joseph KrauszCEOAuthor Commented:
Thanks for the answer.
But I'm wondering how do you deal when you want to store user specific info like which user (not the workstation which I can get from the environ) logged in to the system and does the CRUD operation and use this info in SQL server ?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Joseph,

I handle it in the front end. I pass the user data as part of the CRUD operations to whatever back end I am using. I use this with front ends in Access, VB.net, VB6, PHP, ASP.net with many different back ends (SQL Server, .accdb, .mdb, mySQL, etc). This way it is back end independent.

 I also like top store the PC, sessions, or whatever makes since for that front end to local the device the user was on when performing the task.

 FWIW: I avoid using environ() info for security/audit since it is so easy to spoof.
0
Joseph KrauszCEOAuthor Commented:
I'm looking to run triggers  for audit log to save any operation user did based in the users log in to the front end system. So I believe that this will only be possible if I explicitly call a SP. is there any other way just to sore some local info in SQL server ?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Thanks for the additional information.

How are you authenticating with the SQL Server?  

Found this in my personal KB:

If SQL Server n2005 or later:


SELECT @UserName =  ORIGINAL_LOGIN() 

Open in new window


or

SELECT @UserName = SYSTEM_USER

Open in new window


SYSTEM_USER will return current user. From Book On-Line – SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER returns the name of the impersonated context.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.