Using ADO to write to an Excel file - Type Format

So lately I've been experimenting with using ADO to read/write data to/from Excel workbooks. The recordset gets written successfully but sometimes columns I intended to store numeric have values written to them as strings (adVarChar). I want to find a method which will ensure that data gets written in the correct format.

I can only think of two ways to properly handle this (I've dismissed the possibilities of MaxScanRows and IMEX):

1. Force each field to the correct Type before writing
2. Structure the UPDATE/INSERT SQL strings so that each value is written in the correct format for that field.

Can anyone please help with either of the above?

Care to share the code you've tried so far?

Originally Posted by Norie

Care to share the code you've tried so far?

This is the first time I've used ADO so I don't know what to try. :S


Below is an extract of code (please don't laugh) where I am looking at altering the SQL string to handle different types. Not sure that it will work though.

Please Login or Register  to view this content.

Is that all the code?

There seems to be a few bits missing.

PS Why do you want to write to an Excel file anyway?

Originally Posted by Norie

Is that all the code?

There seems to be a few bits missing.

Well I did say it was an extract. I only posted enough to answer your question as to what I was trying so far to solve the thread questions...

Originally Posted by Norie

PS Why do you want to write to an Excel file anyway?

Currently just as an experiment. Curious to see if you could use an Excel workbook as a pseudo database in the absence of MS Access installed on the end users PC. So far it seems to be working. I accept that going down this route will have a lot of drawbacks. I have overcome most of these so far.


Anyway getting back to the OP, do you have any suggestions on how I can set the datatype for the fields or values written?

I've come up with a work around. It's not ideal but it will have to do for now seeing as nobody currently has a better solution.

There's a rep waiting for any forum user who can solve either of my opening questions (without using parameters - I'm trying to keep the VBA functions as flexible as possible).


Details below for how I'm working round the issue:

I've altered the VBA code so that whenever a record is added or updated and any of the values is missing, it will replace these missing values with a default value for that field. This works by forcing MaxScanRows to correctly detect the datatype for that column (this wouldn't work before due to empty cells in the column).

The disadvantages of this method is that: 1. any retrieved values need to be checked in VBA to detect and ignore the default values for each field and 2. any values to be written need to be checked in VBA for missing entries and then have a default value assigned. This is not ideal as I need to write custom functions for every read/write query I use.

Posting an 'extract' of your code doesn't really help.

It is possible to read/write database file without Access on computer. Excel has no field type so you must do a lot of work to emulate.

solve either of my opening questions (without using parameters - I'm trying to keep the VBA functions as flexible as possible).

I do not understand - parameters will make code more flexible I think.

PS What if ADO isn't available?

Originally Posted by Izandol

It is possible to read/write database file without Access on computer.

Hi Izandol. I was aware that this was possible. However is it possible to create a new access file (with set up field names etc) without having Access installed? I don't know of a way to do this.

Originally Posted by Izandol

Excel has no field type so you must do a lot of work to emulate.

I know. (I found that out after a few quick google searches). However as long as the data type is not mixed in each column (which shouldn't be done anyway) then ADO should auto determine what the data type is when reading (by MaxScanRows). Where I fell down at the start of this thread is that some of my field values were empty. (Which occasionally caused the auto-determined data type to be set to the wrong type).

Originally Posted by Izandol

I do not understand - parameters will make code more flexible I think.

The problem is that parameters are set by field order, right? So when the function receives an argument of an array of field name strings, it will blindly add parameter data types based on the field order hard-coded in the function. However I will be passing different tables to this function and field(3) from table CONTACTS will be a string data type but field(3) table TRANSACTIONS will be a numeric data type.

I refer to function parameters-you may pass array of field names and array of field types.

Originally Posted by Norie

Posting an 'extract' of your code doesn't really help.

I thought you only wanted an idea to see what I was trying so far? I'll PM you my current module but I doubt it will help.

Originally Posted by Izandol

array of field types

Hey that's an idea

Would this be used to format the table before writing a recordset? Or would you use it on the field values before doing a SQL injection?

I would not do this.

You may format field values - I am not sure what you intend by "format the table before writing a recordset"?

Originally Posted by Izandol

I would not do this.

You may format field values - I am not sure what you intend by "format the table before writing a recordset"?

Sorry my fault. ADO is all new to me. I meant "Set the Field Type Property" prior to writing a recordset?

(I know that Excel can not save Field Data Types however can the field parameters be applied to the current connection before writing the values? So that the values are written to Excel, the values are stored in the correct data type? Otherwise when I open the Excel file normally I find numeric values written as text )

Yes, you may specify field types for recordset when you create it (but you are not creating one in your code).

For your earlier question I neglected, yes I believe you may create database without Access application - certainly it is simple to create tables with ADO or DAO which is sometimes more simple for Access.

Originally Posted by Izandol

Yes, you may specify field types for recordset when you create it (but you are not creating one in your code).

I like the sound of this Could you please point me to a webpage that has an example of this?

There is simple example on StackOverflow page here: http://stackoverflow.com/questions/2...rdset-manually

Originally Posted by Izandol

There is simple example on StackOverflow page here: http://stackoverflow.com/questions/2...rdset-manually

Yes! Thanks Izandol +1

I think I can take it from here so I will mark the thread solved.