Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: Access and ActiveX scripts

    Is there a way to manipulate the Access Object Model via ActiveX script. What I am trying to do is create a DTS package that uses a ActiveX script to manipulate a Access MDB to import and Excel file into the Access MDB and then in the end export the Access table data to a SQL Server Table.

    Any ideas?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a connection to both the excel file and the access database.

    Then join the two with a Transform Data Task; map your columns to the table to get the data in there.

    In fact, why on earth are you using Access as a middle-man; can't you simply put the data into SQL Server?

    Meh, moved to SQL Server forum
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm quite curious now, why the "digital two-step" in this process (the business of Excel -> MS-Access -> MS-SQL) ? I'm sure that there have to be a number of business considerations in there somewhere, but I can't for the life of me imagine them.

    Yes, there are multiple ways to do what you've described so far, but without more background I have no way to devine which of them is best suited to your needs.

    You refer to DTS and the Transform Data Task, so I assume that you're using SQL 2000. Is that assumption correct?

    BTW, Tastefully Simple is wonderful stuff in my book!

    -PatP

  4. #4
    Join Date
    Feb 2008
    Posts
    3
    The issue is the vendor supplied file is a .CS file (not .CSV) and the only application we have found to open it up was excel. But if I just open it in Excel then I get a formatting error but then it does save. If I try to go directly after it with a transformation in my DTS package then I get a formatting error again. But Access pulls it in just fine and then the transformation works.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is the format of the file; is it structured the same as a CSV file?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2008
    Posts
    3
    When you open the file up in notepad you get the following (this is not the complete file:
    . Â^  TRACK_NO C  PKGID C  STATUS C  CARRIER C
    ACT_WGT N
     SHP_WGT N  SHP_CHG N
     TRC_CHG N  COD_CHG N  INS_CHG N  PIP_CHG N  DCR_CHG N  DCS_CHG N  AH_CHG N  HAZ_CHG N  SAT_CHG N  HND_CHG N  RES_CHG N  POD_CHG N  AOD_CHG N  AKHI_CHG N  REG_CHG N  CERT_CHG N  RTN_CHG N  RDEL_CHG N  SDEL_CHG N  SHND_CHG N  PAL_CHG N  VHND_CHG N  SUR_CHG N  VSUR_CHG N  TOT_CHG N
     OACT_WGT N
     OSHP_WGT N  OSHP_CHG N
     OTRC_CHG N  OCOD_CHG N  OINS_CHG N  OPIP_CHG N  ODCR_CHG N  ODCS_CHG N  OAH_CHG N  OHAZ_CHG N  OSAT_CHG N  OHND_CHG N  ORES_CHG N  OPOD_CHG N  OAOD_CHG N  OAKHI_CHG N  OREG_CHG N  OCERT_CHG N  ORTN_CHG N  ORDEL_CHG N  OSDEL_CHG N  OSHND_CHG N  OPAL_CHG N  OVHND_CHG N  OSUR_CHG N  OVSUR_CHG N  OTOT_CHG N
     ORD_CNT N  EXP_CNT N  TRC_FLAG C  COD_FLAG C  INS_FLAG C  PIP_FLAG C  DCR_FLAG C  DCS_FLAG C  AH_FLAG C  OS_FLAG C  DIM_FLAG C  HAZ_FLAG C  SAT_FLAG C  HND_FLAG C  CT_FLAG C  HOLD_FLAG C  POD_FLAG C  AOD_FLAG C  RES_FLAG C  ICE_FLAG C  AKHI_FLAG C  PAY_FLAG C  REG_FLAG C  CERT_FLAG C  RTN_FLAG C  RDEL_FLAG C  SDEL_FLAG C  SHND_FLAG C  PAL_FLAG C  DIRECT C  COD_AMT N
     INS_AMT N
     REG_AMT N  PIP_AMT N
     PIP_UNIT N  PIP_PCT N  PIP_CARR N  DIM_LENGTH N  DIM_WIDTH N  DIM_HEIGHT N  DIM_FACTOR N  ACCOUNT N  INTCODE C  DATE D  TIME C  NUMBER C  TRANS N  ZONE C  QTY N  UNIT N
     DEPT C
    PICK C
    PACK C
    OPER C
    PROD C
    DESC C  PO_NO C
    REMARK C  CARNAME C  PHONE C  CUST C  SHIPTO1 C  SHIPTO2 C  SHIPTO3 C  SHIPTO4 C  SHIPTO5 C  SHIPTO6 C  SHIPTO7 C  ZIP C
    COUNTRY C  PAY_ACCT C  BILLTO1 C  BILLTO2 C  BILLTO3 C  BILLTO4 C  BILLTO5 C  BILLTO6 C  BILLTO7 C  CLASS C  ARRIVE C  ROUTE C  REGION C  METRO C  BUNDLE C  TRACK_MOM C  REF C  USERNAME C
    BAXS_APT C  BAXH_APT C  BAX_EXP C  BAX_SUR C  BAX_BYN C  BAX_STA C  BAX_HUB C  BAX_TRK C  CVD_CHG N  CVP_CHG N  DLC_CHG N  DRP_CHG N  FSC_CHG N  HED_CHG N  HEP_CHG N  HOLD_CHG N  ICE_CHG N  IND_CHG N  INP_CHG N  LGD_CHG N  LGP_CHG N  PUC_CHG N  RSC_CHG N  RSD_CHG N  RSP_CHG N  SDL_CHG N  SPU_CHG N  SSS_CHG N  AKS_CHG N  HIS_CHG N  PRS_CHG N  OCVD_CHG N  OCVP_CHG N  ODLC_CHG N  ODRP_CHG N  OFSC_CHG N  OHED_CHG N  OHEP_CHG N  OHOLD_CHG N  OICE_CHG N  OIND_CHG N  OINP_CHG N  OLGD_CHG N  OLGP_CHG N  OPUC_CHG N  ORSC_CHG N  ORSD_CHG N  ORSP_CHG N  OSDL_CHG N  OSPU_CHG N  OSSS_CHG N  OAKS_CHG N  OHIS_CHG N  OPRS_CHG N  CVD_FLAG C  CVP_FLAG C  DLC_FLAG C  DRP_FLAG C  FSC_FLAG C  HED_FLAG C  HEP_FLAG C  IND_FLAG C  INP_FLAG C  LGD_FLAG C  LGP_FLAG C  PUC_FLAG C  RSC_FLAG C  RSD_FLAG C  RSP_FLAG C  SDL_FLAG C  SPU_FLAG C  SSS_FLAG C  AKS_FLAG C  HIS_FLAG C  PRS_FLAG C  AOG_FLAG C  DTCS_FLAG C  FOOD_FLAG C  GOVT_FLAG C  PERI_FLAG C  SPD_FLAG C  SPP_FLAG C  RATE_FLAG C  DIM_WGT N
     VDC_FLAG C  VDC_CHG N
     VDC_NAME C ( VDC_PHONE C  ALTCARR1 C
    ALT_CHG1 N
     ALTCARR2 C
    ALT_CHG2 N
     ALTCARR3 C
    ALT_CHG3 N
     ALTCARR4 C
    ALT_CHG4 N
     RECORDID C  CITY C  DDP_FLAG C  DDP_CHG N  CUSTOMS C  REF1_CHG N  REF2_CHG N  SS_FLAGS C ( PRE_CHG N  OPRE_CHG N 
    1ZX557750355483327 BR U11 1.5420 2.00 7.1800 0.00 0.00 0.00 0.00000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7.1800 1.5420 2.00 7.1800 0.00 0.00 0.00 0.00000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7.1800 1 1 Y 1 N 0.00 0.00 0.00 0.00 0 0.000 0.00 0 0 0 194 1U112008020108:09:294505977096 105 0 0.0000PERSONAL UPS Ground 320.760.3807

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That realy doesn't help (me).
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •