Accessing SQL Database in Excel-VBA

by

I’ve added the Initial Catalog to your connection string. I’ve also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.

Hope this helps.

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "select * from myTable"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL            

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. Performing SQL queries on an Excel Table within a Workbook with VBA Macro
  3. Using Excel VBA to export data to MS Access table
  4. Excel function to make SQL-like queries on worksheet data?
  5. Using Excel VBA to run SQL query
  6. How do I call an Oracle stored procedure from an Excel VBA script?
  7. Can anyone help me with this SQL Query?
  8. How do I use parameters in VBA in the different contexts in Microsoft Access?
  9. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  10. How to run a SQL query on an Excel table?
  11. Export SQL query data to Excel
  12. how to pass parameters to query in SQL (Excel)
  13. How to use system username directly in MS Access query?
  14. Generate sql insert script from excel worksheet
  15. ADODB Connection String for .csv
  16. Access – compare two tables and update or insert data in first table
  17. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  18. Error trying to call stored procedure with prepared statement
  19. How to save the result of a SQL query into a variable in VBA?
  20. How to deal with single quote in Word VBA SQL query?
  21. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text
  22. Escaping ‘ in Access SQL
  23. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  24. Is there ever a time where using a database 1:1 relationship makes sense?
  25. SQL Error: ORA-01861: literal does not match format string 01861
  26. SQL Server 2008 – IF NOT EXISTS INSERT ELSE UPDATE
  27. Update a column of a table with a column of another table in PostgreSQL
  28. How to protect Excel workbook using VBA?
  29. SQL WITH clause example [duplicate]
  30. Insert all values of a table into another table in SQL

Leave a Comment