Import Data from Access Q

  • Thread starter Thread starter John
  • Start date Start date
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
 
To add something further

I have re-recorded the macro exactly as it was again, it works, but after
the first time you go into the access database concerned and then try and
run the macro it comes back with this same error. It doesn't matter then if
the database is open or closed, or you close and open Excel again, the same
error appears
 
Back
Top