Data from Access

  • Thread starter Thread starter MIchel Khennafi
  • Start date Start date
M

MIchel Khennafi

Good morning

In the following Macro, we are trying to get all the fields from a connected
database. If you look at the macro, you'll see that there are mising field
names (refer to the portion where you only have ' ' ' ' ' ' in the Array
definition

Why is it? is it an array limitation? Is there a way to tell Excel we wqant
all the fields in this table without having to list all of them?

Thanks a lot for your help

'

Cells.Select

Selection.ClearContents

Selection.QueryTable.Delete

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DSN=freight payment database;DBQ=X:\FREIGHT PAYMENT\Freight
Payment.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeou" _

), Array("t=5;")), Destination:=Range("origin"))

.CommandText = Array( _

"SELECT `report improper records to finance`.ID, `report improper
records to finance`.`Customer Number`, `report improper records to
finance`.`Airway Invoice #`, `report improper records to finance`.`B" _

, _

"alance Due Flag`, `report improper records to finance`.`CTSI Batch
Code`, `report improper records to finance`.`Bill to Number`, `report
improper records to finance`.`Bill Type`, `report improper reco" _

, _

"rds to finance`.Bound, `report improper records to
finance`.`Carrier Name`, `report improper records to finance`.`Check
Amount`, `report improper records to finance`.`Check Date`, `report improper
rec" _

, _

"ords to finance`.`Check Number`, `report improper records to
finance`.Shipper, `report improper records to finance`.`Shipper Address`,
`report improper records to finance`.`Shipper City`, `report impr" _

,,,,,,,,,,,,,,,,,,,,)

.Name = "Query from freight payment database_1"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = True

.SaveData = True
 
Michel:
Why is it? is it an array limitation? Is there a way to tell Excel we
wqant all the fields in this table without having to list all of them?
Click to expand...

This Access newsgroup might not be the best newsgroup to post to. Maybe an
Excel newsgroup would be better?

However, here are a few comments.

1. Normally, you use the asterisk (*) in an SQL statement to indicate
that you want all fields.

2. You have the Array function twice at the beginning of your connection
string.

3. If you record your macro, Excel puts in the Array function at the
beginning of the connection string and then uses a comma and underscore ( ,
_ ) without an ampersand (&) to indicate that the string continues on a new
line. I think you may be able to omit the array function and use the normal
VBA string continuation format as shown in the following example (which uses
the comma, underscore and ampersand) . I'm not sure whether this will work
in your situation - you will have to test it. To make the code more
readable, the following example initialises a connection string variable, a
destination-range object variable and a SQL string variable before using
them to create the new QueryTable. You might like to copy the example below
and paste it into a "macro" module in Excel, where it should become more
readable.

Sub MacroToCreateNewQueryTable()

' Declare working variables:
Dim objQT As Excel.QueryTable
Dim strConnection As String
Dim rngDestination As Excel.Range
Dim strSQL As String

' Prepare the Worksheet:
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete

' Initialise the connection string:
strConnection = _
"ODBC;DSN=freight payment database;" _
& "DBQ=X:\FREIGHT PAYMENT\Freight Payment.mdb;" _
& "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

' Initialise the destination range:
Set rngDestination = Range("origin")

' Initialise the SQL statement
' (using an asterisk to select all fields):
strSQL = "SELECT [report improper records to finance].* " _
& "FROM [report improper records to finance];"

' Create a new QueryTable object using the
' connection string and destination range:
Set objQT = ActiveSheet.QueryTables.Add(strConnection, rngDestination)

' Set the properties of the new QueryTable object:
With objQT
.CommandType = xlCmdTable
.CommandText = strSQL
.Name = "Query from freight payment database_1"
.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

Geoff
 
Back
Top