Excel VBA Introduction Part 56.1 - Connect to SQL Server using ADO and the MSOLEDBSQL Driver

[@nuhzakir8896]

As always just perfect. Thank you.

[@WiseOwlTutorials]

Thank you for watching and taking the time to comment, it's much appreciated!

[@pollipudlipotty8300]

This was great, the video helps me in my work. Thanks!

[@WiseOwlTutorials]

You're welcome, happy to hear that it helped!

[@soalanmedia5523]

Thanks for your video, its very helpful for my work.

[@WiseOwlTutorials]

You're very welcome! Happy to hear that you found the video useful and thank you for leaving a comment!

[@KhalilYasser]

Thanks a lot for great tutorials.

[@WiseOwlTutorials]

You're welcome Yasser!

[@dineshkumarachari8757]

can you please create video for VBA connect to oracle sql developer database with using option service name ,hostname and more

[@joshuakilian3206]

Thank you so much for Part 56! This has helped me so much with setting up a User Frontend on Excel with SQL Server Express! I am wanting to eventually take the Database online to allow colleagues to add data and generate reports using the Excel Frontend. However, I am unsure of how to use the ADO connection to connect to this online remote server. Do you have any readings or knowledge on how to achieve this? Thank you again!

[@WiseOwlTutorials]

Hi Joshua! It depends which software you decide to use to host your database. I'm guessing that you're running SQL Server Express on your local machine for testing? If you're intending to continue using SQL Server Express and install it on a server machine you'll just need to adjust your connection string to point to that server rather than your local machine. If you're intending to use different database software you'll need to alter the connection string to take that into account. You can find lots of connection string examples for different software at connectionstrings.com I hope it helps!

[@joshuakilian3206]

@@WiseOwlTutorials Thank you for such a quick response! Yes I am using SQL Server Express on my local machine for testing. Ok perfect, I was hoping it would be something simple like just changing the server name. However, I am assuming there is additional work required for setting up Firewalls on the host machine and local machines to enable the communication? Thanks again!

[@WiseOwlTutorials]

@@joshuakilian3206 Hi Joshua! Yes indeed, there's quite a lot of work involved in setting up your server, but at least your VBA code won't need to change much!

[@joshuakilian3206]

@@WiseOwlTutorials Amazing! Thank you for all the input! I really appreciate it! Keep up the amazing work! Your videos are by far the best I have come across!

[@WiseOwlTutorials]

@@joshuakilian3206 Thanks Joshua, good luck!

[@tejamarneni]

First comment. Back on track. Yay!!!!!

[@WiseOwlTutorials]

:D excellent!

[@gayathrie5903]

The video was really helpful. Can we connect to Azure Devops through macro? What provider should we need to use?

[@WiseOwlTutorials]

Hi! I'm sorry I don't know!

[@OrangeBossa]

Thank you very much for these excellent tutorial videos! Got a question at 2:50, once you check the box for a library and click OK, next time when you open Excel and VBE, do you need to make the reference setting again? What about you send your Excel workbook for someone else to use, will Excel remember the setting, if not, where does it store the setting? Thanks!!

[@WiseOwlTutorials]

Hi! A reference that you set is saved in the VBA project (and therefore the Excel workbook) that you set the reference in. If you reopen the same Excel workbook the reference will still be set. If you create a new Excel workbook, you'll need to set the reference in the new VBA project. As for what happens when you send the Excel file to someone else, you may find this video helpful kzbin.info/www/bejne/p56tgZWrl5J3Zqc I hope it helps!

[@krn14242]

Thanks Andrew. Are you going to watch the Yankees vs Red Sox today in London?

[@WiseOwlTutorials]

Honestly, I didn't even realise that was happening! I can't claim to be much of a baseball fan I'm afraid. I do try to keep up with what's happening in the NFL although I rarely get chance to watch it these days!

[@manjuanth]

Could you please help me out connection reflection session

[@vinayupadhayay4004]

How to copy from Microsoft access passsword protected table from vba

[@noorsalh2822]

Hello, Thanks for those powerful videos. I am trying to use your method to connect to SQL server via VBA code in my FactoryTalk SE HMI. However, i am getting this error " SQL server network interface: connecting string is not valid [87]" any suggestion ?? Q2: my SQL server is not local, how can VBA communicate with the SQL ? Thanks and appreciate your support in advance.

[@WiseOwlTutorials]

Hi Noor, I've added a reply to your question on the other SQL video, I hope it helps!

[@Seema-vl1gb]

CopyFromRecordset is used for vba excel. I am writing same in vba word. Pls help me how can I fetch the record

[@WiseOwlTutorials]

Hi Prachi! There's no built-in method for this in Word as far as I know. You could loop through the recordset and write each record into the Word document one-by-one. Or you could just use the CopyFromRecordset method in Excel to get the data into a worksheet quickly and then copy the Excel table into the Word document. There are some other suggestions which you might find useful here www.pcreview.co.uk/threads/copy-from-recordset-ms-word.3650874/ I hope it helps!

[@rks42188]

As a Indian how can I join your self paced classes.

[@soalanmedia5523]

God

[@barrysmith6416]

How do I write the string so the user gets an Azure AD Prompt to sign in and then allows access to the specified Azure SQL Server??? I've tried Authentication=ActiveDirectoryInteractive, but it gives me an error about not being federated...

[@WiseOwlTutorials]

Sorry I don't know the answer to that one

[@juanpablohorn6642]

Does the old code of part 30-32 work with the new driver?

[@WiseOwlTutorials]

Hi Juan Pablo, yes the MSOLEDBSQL Driver allows you to use the same techniques described in the earlier videos.

[@juanpablohorn6642]

@@WiseOwlTutorials Thanks a lot Andrew! I've been following the whole VBA Introduction playlist and I've learned so much. So thanks again! Your content is great!

[@WiseOwlTutorials]

@@juanpablohorn6642 Thanks Juan Pablo, really pleased to hear that you found the VBA videos helpful, thanks for watching!

[@jayjayf9699]

I have a question; do you think VBA will become obsolete? Because I don't want to spend a lot of time learning , and find out time has been wasted.

[@WiseOwlTutorials]

In the short to medium term, no, definitely not. Even if it does, the general programming skills you'll pick up by learning VBA will be transferable to other languages. I learnt BASIC in the 1980s and that's about as dead as languages get, but the general principles are still very much the same in modern languages.

[@jayjayf9699]

@@WiseOwlTutorials thanks, i'm so confused in my career direction, I think of booking my self in to one of your London courses, I've graduated in mathematics a while ago, and was a data analyst for a while, not sure if I want to become an actuary as I heard they use VBA a lot, same as a pricing analyst. what other languages would be easy to learn after picking up the concepts from VBA?

[@WiseOwlTutorials]

Indeed, you'll find VBA used in plenty of financial roles (we do a fair amount of VBA training across a range of departments at PwC for example). After VBA you'll find the same basic concepts (along with many more advanced ones) in pretty much any other object-oriented language. VB.NET would be an obvious choice although will provide perhaps fewer opportunities - Visual C# would be my choice as it's more widely used. If analysis is your thing then maybe Python or R. Bear in mind that I'm not a careers advisor so take all this with a pinch of salt. Maybe try looking for some job listings for a particular role and see what skills they're asking for. Best of luck with whatever you choose!

[@carlitos4007]

Is compatible con TLS 1.2?

[@WiseOwlTutorials]

I'm sorry I don't know!