Replace with result of database query, instead of inserting
 

I'm using thecode below (derived using Record Macro, and them modified to
include file and directory name variables) to extract data from an Access
database table and copy to a worksheet called "LinkedData3".

It works, but it inserts the data, and so the sheet grows with every
refresh. Could someone tell me how I can change the code to "replace" the
full content of the sheet rather than "insert"?

Regards...
================================================== =====

With
Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & ReportFileName & ";DefaultDir=
&" & DataFileDirectory & ";DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")),
Destination:=Worksheets("LinkedData3").Range("A1") )
.CommandText = Array( _
"SELECT `Agent Report`.datetime, `Agent Report`.groupNumber, `Agent
Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall, `Agent
Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _
, _
"ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime,
`Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13) & ""
& Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent Report`" &
Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _
, _
"t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent
Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) & "" &
Chr(10) & "ORDER BY `Agent Report`.datetime" _
)
.Name = "Query from MS Access Database"
.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 With

Replace with result of database query, instead of inserting
 

Android

The best way to do this is to not Add a QueryTable every time you want to
run it. You can just change some properties of your QT and Refresh it. It
looks like you're just changing the connection string and not the sql. See
here

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

Yours will be a little different than what's on that page. You need to
extract the old database and directory and replace it with the new one.
Here's an example:

Dim OldReport As String
Dim DBQStart As Long
Dim DBQEnd As Long
Dim OldDirect As String
Dim DefStart As Long
Dim DefEnd As Long

Const sDBQ As String = "DBQ="
Const sDef As String = "DefaultDir="

With Sheet1.QueryTables(1)
DBQStart = InStr(1, .Connection, sDBQ) + Len(sDBQ)
DBQEnd = InStr(DBQStart, .Connection, ";")
OldReport = Mid(.Connection, DBQStart, DBQEnd - DBQStart)

DefStart = InStr(1, .Connection, sDef) + Len(sDef)
DefEnd = InStr(DefStart, .Connection, ";")
OldDirect = Mid(.Connection, DefStart, DefEnd - DefStart)

.Connection = Left(.Connection, DBQStart - 1) _
& Replace(.Connection, OldReport, _
ReportFileName, DBQStart, 1)
.Connection = Left(.Connection, DefStart - 1) _
& Replace(.Connection, OldDirect, _
DataFileDirectory, DefStart, 1)

.Refresh

End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Android" wrote in message
...
I'm using thecode below (derived using Record Macro, and them modified to
include file and directory name variables) to extract data from an Access
database table and copy to a worksheet called "LinkedData3".

It works, but it inserts the data, and so the sheet grows with every
refresh. Could someone tell me how I can change the code to "replace" the
full content of the sheet rather than "insert"?

Regards...
================================================== =====

With
Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & ReportFileName &
";DefaultDir=
&" & DataFileDirectory & ";DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")),
Destination:=Worksheets("LinkedData3").Range("A1") )
.CommandText = Array( _
"SELECT `Agent Report`.datetime, `Agent Report`.groupNumber,
`Agent
Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall, `Agent
Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _
, _
"ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime,
`Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13) &
""
& Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent Report`"
&
Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _
, _
"t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent
Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) & ""
&
Chr(10) & "ORDER BY `Agent Report`.datetime" _
)
.Name = "Query from MS Access Database"
.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 With

Replace with result of database query, instead of inserting
 

Thanks for the help. This is a little complex for me for now -- I need to
spend more time before coding it.

However, FYI, a workaround I have implemented in the meanwhile is

1) Copy the new data to when my defined Ranges moved to:
Worksheets("Linked Data").Range("a1:L65536").Copy
Destination:=Worksheets("Linked Data").Range("M1")
2) Delete the original location of the new data
Worksheets("Linked Data").Range("A:L").Delete shift:=xlShiftToLeft

Not pretty, and the column ranges are hardcoded -- but does an interim job.

Thanks again for the code you provided. I will need it to make this more
reliable.

Android.


"Dick Kusleika" wrote in message
...
Android

The best way to do this is to not Add a QueryTable every time you want to
run it. You can just change some properties of your QT and Refresh it.
It
looks like you're just changing the connection string and not the sql.
See
here

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

Yours will be a little different than what's on that page. You need to
extract the old database and directory and replace it with the new one.
Here's an example:

Dim OldReport As String
Dim DBQStart As Long
Dim DBQEnd As Long
Dim OldDirect As String
Dim DefStart As Long
Dim DefEnd As Long

Const sDBQ As String = "DBQ="
Const sDef As String = "DefaultDir="

With Sheet1.QueryTables(1)
DBQStart = InStr(1, .Connection, sDBQ) + Len(sDBQ)
DBQEnd = InStr(DBQStart, .Connection, ";")
OldReport = Mid(.Connection, DBQStart, DBQEnd - DBQStart)

DefStart = InStr(1, .Connection, sDef) + Len(sDef)
DefEnd = InStr(DefStart, .Connection, ";")
OldDirect = Mid(.Connection, DefStart, DefEnd - DefStart)

.Connection = Left(.Connection, DBQStart - 1) _
& Replace(.Connection, OldReport, _
ReportFileName, DBQStart, 1)
.Connection = Left(.Connection, DefStart - 1) _
& Replace(.Connection, OldDirect, _
DataFileDirectory, DefStart, 1)

.Refresh

End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Android" wrote in message
...
I'm using thecode below (derived using Record Macro, and them modified
to
include file and directory name variables) to extract data from an
Access
database table and copy to a worksheet called "LinkedData3".

It works, but it inserts the data, and so the sheet grows with every
refresh. Could someone tell me how I can change the code to "replace"
the
full content of the sheet rather than "insert"?

Regards...
================================================== =====

With
Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & ReportFileName &
";DefaultDir=
&" & DataFileDirectory & ";DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")),
Destination:=Worksheets("LinkedData3").Range("A1") )
.CommandText = Array( _
"SELECT `Agent Report`.datetime, `Agent Report`.groupNumber,
`Agent
Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall,
`Agent
Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _
, _
"ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime,
`Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13)
&
""
& Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent
Report`"
&
Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _
, _
"t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent
Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) &
""
&
Chr(10) & "ORDER BY `Agent Report`.datetime" _
)
.Name = "Query from MS Access Database"
.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 With