Importing some Tables 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?

I have also 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

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
 
from the code it seems to be trying to add a new
querytable. then at the bottom it is saying on one line to
..backgroundquery = true then further down
..refresh backgroundquery = false.
seems to have conflicts.
1. add then refresh???? once added all you need there
after is refresh. does your range finder id the
querytable? I would suspect that it is adding multiple
querytables over and over again.
2. true then false??? which do you want? background query
= false pauses other code until the refresh is complete.
try commenting the refresh line out and run the macro
let me know.
 
Thanks for your reply, I recorded the macro which added a new query, suppose
then I'm trying to to the same thing the next time I run the macro .i.e. add
a new query, except it des run the next time, except when I actually go into
the database, then I can't run it, it seems strange
 
Some updates

By changing the line Refresh BackgroundQuery:=False to "True"

I can now access the database and run the macro without any apparent
problems, the only issues I see is that it Adds a new Named Range everytime
I run the query although visually it overwrites what was there. Not sure if
this will balloon the size of my file
 
hi again,

it will balloon the file in size. the problem is the "add"
part. you are adding a new query each time you run it -
one over the other.
like i said. once added all you need to do after that is
refresh.
 
Thanks for your reply, my code is for adding

Is there a simple code for Refresh or should I just go ahead and record it?

Thanks
 
Hi, recorded it and works a treat, its exactly what I required from the very
start

Thanks
 
Back
Top