'Option Explicit

Sub TESTQT()

'Dim qt As QueryTable
Dim Datum As Long
Dim SQL As String
Dim conn As String
'ndatum = Application.InputBox(prompt:="Vložte datum vystavení dokladu DO:", Default:=Format(Date, "d.m.yyyy"), Type:=2)
ndatum = ActiveCell(1, -1)
Datum = ndatum
SQL = "SELECT sum(A.LocalAmount) "
SQL = SQL + " -sum((SELECT  LocalAmount FROM  PaymentAmountsForRCreditNote (A.ID, " & Datum & ")))"
SQL = SQL + " +sum((SELECT  LocalPAmount FROM  PaymentAmountsForRCreditNote (A.ID, " & Datum & ")))"
SQL = SQL + " -sum((SELECT  COALESCE(SUM(LocalPAmount),0) FROM  PaymentsForDocument ('04', A.ID, " & Datum & ")))"
SQL = SQL + " FROM ReceivedInvoices A"
conn = "ODBC;DSN=ABRA"
Set qtable = ActiveSheet.QueryTables.Add(Connection:=conn, Destination:=Range(ActiveCell.Address), SQL:=SQL)
qtable.FieldNames = False
'qtable.SaveData = False
'qtable.MaintainConnection = False
qtable.Refresh BackgroundQuery:=False
'qtable.Refreshing
'    .Name = "Contact List"
'    .FieldNames = True
'    .RowNumbers = False
'    .FillAdjacentFormulas = False
'    .PreserveFormatting = True
'    .RefreshOnFileOpen = False
'    .BackgroundQuery = True
'    .RefreshStyle = xlInsertDeleteCells
'    .SavePassword = True
'    .SaveData = True
'    .AdjustColumnWidth = True
'    .RefreshPeriod = 0
'    .PreserveColumnInfo = True
'    .Refresh BackgroundQuery:=False

End Sub

Sub getFA()

Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim rs As DAO.Recordset
Dim sConn As String
Dim xlSheet As Worksheet
Dim iFieldCount As Integer
Dim i As Integer

Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

sConn = "ODBC;DSN=ABRA"

Set wrk = CreateWorkspace("", "", "", dbUseODBC)
Set cnn = wrk.OpenConnection("", , , sConn)
Set rs = cnn.OpenRecordset("Select * FROM Accounts", dbOpenDynamic)



End Sub

Sub getFA2()


Set cnnConnect = "ODBC;DSN=ABRA"

Set rstRecordset = New ADODB.Recordset
strSQL = "SELECT * from Accounts"

rstRecordset.Open Source:=strSQL, _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

varMaxYear = rstRecordset.Fields("MaxOfPYEAR").Value
rstRecordset.Close
End Sub