Programatically finding instances of SQL Server 2008

I have an MS Access 2010 app that uses SQL Server at the back end. I need to introduce a startup splash to identify the available SQL Servers and populate a listbox with the server names.

The word is that this can be done using SQLDMO, but MS retired it I understand and do not advise use after SQL Server 2005. Can anyone help with any sample code that might give me what I need?
martmacAsked:
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.

sameer2010Commented:
0
martmacAuthor Commented:
Hi,

Thanks SO MUCH for this, you are a life saver.

Excuse my ignorance, but I am relatively new to this, re the load event code, would this populate a listbox (istSQLServers)? Ni need for Add,item? Does the listbox need to be set to Quesry rather than value list?
0
martmacAuthor Commented:
OK have done as you suggest created a module, copied your code in and used your name on my listbox etc and get an error highlighting GETSqlServers on the load event saying

Expected variable or procedure not Module
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Barry CunneyCommented:
Hi martmac
What did you name the module you created - by any chance did you name the module GetSQLServers

This is what the error message is saying - it thinks GetSQLServers is a module
0
martmacAuthor Commented:
I did indeed, I warned you of my ignorance!!

Have now renamed. Now het a Type Mismatch error on this ine on th load event

Set lstSQLServers = Forms!Frm_StartupSplash.lstSQLServers
0
Barry CunneyCommented:
I think I may have had a typo in my original code
should be

Dim lstSQLServers as ListBox

so the lstSQLServers variable needs to be declared as type listbox because we are then assigning your actual listbox(is your listbox an actual MS Access listbox control?) to this variable
>>Set lstSQLServers = Forms!Frm_StartupSplash.lstSQLServers
, so as we can then pass it to the function so as the function can then get all the SQL Servers and put them into this listbox
0
martmacAuthor Commented:
Had already picked that up and adjusted it.

Yes it is an MSAccess ListBox

When I debug your code in the module I get this (see image)See error
0
Barry CunneyCommented:
I had this slightly incorrect in my original post

That line should be

serverList.AddItem Pointer2stringw(sv100.name)



i.e. the correct method to add an entry to an MS Access listbox is AddItem not Add
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
martmacAuthor Commented:
That did the trick thanks very much for your patience and help.
0
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 Access

From novice to tech pro — start learning today.