J
John
I have recorded the following macro which pulls tables within my excel
spreadsheet, my problem is that I am getting a debug error on this line
(which is my very last line, stating SQL syntax error
..Refresh BackgroundQuery:=False
Don't really know what this line does, will it disallow me from accessing
the access tables if the database is open?
The full recorded macro is
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir=C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _
"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.PayrollNum,
Employees.EmployeeType, Employees.Forename, Employees.Surname,
Employees.EmpAddress1, Employees.EmpAddress2, Employees.EmpAddress3,
Employees.EmpAddres" _
, _
"s4, Employees.DateOfBirth, Employees.Termination Date,
Employees.CommencementDate, Employees.PayRate, Employees.NatInsNum" &
Chr(13) & "" & Chr(10) & "FROM `C:\timepoint\Timepoint_be`.Employees
Employees" & Chr(13) & "" & Chr(10) & "ORDER BY Employees.Surna" _
, "me")
.Name = "Query from Timepoint"
.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
End Sub
spreadsheet, my problem is that I am getting a debug error on this line
(which is my very last line, stating SQL syntax error
..Refresh BackgroundQuery:=False
Don't really know what this line does, will it disallow me from accessing
the access tables if the database is open?
The full recorded macro is
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir=C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _
"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.PayrollNum,
Employees.EmployeeType, Employees.Forename, Employees.Surname,
Employees.EmpAddress1, Employees.EmpAddress2, Employees.EmpAddress3,
Employees.EmpAddres" _
, _
"s4, Employees.DateOfBirth, Employees.Termination Date,
Employees.CommencementDate, Employees.PayRate, Employees.NatInsNum" &
Chr(13) & "" & Chr(10) & "FROM `C:\timepoint\Timepoint_be`.Employees
Employees" & Chr(13) & "" & Chr(10) & "ORDER BY Employees.Surna" _
, "me")
.Name = "Query from Timepoint"
.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
End Sub