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!
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
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.
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
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.
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.
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
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 ```
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
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
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.
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.
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.
The Add Library Reference helped a lot. Many Thanks!
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!
I wish I got here those 2 years ago. Great effort! Good job!
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
Thanks for uploaded video, its a useful for me to acess sql through vba
Hi i am getting syntax error at .open.connection string line an some one help me with this
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.
well done. its works for me.
Thank you! Saved my life! :D
what do i do if i want the end user to be running this as a parameterized report on excel front end....
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
Thank you for sharing. What if I want to get the "SQL" statement from a worksheet?
Hi Jie Jenn great video! Many thanks. if you had Stored procedures in SQL server how could you call them from VBA?
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.
it was real time output script.. very useful ..thanks a lot
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.
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.
Thank you so much for this sir. I have an error under--- . Open Source:=SQL_Statement (Variable not defined)
It worked, thank you very much!
Amazing video, thanks!
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
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
```
16:13 is the resulting report
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?
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
@@jiejenn Many thanks for the swift reply- I will test it out with my sample Databases in MS SQL
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
Your connection string is probably incorrect.
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.
Great video
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.
Are you accessing from a remote location?
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.
@@Aniket38 if you are sure you type your server name correctly, then it could be something to do with your company's security settings.
Ohh & that not possible for sure lol, anyways thanks your prompt reply :)
Mine is giving me an error "Invalid connection string attribute" what could it be?
Check if there is any typo in your connection string.
Great video. Link to file and code on Github seems to be not working. Any way you can provide updated link?
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.
Super explained sir
very useful sir
Thanks
Is there any chance that one day you will discover unthinkable possibility of using bigger fonts to make your videos watchable?
Font too small? Alright, I'll make them bigger in the future.
Jie Jenn Thank you very much indeed