checking Excel VBA connection to SQL Server

[almagg]

so i connected to an Access query on my machine and had no problem.

now i did a job once where Excel was connected to a SQL Server and i still have the source files.
all i did there was hardcode a 'Refresh' so it did not matter how thed user's machine was set up.

i copied the 'ConnectionString' and 'strSQL' info from the Data/Connections/Properties window in Excel.

since i do not have access to SQL server i was wondering if the below code would work. i like the option of not having to have an Excel file that is already 'connected'.

instead everything is right in the code.

just practicing. and thanks.

  Sub CreateQuery()
  Dim objMyConn As New ADODB.Connection
  Dim objMyRecordSet As New ADODB.Recordset
  Dim strSQL As String
  Dim objMyQueryTable 'As QueryTable

  ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
  "Persist Security Info=True;Initial Catalog=QMDSE;Data Source=ATLWDB36;" & _
  "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
  "Workstation ID=DYCDTRSIKAND;Use Encryption for Data=False;" & _
  "Tag with column collation when possible=False"

  objMyConn.Open ConnectionString

  strSQL = "SELECT office_id, office_desc, work_date, Supervisor_Fullname,” & _ 
  “Tech_Fullname, prior_day_view, time_start, ticket_arrive, first_gps_arrival,” & _ 
  “clock_arrive_delta, start_work_delta, qm_arrive_delta, pre_qm_arrive_ping_qty,” & _
  “first_esketch_ticket_number, esketch_arrive_onsite_color, ticket_distance,” & _
  “image_distance, startup_route_url, entry_date_local, start_work_notation" & _
  "FROM [QMDSE].[dbo].[RPT_Morning_Route]" & _
  "WHERE datediff(day, work_date,  (SELECT MAX(work_date)" & _
  "FROM RPT_Morning_Route WITH (NOLOCK)" & _
  "where DATEPART(dw, work_date) not IN (1, 7))) = 0" & _
  "order by office_id, work_date, Supervisor_Fullname, Tech_Fullname"

  objMyRecordSet.Open strSQL, objMyConn  
  Set objMyQueryTable = ActiveSheet.QueryTables.Add(objMyRecordSet, Range("A1"))
  objMyQueryTable.Refresh True

  objMyRecordSet.Close
  objMyConn.Close

  Set objMyRecordSet = Nothing
  Set objMyConn = Nothing

  End Sub