Alan,
Ok, I almost have it. Thanks for pointing me to the update query!
This will allow me to pass sheet refrences to my sql query and allow me to place tables into my workbook, I can insert columns and place formulas into the table and upon refresh formulas will remain. However I need one more thing: I need to be able to move my table anywhere in my worksheet and update the table by table name. Not just a static cell refrence, but by table name refrence. I know it has to deal with the target line in my vba but I can not figure out how to set the correct property, or if it is even possible. Any guidance will be much help!
Option Explicit
Function GetTestConnectionString() As String
GetTestConnectionString = OleDbConnectionString("6-101dbsrv", "6-101db", "", "")
End Function
Function GetTestQuery() As String
GetTestQuery = "SELECT EIACFT.EI_SN AS 'TAIL #', ENDITEM.STATUS, ENDITEM.EI_BEG_AGE AS 'HOURS'," _
& " EIACFT.PHASE_DUE AS 'PHASE DUE', EIACFT.PHASE_NO AS 'PMI Sequence #', MIG_LOG.DATE_TIME_STAMP AS 'LAST MIGRATED'" _
& " FROM dbo.EIACFT EIACFT, dbo.ENDITEM ENDITEM, dbo.MIG_LOG MIG_LOG" _
& " WHERE EIACFT.EI_ID = ENDITEM.EI_ID AND MIG_LOG.TAG_ID = ENDITEM.EI_ID AND ((ENDITEM.UIC_OWN='" + Range("H3") + "') AND (ENDITEM.DEL_FLAG=0))" _
& " ORDER BY EIACFT.EI_SN"
End Function
' ===== Import Using QueryTable =====
Sub TestImportUsingQueryTable()
Dim conString As String
conString = GetTestConnectionString()
Dim query As String
query = GetTestQuery()
Dim target As Range
Set target = ThisWorkbook.Sheets(1).Cells(5, 2)
Select Case ImportSQLtoQueryTable(conString, query, target)
Case Else
End Select
End Sub
' ===== Connection String Functions =====
Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, _
ByVal Username As String, ByVal Password As String) As String
If Username = "" Then
OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
& ";Initial Catalog=" & Database _
& ";Integrated Security=SSPI;Persist Security Info=False;"
Else
OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
& ";Initial Catalog=" & Database _
& ";User ID=" & Username & ";Password=" & Password & ";"
End If
End Function
' Source: http://support.microsoft.com/kb/816562
Function StringToArray(Str As String) As Variant
Const StrLen = 127
Dim NumElems As Integer
Dim Temp() As String
Dim i As Integer
NumElems = (Len(Str) / StrLen) + 1
ReDim Temp(1 To NumElems) As String
For i = 1 To NumElems
Temp(i) = Mid(Str, ((i - 1) * StrLen) + 1, StrLen)
Next i
StringToArray = Temp
End Function
Function ImportSQLtoQueryTable(ByVal conString As String, ByVal query As String, _
ByVal target As Range) As Integer
On Error Resume Next
Dim ws As Worksheet
Set ws = target.Worksheet
Dim address As String
address = target.Cells(1, 1).address
' Procedure recreates ListObject or QueryTable
If Not target.ListObject Is Nothing Then ' Created in Excel 2007 or higher
target.ListObject.Refresh 'DELETE
ElseIf Not target.QueryTable Is Nothing Then ' Created in Excel 2003
'target.QueryTable.ResultRange.Clear
'target.QueryTable.Refresh
'Delete
End If
If Application.Version >= "12.0" Then ' Excel 2007 and higher
With ws.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;" & conString), Destination:=Range(address))
.Name = "test"
With .QueryTable
.CommandType = xlCmdSql
.CommandText = StringToArray(query)
.BackgroundQuery = True
.SavePassword = True
.Refresh BackgroundQuery:=True
End With
End With
Else ' Excel 2003
With ws.QueryTables.Add(Connection:=Array("OLEDB;" & conString), _
Destination:=Range(address))
.CommandType = xlCmdSql
.CommandText = StringToArray(query)
.BackgroundQuery = True
.SavePassword = True
.Refresh BackgroundQuery:=True
End With
End If
ImportSQLtoQueryTable = 0
End Function
Thanks
Bookmarks