How to Retrieve Data From Microsoft SQL Server with Excel VBA | Excel VBA Automation

[@z-wo4ue]

The Add Library Reference helped a lot. Many Thanks!

[@SimpleExcelVBA]

I wish I got here those 2 years ago. Great effort! Good job!

[@mauriciofbv]

Great tutorial, workerd perfectly for me. Just for the folks that are connecting to Azure SQL Server Database. I needed to add the following line to the connection string: strConn = strConn & "Trusted_Connection=False;Encrypt=True;" Thanks!

[@OmarRosado-e2v]

well done. its works for me.

[@iamgroot8954]

Jie Jenn, quick question. Can I store the SQL server in a network drive and allow multiple machines to connect to it? I'm doing it with Access, but I would like to do the same with SQL server. Thanks

[@theFactsPage]

Thank you! Saved my life! :D

[@Dan-ie2ip]

Amazing video, thanks!

[@pravinwaghchoure2277]

it was real time output script.. very useful ..thanks a lot

[@apergiel]

16:13 is the resulting report

[@gdehssalto8175]

It worked, thank you very much!

[@trilok98]

Thanks for uploaded video, its a useful for me to acess sql through vba

[@amabaltica]

Thanks for this great tutorial! Is it also possible to give a path to the Server / Database / Query instead of the names? Idea is to build a user interface, where the paths can be manually added, so that the code works from multiple PCs with different databases etc

[@1ajdelacruz]

Hi Jie Jenn great video! Many thanks. if you had Stored procedures in SQL server how could you call them from VBA?

[@jiejenn]

If you are calling a SP that is retrieving records from SQL Server, then you will treat the SP the same way as you would when you provide a SQL Statement. Just make sure that your SP is pulling one dataset only. If you have multiple batches in a SP, I think VBA will only pull the 1st dataset.

[@sharonsitu8788]

Thank you for sharing. What if I want to get the "SQL" statement from a worksheet?

[@real1flame59]

Thank you so much for this sir. I have an error under--- . Open Source:=SQL_Statement (Variable not defined)

[@1ajdelacruz]

Hi Jie Jenn great video! Many thanks.again. If you had bulk data files like records you just retrieved how to upload back into into SQL from excel?

[@jiejenn]

If you are working with a large dataset, I would recommend you use SSIS instead. Otherwise, if you want to perform an action query, you will have to concatenate your insert statement. For example, if I am trying to insert records from a worksheet to SQL Server, giving the code to create connection instance is the same, the VBA code will look like this Set rst = New ADODB.Recordset rst.ActiveConnection = conn Set ws = ThisWorkbook.Worksheets("data") With ws For i = 1 To 2 rst.Open Source:="INSERT INTO Kickstarter (Id, Name) VALUES(" & .Cells(i, "A").Value & ",'" & .Cells(i, "B").Value & "')" Next i End With Set ws = Nothing

[@1ajdelacruz]

@@jiejenn Many thanks for the swift reply- I will test it out with my sample Databases in MS SQL

[@dishantmohite1762]

Hi i am getting syntax error at .open.connection string line an some one help me with this

[@TheDuerden]

I had the same issue, I am running a sql 2019 server and the strConn = "Provider=SQLOLEDB;" is not correct for that purpose. You should check connectionstring . com for the right string. For me it is MSOLEDBSQL - may be different for you if you using a different provider.

[@davidcardona31]

Great video

[@petermaina7111]

kindly assist us with with more visible font..however i appreciate the content , i wanna know how to make parameterized reports querying sql views at any given point.

[@petermaina7111]

what do i do if i want the end user to be running this as a parameterized report on excel front end....

[@KumarAnalytic]

Super explained sir

[@aehrartasavoff5172]

Thank you this is really really helpful. However when I try to pull data from a sql table that has 5000 records, my excel sheet gets stuck and the query takes forever to run. How can I optimize the query? I already have indexes built on multiple columns.

[@jiejenn]

Not sure how I can help, there are many things could cause the issue. I am able to import 1 million rows without any issue.

[@pravinwaghchoure2277]

very useful sir

[@zm2813]

Thanks for video. If I have two databases and need to join them (Left Outer Join for example, do I need to specify database name?) What would be the code in this case? thanks

[@jiejenn]

If you have two tables you are trying to join from two separate databases, then you will have to reference the database + schema + table of both tables. For example, ```SQL FROM [Database1].dbo.Table1 T1 INNER JOIN [Database2].dbo.Table2 T1 ON T1.Id = T2.Id ```

[@askeskovandersen3993]

Great video. Link to file and code on Github seems to be not working. Any way you can provide updated link?

[@jiejenn]

This is a pretty old video, so I don't think I no longer have the VBA script. Might have to re-do the video sometime in the future.

[@nyya0509]

Thanks

[@Aniket38]

Hi Jie Jenn, I’m accessing different domain sql server, how to authenticate that server? (LDAP, Windows authentication) Getting login failed error, please advise and thanks in advance.

[@jiejenn]

Are you accessing from a remote location?

[@Aniket38]

Yes correct, I’ve system credentials to access that server remotely and also have DB server credential’s. FYI local policies configuration is not helpful in my case as I don’t have admin permission. please advice. Thanks.

[@jiejenn]

@@Aniket38 if you are sure you type your server name correctly, then it could be something to do with your company's security settings.

[@Aniket38]

Ohh & that not possible for sure lol, anyways thanks your prompt reply :)

[@serge9081]

Can't connect, there is an error fo connection runtime expired, the VBA shows this line in yellow ".Open ConnectionString:=strConn". What it might be ? Thanks

[@jiejenn]

Your connection string is probably incorrect.

[@lucasdenker3734]

Mine is giving me an error "Invalid connection string attribute" what could it be?

[@jiejenn]

Check if there is any typo in your connection string.

[@marcing1390]

Is there any chance that one day you will discover unthinkable possibility of using bigger fonts to make your videos watchable?

[@jiejenn]

Font too small? Alright, I'll make them bigger in the future.

[@marcing1390]

Jie Jenn Thank you very much indeed