Compile Error Help

[Jakboi]

Hello,

I recorded this macro via Excel and it has a compile error anyone know why?

It runs a SQL for me, here is the section that highlights as compile error- syntax:

        .CommandText = Array( _
        "SELECT AC_BI.ACCOUNTANT, AC_BI.ACCOUNT_NAME_FIRST, AC_BI.ACCOUNT_NAME_LAST, AC_BI.ACCOUNT_NUMBER, AC_BI.ACCOUNT_TAX_TYPE, AC_BI.ACCOUNT_TAX_TYPE_TEXT, AC_BI.ACCT_SUB_TYPE, AC_BI.ADDRESS_01, AC_BI.ADDR" _
        , _
        "ESS_02, AC_BI.ADDRESS_03, AC_BI.ADDRESS_04, AC_BI.ADDRESS_05, AC_BI.ADMIN_BRANCH_NUMBER, AC_BI.ADMIN_OFFICER, AC_BI.ALTERNATE_ACCOUNT_NUMBER_01, AC_BI.ALTERNATE_ACCOUNT_NUMBER_02, AC_BI.ANNUAL_ACCT_DA" _
        , _
        "TE, AC_BI.APPOINT_DATE, AC_BI.BASIS_POINT_FEE_RATE, AC_BI.BRANCH_NUMBER, AC_BI.CASH_MGMT_BASIS_PT_FEE_RATE, AC_BI.CONSOL_FEE_TRANS_ON_CS1_TO_CS7, AC_BI.CONTROL_ACCOUNT_TYPE, AC_BI.CONTROL_ACCOUNT_TYPE" _
        , _
        "_TEXT, AC_BI.COUNTY_CODE, AC_BI.DATE_FORM_W9_RECEIVED, AC_BI.DIST_SUBJ_MA_INHERIT_TX, AC_BI.DIST_SUBJ_MA_INHERIT_TX_TEXT, AC_BI.EXCLUDE_FROM_SCHEDULE_RCT, AC_BI.FEE_REBATE_EXCLUDED_FUNDS_01, AC_BI.FEE" _
        ,,,,,,,,,)

Any Ideas?

 

[DJkarl]

Hello,

I recorded this macro via Excel and it has a compile error anyone know why?

It runs a SQL for me, here is the section that highlights as compile error- syntax:

        .CommandText = Array( _
        "SELECT AC_BI.ACCOUNTANT, AC_BI.ACCOUNT_NAME_FIRST, AC_BI.ACCOUNT_NAME_LAST, AC_BI.ACCOUNT_NUMBER, AC_BI.ACCOUNT_TAX_TYPE, AC_BI.ACCOUNT_TAX_TYPE_TEXT, AC_BI.ACCT_SUB_TYPE, AC_BI.ADDRESS_01, AC_BI.ADDR" _
        , _
        "ESS_02, AC_BI.ADDRESS_03, AC_BI.ADDRESS_04, AC_BI.ADDRESS_05, AC_BI.ADMIN_BRANCH_NUMBER, AC_BI.ADMIN_OFFICER, AC_BI.ALTERNATE_ACCOUNT_NUMBER_01, AC_BI.ALTERNATE_ACCOUNT_NUMBER_02, AC_BI.ANNUAL_ACCT_DA" _
        , _
        "TE, AC_BI.APPOINT_DATE, AC_BI.BASIS_POINT_FEE_RATE, AC_BI.BRANCH_NUMBER, AC_BI.CASH_MGMT_BASIS_PT_FEE_RATE, AC_BI.CONSOL_FEE_TRANS_ON_CS1_TO_CS7, AC_BI.CONTROL_ACCOUNT_TYPE, AC_BI.CONTROL_ACCOUNT_TYPE" _
        , _
        "_TEXT, AC_BI.COUNTY_CODE, AC_BI.DATE_FORM_W9_RECEIVED, AC_BI.DIST_SUBJ_MA_INHERIT_TX, AC_BI.DIST_SUBJ_MA_INHERIT_TX_TEXT, AC_BI.EXCLUDE_FROM_SCHEDULE_RCT, AC_BI.FEE_REBATE_EXCLUDED_FUNDS_01, AC_BI.FEE" _
        ,,,,,,,,,)

Any Ideas?

Well if you copied an Excel macro into Access that would be the first problem

But on a more serious note, the Array function returns a Variant type object, Excel considers the .CommandText property to be a variant type so there is no problem, Access considers the .CommandText property to be part of the ADO library and it needs a String passed to it, not a Variant.

 

[Jakboi]

Thanks for the reply.

Sorry I did post in the wrong forum by accident. This is for a Excel macro.

Here is the whole thing...so what should I do to fix this or what is causing this?

Sub AddVantage_SQL()
'
' AddVantage_SQL Macro
' Macro recorded 4/5/2007 by user
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=AddVantage Cache for TRP;SERVER=xxx.xxx.xxx.xxx;PORT=xxxx;DATABASE=xxxx;AUTHENTICATION METHOD=0;UID=user;;QUERY TIMEOUT=1" _
        , Destination:=ActiveCell)
        .CommandText = Array( _
        "SELECT AC_BI.ACCOUNTANT, AC_BI.ACCOUNT_NAME_FIRST, AC_BI.ACCOUNT_NAME_LAST, AC_BI.ACCOUNT_NUMBER, AC_BI.ACCOUNT_TAX_TYPE, AC_BI.ACCOUNT_TAX_TYPE_TEXT, AC_BI.ACCT_SUB_TYPE, AC_BI.ADDRESS_01, AC_BI.ADDR" _
        , _
        "ESS_02, AC_BI.ADDRESS_03, AC_BI.ADDRESS_04, AC_BI.ADDRESS_05, AC_BI.ADMIN_BRANCH_NUMBER, AC_BI.ADMIN_OFFICER, AC_BI.ALTERNATE_ACCOUNT_NUMBER_01, AC_BI.ALTERNATE_ACCOUNT_NUMBER_02, AC_BI.ANNUAL_ACCT_DA" _
        , _
        "TE, AC_BI.APPOINT_DATE, AC_BI.BASIS_POINT_FEE_RATE, AC_BI.BRANCH_NUMBER, AC_BI.CASH_MGMT_BASIS_PT_FEE_RATE, AC_BI.CONSOL_FEE_TRANS_ON_CS1_TO_CS7, AC_BI.CONTROL_ACCOUNT_TYPE, AC_BI.CONTROL_ACCOUNT_TYPE" _
        , _
        "_TEXT, AC_BI.COUNTY_CODE, AC_BI.DATE_FORM_W9_RECEIVED, AC_BI.DIST_SUBJ_MA_INHERIT_TX, AC_BI.DIST_SUBJ_MA_INHERIT_TX_TEXT, AC_BI.EXCLUDE_FROM_SCHEDULE_RCT, AC_BI.FEE_REBATE_EXCLUDED_FUNDS_01, AC_BI.FEE" _
        ,,,,,,,,,)
        .Name = "Query from AddVantage Cache for TRP"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

End Sub