Long Query when importing data from Access

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top