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!