VBA to connect to SQL Server

[VidyaKruthi]

Hi All Good Evening,

Please help me out how to connect to SQL Server and get data using VBA.

 

[SvetB]

Derangeddm, thanks for the clarification. At the end it didn't work for me until in the connection string I used AttachDBFileName=C:\... with full path to DB (.mdf file)

 

[Mepwnz2010]

To start thanks for any help I get in advance. I have a workbook with SQL connection and query already working, but I have been asked to add a few columns. The information that I am adding could be in 1 of 30 different tables that are scattered across about 13 different servers. My company has built a template for a dynamic search to sift through all these tables across all the servers to find the information needed. So I've added this template to the already prebuilt query and I've got the query getting everything we need in SQL Server Management Studio. I know the query works because I use it daily, but when I add it to the workbook it pulls all the information that is on the server my connection string connects to but stops there. It runs fine with no errors it just doesn't give me everything I'm querying... I'm wondering if I would need to create a connection to all of the servers? Again thanks in advance for any advise.

 

[koltregaskes]

It has taken me ages to find a straightforward example like this to work from.

How do add the server username and password, I'm guessing it's this line:

"Integrated Security=SSPI;"

 

[koltregaskes]

I should add, I need to hardcode the username and password as our VBA query keeps popping up with the login prompt every time we run it and we want to stop the prompt. We use two SQL servers so I'll need to do this for both.

 

[Galley]

It has taken me ages to find a straightforward example like this to work from.

How do add the server username and password, I'm guessing it's this line:

"Integrated Security=SSPI;"

This connection string includes the User ID and Password.

    'Open Connection'
        objMyConn.ConnectionString = "Provider=IBMDA400;Data Source=YourSource;User Id=YourUserName;Password=YourPassword;Force Translate = 0;"
        objMyConn.Open

You will need to modify the Provider, Data Source, User ID and Password values.