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

Поділитися
Вставка
  • Опубліковано 5 січ 2025

КОМЕНТАРІ • 45

  • @z-wo4ue
    @z-wo4ue Рік тому

    The Add Library Reference helped a lot. Many Thanks!

  • @mauriciofbv
    @mauriciofbv 4 роки тому +5

    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!

  • @SimpleExcelVBA
    @SimpleExcelVBA 3 роки тому

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

  • @iamgroot8954
    @iamgroot8954 3 роки тому +1

    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

  • @trilok98
    @trilok98 5 років тому

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

  • @dishantmohite1762
    @dishantmohite1762 4 роки тому +3

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

    • @TheDuerden
      @TheDuerden 4 роки тому

      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.

  • @OmarRosado-e2v
    @OmarRosado-e2v 6 місяців тому

    well done. its works for me.

  • @theFactsPage
    @theFactsPage 3 роки тому

    Thank you! Saved my life! :D

  • @petermaina7111
    @petermaina7111 4 роки тому

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

  • @amabaltica
    @amabaltica 2 роки тому

    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

  • @sharonsitu8788
    @sharonsitu8788 3 роки тому

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

  • @1ajdelacruz
    @1ajdelacruz 5 років тому +1

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

    • @jiejenn
      @jiejenn  5 років тому +1

      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.

  • @pravinwaghchoure2277
    @pravinwaghchoure2277 4 роки тому

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

  • @aehrartasavoff5172
    @aehrartasavoff5172 2 роки тому

    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
      @jiejenn  2 роки тому

      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.

  • @real1flame59
    @real1flame59 3 роки тому

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

  • @gdehssalto8175
    @gdehssalto8175 3 роки тому

    It worked, thank you very much!

  • @Dan-ie2ip
    @Dan-ie2ip 3 роки тому

    Amazing video, thanks!

  • @zm2813
    @zm2813 5 років тому

    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
      @jiejenn  5 років тому +1

      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
      ```

  • @apergiel
    @apergiel 5 років тому +2

    16:13 is the resulting report

  • @1ajdelacruz
    @1ajdelacruz 5 років тому

    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
      @jiejenn  5 років тому +2

      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
      @1ajdelacruz 5 років тому

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

  • @serge9081
    @serge9081 3 роки тому

    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
      @jiejenn  3 роки тому

      Your connection string is probably incorrect.

  • @petermaina7111
    @petermaina7111 4 роки тому

    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.

  • @davidcardona31
    @davidcardona31 3 роки тому

    Great video

  • @Aniket38
    @Aniket38 5 років тому

    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
      @jiejenn  5 років тому

      Are you accessing from a remote location?

    • @Aniket38
      @Aniket38 5 років тому

      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
      @jiejenn  5 років тому

      @@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
      @Aniket38 5 років тому

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

  • @lucasdenker3734
    @lucasdenker3734 3 роки тому

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

    • @jiejenn
      @jiejenn  3 роки тому +1

      Check if there is any typo in your connection string.

  • @askeskovandersen3993
    @askeskovandersen3993 3 роки тому

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

    • @jiejenn
      @jiejenn  3 роки тому

      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.

  • @KumarAnalytic
    @KumarAnalytic 5 років тому

    Super explained sir

  • @pravinwaghchoure2277
    @pravinwaghchoure2277 4 роки тому

    very useful sir

  • @nyya0509
    @nyya0509 Рік тому

    Thanks

  • @marcing1390
    @marcing1390 6 років тому +2

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

    • @jiejenn
      @jiejenn  6 років тому +2

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

    • @marcing1390
      @marcing1390 6 років тому +1

      Jie Jenn Thank you very much indeed