Excel Pivot table with AQL

Hi,
I have a question of query database for SQL server and display summary result on Excel Pivot Table. I have been write a VBA marco to connect SQL server , retrieve data and display on Excel Pivot Table as list in below:


Sub connectSQLObject()
Dim QArray As Variant
Dim Range1 As Range
Dim ConnectString As String
Dim PivotName As String
Dim Pivot3 As PivotTable

PivotName = "Pivot3"
Worksheets(1).Select
Set Range1 = Worksheets(1).Range("A30")

ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "\crmdbv310_RnD.MDF;" & "Driver={sql server};
SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"

QArray = Array(ConnectString, "SELECT * FROM tblCustomer WHERE CustCode < 1000")

PivotName = "Pivot3"
Worksheets(1).PivotTableWizard _
SourceType:=xlExternal, _
SourceData:=QArray, _
tableDestination:=Range1, _
tableName:=PivotName, BackgroundQuery:=False
Set Pivot3 = Worksheets(1).PivotTables(PivotName)

With Pivot3
.PivotFields("CustCode").Orientation = xlColumnField
.PivotFields("CustName").Orientation = xlRowField
.PivotFields("CompRegNo").Orientation = xlDataField
End With

End Sub


My problem are:
1. Connect SQL string problem:
I have writed a connection string statement for SQL server as show in below, this connection string is working but EXCEL always prompt a dialog box required me re-
enter password and user_id. (Note: the Database User_ID and Password are same that is 'Research') Could you help me debug below connection string. Thank you.
ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "\crmdbv310_RnD.MDF;" & "Driver={sql server};
SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"

2. SQL query statement limitation ?
If I used simple SQL statement to retreive data and display out via Excel Pivot Table, the pivot table can easy display out the relate data fields as show in below
code:
.......
QArray = Array(ConnectString, "SELECT * FROM tblCustomer WHERE CustCode < 1000")

.......
.PivotFields("CustCode").Orientation = xlColumnField
.PivotFields("CustName").Orientation = xlRowField
.PivotFields("CompRegNo").Orientation = xlDataField
........

Above query statement just limits on retreive data filed on a table from database. I have been tried join to table and display data but Excel cannot execute. The code
as show in below:
.......
QArray = Array(ConnectString, "SELECT * FROM tblCustomer.StateID ,
tblAddressState.StateName, tblCustomer.CustName , tblCustomer.CustCode, tblCustomer.CompRegNo WHERE
tblCustomer.StateId = tblAddressState.StateId")

.......
.PivotFields("tblCustomer.CustCode ").Orientation = xlColumnField
.PivotFields("tblAddressState.StateName").Orientation = xlRowField
.PivotFields("tblCustomer.CompRegNo").Orientation = xlDataField
........

Can you helping debugging above VBA Marco. Thank you.


If you can provide any web site relate Excel Pivot Table with VBA and SQL there is very thanl you so much.

Best Regards,
Hoo
URL