Hi all,
I've been trying to convert the below code from sql server to run directly from excel vba. But unable to do so.
into VBA like below
But I get the following error:
The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.
Kindly help me guys!! Been trying this for weeks!
P.S. Do not require a Power Query as it will ask for credentials to the other users whom do not have access to the server
I've been trying to convert the below code from sql server to run directly from excel vba. But unable to do so.
Code:
Update Legal Set Category = Case
when datediff(month,GETDATE(),[End date])>6 then 'Blue'
when datediff(month,GETDATE(),[End date])<6 and datediff(month,GETDATE(),[end date])>1 then 'Orange'
when datediff(month,GETDATE(),[End date])<2 then 'Red'
End
where classification = 'B'
Select classification,datediff(month,GETDATE(),[End date]),Category from Legal
into VBA like below
Code:
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLSelect As String
Dim rs As ADODB.Recordset
Dim sqlcmd as string
cbb = Environ("computername")
With Sheet3.Range("A4:Z" & Rows.Count)
.ClearContents
'.ClearFormats
End With
[COLOR=#ff0000]sqlcmd[/COLOR] = "Update Legal Set Category = Case" & _
" when datediff(month,GETDATE(),[End date])>9 then 'Blue'" & _
" when datediff(month,GETDATE(),[End date])<9 and datediff(month,GETDATE(),[end date])>1 then 'Orange'" & _
" when datediff(month,GETDATE(),[End date])<2 then 'Red'" & _
" End " & _
" where classification = 'A'" & _
"Select classification,datediff(month,GETDATE(),[End date]),Category from Legal ""])"""
Debug.Print sqlcmd
With Sheet3.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=[COLOR=#ee82ee]Login_ID[/COLOR];Password=[COLOR=#ee82ee]Password[/COLOR];Data Source=[COLOR=#ee82ee]Server_Name[/COLOR];Use Procedure f" _
, _
"or Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & cbb & ";Use Encryption for Data=False;Tag with column collatio" _
, "n when possible=False;Initial Catalog=[COLOR=#ee82ee]DB_Name[/COLOR]"), Destination:=Sheet3.Range( _
"$A$4")).QueryTable
.CommandType = xlCmdSql
.CommandText = [COLOR=#ff0000][B]sqlcmd[/B] [/COLOR]
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\xxxxx\Documents\My Data Sources\xxxxx.odc"
.ListObject.DisplayName = _
"AP_123"
.Refresh BackgroundQuery:=False
End With
Sheet3.ListObjects("AP_123").Unlink
Sheet3.ListObjects("AP_123").Unlist
But I get the following error:
The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.
Kindly help me guys!! Been trying this for weeks!
P.S. Do not require a Power Query as it will ask for credentials to the other users whom do not have access to the server