fboehlandt
Active Member
- Joined
- Sep 9, 2008
- Messages
- 334
Hello everyone,
I have the following problem. Ii would like to import data from an access database into an excel pivottable using the connection.add method in vba. As a command string I want to implement a union query that combines the data from two tables in the original database.
When I run the code it returns the error message 'Type mismatch' on the last line of coding. Obviously, VBA doesn't recognize connectionstring and/or commandarray as variant datatype. I cannot set them to Variant either (commandarray As Variant), the error then just appears when I define the array. I'm sure this is a fairly simple problem that has just to do with my limited knowledge of VBA. Can somebody help please?
I have the following problem. Ii would like to import data from an access database into an excel pivottable using the connection.add method in vba. As a command string I want to implement a union query that combines the data from two tables in the original database.
Code:
Sub Record_dataimport()
Dim wb As Workbook
Dim commandarray
Dim connectionstring
Set wb = ThisWorkbook
'***********************************************
'This is a select / union query as command array
'***********************************************
commandarray = Array( _
"SELECT RETURNS.Code, RETURNS.Fund, ADMINISTRATIVE.Main_Strategy, IIf([ADMINISTRATIVE.Leverage]='No','No','Yes') AS Leverage, RETURNS.MM_DD_YYYY, RETURNS.Performance, 'ROI' AS [Type]" _
& "FROM `C:\path\database.accdb`.`ADMINISTRATIVE`" _
& "INNER JOIN `C:\path\database.accdb`.`RETURNS`" _
& "ON ADMINISTRATIVE.Code = RETURNS.Code" _
& "UNION" _
& "SELECT ASSETS.Code, ASSETS.Fund, ADMINISTRATIVE.Main_Strategy, IIf([ADMINISTRATIVE.Leverage]='No','No','Yes') AS Leverage, ASSETS.MM_DD_YYYY, ASSETS.Assets_Managed, 'AUM' AS [Type]" _
& "FROM `C:\path\database.accdb`.`ADMINISTRATIVE`" _
& "INNER JOIN `C:\path\database.accdb`.`ASSETS`" _
& "ON ADMINISTRATIVE.Code = ASSETS.Code")
'***********************************************
'This is the connection string to the Access database
'***********************************************
connectionstring = Array( _
"OLEDB;" _
& "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Password="""";" _
& "User ID=Admin;" _
& "Data Source=C:\path\database.accdb;" _
& "Mode=ReadWrite;" _
& "Extended Properties="""";" _
& "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
& "Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;" _
& "Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;" _
& "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" _
& "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" _
& "Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;" _
& "Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False")
'***********************************************
'Connections.Add Method -->
'Name As String, Description As String, ConnectionString As Variant,
'CommandText As Variant, lCmdtype As Variant
'***********************************************
wb.Connections.Add "HFR_ACCESS_20070115", "", connectionstring, commandarray, 3When I run the code it returns the error message 'Type mismatch' on the last line of coding. Obviously, VBA doesn't recognize connectionstring and/or commandarray as variant datatype. I cannot set them to Variant either (commandarray As Variant), the error then just appears when I define the array. I'm sure this is a fairly simple problem that has just to do with my limited knowledge of VBA. Can somebody help please?
Last edited:
