Results 1 to 3 of 3
  1. #1
    scattered is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    1

    VBA MS-Access set dtype of imported excel using existing table


    Hi

    I am writing a VBA script to auto update an Access DataBase. I can not used linked tables for this and need to do it all through a mixture of VBA and jet SQL. I have written the script that imports and update the table and I am now testing it. It works OK unless there is a dtype mismatch and so I need to add a some code to set the dtypes. this is the way I was going to do it and I was hoping to get advice on whether or not this is the best way or not before I start. I got to this point through research, not sure if its a silly way or not. I am normally a Python Programmer and have only been using VBA a few months.

    1. Create a collection that converts Access dtypes to SQL query e.g. 202, adVarWChar: TEXT()
    2. Create a collection from original table that to get dtype e.g column1: 202
    3. Cross reference collections 1&2 to get the column dtype in SQL e.g. column1: TEXT()
    4. Use this collection in 3 to update the column dtype using SQL

    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,873
    you should know the datatypes in advance, then you would just run a query (or transfer) to append the data.
    why would the datatype not match?

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,598
    Quote Originally Posted by scattered View Post
    I have written the script that imports and update the table
    Where is the data coming from? Excel workbook, Text/CSV, ???

    Maybe you would post the script/code/SQL??
    And maybe some records of the input data and the output (table design) wanted...

    Or post your dB??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update existing table in Access from Excel using VBA
    By rscott1989 in forum Import/Export Data
    Replies: 3
    Last Post: 11-25-2015, 01:20 PM
  2. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  3. Replies: 6
    Last Post: 10-09-2014, 12:41 PM
  4. Import Excel data into existing Access Table
    By octsim in forum Import/Export Data
    Replies: 4
    Last Post: 10-24-2013, 07:21 AM
  5. Linking Access Table with already imported External Data (Excel)
    By izzarshah in forum Import/Export Data
    Replies: 1
    Last Post: 07-29-2010, 09:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums