Pivot Table from MS Access Database
 

Hello -
This is probably an SQL question. I would like to make the code below (from
J-Walch book) more useful for my needs. The code creates a pivot table by
querying an access database. The change I would like to implement is that I
do not want the whole content of the table brought into excel, but just
selected data belonging to a specific field identified by a variable. This
can be accomplished by changing the * in the code below to a variable (for
instance the value of sheet(2).Range("A1")) How can I do that?

Below is the code. Where you find the * is where I would like to enter the
variable.

Thank you!

Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable

On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)


DBFile = ThisWorkbook.Path & "\Mydata.mdb"


ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile


QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\MYDATA`.MyTable MyTable"


With PTCache
.Connection = ConString
.CommandText = QueryString
End With


Worksheets.Add
ActiveSheet.Name = "PivotSheet"


Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1") , _
TableName:="Payroll for Kreg Project")


With PT

.PivotFields("Dst Acct Unit").Orientation = xlRowField
.PivotFields("Distribution Amount").Orientation = xlDataField

End With
End Sub

Pivot Table from MS Access Database
 

Try this:

QueryString = "SELECT * " _
& "FROM `" & ThisWorkbook.Path & "\MYDATA`.MyTable MyTable " _
& "WHERE MyTable.MyField = " & Sheet(2).Range("A1").Value

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Filo" wrote:

Hello -
This is probably an SQL question. I would like to make the code below (from
J-Walch book) more useful for my needs. The code creates a pivot table by
querying an access database. The change I would like to implement is that I
do not want the whole content of the table brought into excel, but just
selected data belonging to a specific field identified by a variable. This
can be accomplished by changing the * in the code below to a variable (for
instance the value of sheet(2).Range("A1")) How can I do that?

Below is the code. Where you find the * is where I would like to enter the
variable.

Thank you!

Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable

On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)


DBFile = ThisWorkbook.Path & "\Mydata.mdb"


ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile


QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\MYDATA`.MyTable MyTable"


With PTCache
.Connection = ConString
.CommandText = QueryString
End With


Worksheets.Add
ActiveSheet.Name = "PivotSheet"


Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1") , _
TableName:="Payroll for Kreg Project")


With PT

.PivotFields("Dst Acct Unit").Orientation = xlRowField
.PivotFields("Distribution Amount").Orientation = xlDataField

End With
End Sub

Pivot Table from MS Access Database
 

Awesome. Thank you!

"Ron Coderre" wrote:

Try this:

QueryString = "SELECT * " _
& "FROM `" & ThisWorkbook.Path & "\MYDATA`.MyTable MyTable " _
& "WHERE MyTable.MyField = " & Sheet(2).Range("A1").Value

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Filo" wrote:

Hello -
This is probably an SQL question. I would like to make the code below (from
J-Walch book) more useful for my needs. The code creates a pivot table by
querying an access database. The change I would like to implement is that I
do not want the whole content of the table brought into excel, but just
selected data belonging to a specific field identified by a variable. This
can be accomplished by changing the * in the code below to a variable (for
instance the value of sheet(2).Range("A1")) How can I do that?

Below is the code. Where you find the * is where I would like to enter the
variable.

Thank you!

Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable

On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)


DBFile = ThisWorkbook.Path & "\Mydata.mdb"


ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile


QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\MYDATA`.MyTable MyTable"


With PTCache
.Connection = ConString
.CommandText = QueryString
End With


Worksheets.Add
ActiveSheet.Name = "PivotSheet"


Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1") , _
TableName:="Payroll for Kreg Project")


With PT

.PivotFields("Dst Acct Unit").Orientation = xlRowField
.PivotFields("Distribution Amount").Orientation = xlDataField

End With
End Sub

Pivot Table from MS Access Database
 

You're very welcome....I'm glad I could help.


***********
Regards,
Ron

XL2002, WinXP


"Filo" wrote:

Awesome. Thank you!

"Ron Coderre" wrote:

Try this:

QueryString = "SELECT * " _
& "FROM `" & ThisWorkbook.Path & "\MYDATA`.MyTable MyTable " _
& "WHERE MyTable.MyField = " & Sheet(2).Range("A1").Value

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Filo" wrote:

Hello -
This is probably an SQL question. I would like to make the code below (from
J-Walch book) more useful for my needs. The code creates a pivot table by
querying an access database. The change I would like to implement is that I
do not want the whole content of the table brought into excel, but just
selected data belonging to a specific field identified by a variable. This
can be accomplished by changing the * in the code below to a variable (for
instance the value of sheet(2).Range("A1")) How can I do that?

Below is the code. Where you find the * is where I would like to enter the
variable.

Thank you!

Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable

On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)


DBFile = ThisWorkbook.Path & "\Mydata.mdb"


ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile


QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\MYDATA`.MyTable MyTable"


With PTCache
.Connection = ConString
.CommandText = QueryString
End With


Worksheets.Add
ActiveSheet.Name = "PivotSheet"


Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1") , _
TableName:="Payroll for Kreg Project")


With PT

.PivotFields("Dst Acct Unit").Orientation = xlRowField
.PivotFields("Distribution Amount").Orientation = xlDataField

End With
End Sub