locked
connection string from excel macro RRS feed

  • Question

  • what's wrong on this excel macro connection string or recordset?

           Dim Conn1 As New ADODB.Connection
           Dim Cmd1 As New ADODB.Command
           Dim Errs1 As Errors
           Dim Rs1 As New ADODB.Recordset
    
           Dim i As Integer
           Dim connectionString As String
    
    
           connectionString = "Server=fsze88-pc;Database=test;User Id=sa;Password=fs709021;" ' it works on vb.net but not vba
           connectionString = "Provider=SQLOLEDB;Initial Catalog=test;Data Source=fsze88-pc;UID=sa;PWD=fs709021;"
    
           Conn1.Open connectionString
    
           Set Rs1 = Conn1.Execute("SELECT * FROM tbl_Students")
           For i = 0 To Rs1.RecordCount - 1
           Sheet1.Cells(1, i).Value = Rs1("FirstName")
           Next
           
           Rs1.Close
           Conn1.Close

    Saturday, September 7, 2013 3:47 PM

Answers

  • I solved by add a line

    Conn1.CursorLocation = adUseClient

    Sunday, September 8, 2013 11:02 AM

All replies

  • I am no expert on SQL strings, but I would have thought that the syntax would be:

    ConnectionString = "Provider=SQLOLEDB; Data Source=fsze88-pc; Initial Catalog=test; User ID=sa; Password=fs709021;"


    Graham Mayor - Word MVP
    www.gmayor.com

    Sunday, September 8, 2013 5:46 AM
  • I solved by add a line

    Conn1.CursorLocation = adUseClient

    Sunday, September 8, 2013 11:02 AM