VBA Import from Access using ODBC - then trying to manipulate some columns; import comes after

[Chapman]

I am trying to import from Access using ODBC - then I want to
basically concatenate two of the columns.

If I run my macro it actually runs the concatenate part first, then
brings in the odbc information.

If I put a pause or step through it - it works no problem. Code is
provided..

What am I doing wrong?

Thanks in advance...
dc

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & fileandpath & ";DefaultDir="
& spath & "; DriverId=281;FIL=MS Access;MaxBufferSize =2048;
PageTimeout =5;")), Destination:=Range("A1"))


.CommandText = Array( _
"SELECT chart.account_number, chart.account_name,
chart.account_wp_num, chart.leadsheet_code, chart.leadsheet_subcode,
chart.begin_bal_dr, chart.begin_bal_cr, chart.transactions_dr,
chart.transactions_" _

.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:=True

End With


Range("a1").Select
Selection.EntireColumn.Insert


Range("A3").Select
ActiveCell.FormulaR1C1 = "=RC[4]&"" ""&RC[5]"
Range("A3").Select
Selection.Copy

 

[Tushar Mehta]

My guess is that the .BackgroundQuery = True is the culprit.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Chapman]

Thanks

The last line of the with statement should be true..

.Refresh BackgroundQuery = True


again thanks for your assistance

David