Hi there,

I'm frustrating myself within a VB application using DAO.

I've got a remote DB2 cube database and I want to create run-time filtered local copies of tables, within an MDB.

A SELECT INTO statement formed as the following:

SELECT [Field1],[Field2],[Field3],[Field4],[Value]
INTO [MyTable]
IN "My.mdb"
FROM DB2_Table
WHERE NOT Value IS NULL AND [Field1] = 241 AND [Field2] = 107

..corectly creates the table and copies the data, but for some reason Jet creates the "Value" field with adSingle precision in the MDB, although the source "Value" field is a DB2 DOUBLE (adDouble). The other fields are all DB2 INTEGERs, ending up correctly as adInteger in the destination table.

Is there any reason why the SELECT INTO is causing this degradation? Is there any DAO setting to sort this out?

I tried the following work-around:

SELECT [Field1],[Field2],[Field3],[Field4],CDBL(Value) As Value
INTO [MyTable]
IN "My.mdb"
FROM DB2_Table
WHERE NOT Value IS NULL AND [Field1] = 101 AND [Field2] = 102

..but then I get a circular-reference error if "Value" is used within the WHERE clause (as it is above) - the destination field is correctly created as adDouble, however.

Obviously, simply doing:

SELECT [Field1],[Field2],[Field3],[Field4],CDBL(Value)
INTO [MyTable]
IN "My.mdb"
FROM DB2_Table
WHERE NOT Value IS NULL AND [Field1] = 101 AND [Field2] = 102

..creates the "Value" field with an arbitratry field name (Expr003, et al), which is no good. Since I'm using DAO column renames aren't possible - I really want to avoid having to copy the data using a temporary name for the field in the SELECT INTO statement, then running other statements to copy the data into a new field, deleting the temporary one... ick!


What combination of CREATE TABLE or other commands can I use to copy between databases like SELECT INTO that might not have this problem - or is there another way to stop Jet incorrectly typing the field?

Thanks a lot in advance for any help, I need to get this sorted ASAP!!