Long Query when importing data from Access

[USAMax]

I recorded the following code from an import and I am trying to clean it up so I can change a variable or two. The strange thing is that it throws a comma in the middle of the code. I have to assume it is because the line is too long but if that is the case, how do I get around it?

The first one is in the FROM clause where it should say Products.accdb but it says Pro", "ducts.accdb
The second one is in the WHERE clause where it should say DistributorID but it says Distribu", "torId

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=F:\Shaired Data\Dave's Documents\Products.accdb;DefaultDir=F:\Shaired Data\Dave's Documents;DriverId" _
), Array("=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination _
:=Range("$A$2")).QueryTable
.CommandText = Array( _
"SELECT Distributor.DistributorName, " & _
"SalesReps.lastName, SalesReps.firstName, SalesReps.`Hire Date`, SalesReps.HourlyWage, " & _
"Orders.OrderDate, Orders.ProductId" & Chr(13) & "" & Chr(10) & _
"FROM `F:\Shaired Data\Dave's Documents\Pro", "ducts.accdb`.Distributor Distributor, " & _
"`F:\Shaired Data\Dave's Documents\Products.accdb`.Orders Orders, " & _
"`F:\Shaired Data\Dave's Documents\Products.accdb`.SalesReps SalesReps" & Chr(13) & "" & Chr(10) & _
"WHERE Distributor.Distribu", "torId = Orders.DistributorId AND " & _
"Distributor.DistributorId = SalesReps.DistributorId AND " & _
"((SalesReps.lastName='Steve') AND " & _
"(Orders.OrderDate<{ts '2011-03-22 00:00:00'}))" & Chr(13) & "" & Chr(10) & _
"ORDER BY Orders.OrderDate")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
.Refresh BackgroundQuery:=False
End With

 

[Fazza]

Maybe like below, Dave? The recorded macro just broke up the long string. Do whatever works for you (edit the string) to get what you need. The code adds a new query table. You may not always want that? Once there is a query, if you're keeping the same one just change the SQL (aka command text) and refresh it. Hardly any code at all. Once you're up to speed with query tables (which are great but limited), take a look at ADO (which are more controllable/powerful).

BTW, as usual, the macro recorder has thrown in some lines that you can do without. This is unimportant though.

regards

Dim strConn As String
Dim strSQL As String

strConn = "ODBC;DSN=MS Access Database;DBQ=F:\Shaired Data\Dave's Documents\Products.accdb;DefaultDir=F:\Shaired Data\Dave's Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

strSQL = Join$(Array( _
    "SELECT DI.DistributorName, SR.lastName, SR.firstName, SR.[Hire Date], SR.HourlyWage, OR.OrderDate, OR.ProductId", _
    "FROM Distributor DI, Orders OR, SalesReps SR", _
    "WHERE DI.DistributorId = OR.DistributorId AND DI.DistributorId = SR.DistributorId AND SR.lastName='Steve' AND OR.OrderDate<{ts '2011-03-22 00:00:00'}", _
    "ORDER BY OR.OrderDate"), vbCr)

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=strConn, Destination:=Range("$A$2")).QueryTable
  .CommandText = strSQL
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  .ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
  .Refresh BackgroundQuery:=False
End With