Hi gurus (sorry for the cross post...wanting to hit the best advice),

I am using some sql statements to pull data from Excel into new Access
tables as per the following example:-

mQry = "SELECT * INTO TempImportDataCL FROM [Excel
8.0;HDR=Yes;DATABASE=C:\Project\CableListData.xls].[CableList$];"
mDb.Execute mQry, dbFailOnError

Which works fine BUT columns which have only numbers in them are being
created as DataType NUMBER whereas in Excel the column is defined as TEXT. I
actually want the Access table Fields to be all TEXT datatype. Can this be
done using the above?

I am planning on changing the resultant NUMBER fields to TEXT fields after
creating the table/data but dont want to hit any snags.

Any help will be appreciated

cheers
Jeff

Hi,
try to create TempImportDataCL with required field types and then use Insert
Into query

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

"jeff" <jeff@work> wrote in message
news:(E-Mail Removed)...
> Hi gurus (sorry for the cross post...wanting to hit the best advice),
>
> I am using some sql statements to pull data from Excel into new Access
> tables as per the following example:-
>
> mQry = "SELECT * INTO TempImportDataCL FROM [Excel
> 8.0;HDR=Yes;DATABASE=C:\Project\CableListData.xls].[CableList$];"
> mDb.Execute mQry, dbFailOnError
>
> Which works fine BUT columns which have only numbers in them are being
> created as DataType NUMBER whereas in Excel the column is defined as TEXT.
> I actually want the Access table Fields to be all TEXT datatype. Can this
> be done using the above?
>
> I am planning on changing the resultant NUMBER fields to TEXT fields after
> creating the table/data but dont want to hit any snags.
>
> Any help will be appreciated
>
> cheers
> Jeff
>

Alex

Thanks for the reply, unfortunately I got re-assigned temporarily to another
job and only now got back to this one and read your comment.

I dont know the columns at the time the data is imported from Excel, so if I
have to create the table manually instead of the leaving it to the Make
table query, then I guess i would have to retrieve a NULL recordset and run
thru the excel recordset field definitions creating the internal table/field
definitions? I presume this will work...I will give it a go!

cheers
Jeff


"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi,
> try to create TempImportDataCL with required field types and then use
> Insert Into query
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
>
> "jeff" <jeff@work> wrote in message
> news:(E-Mail Removed)...
>> Hi gurus (sorry for the cross post...wanting to hit the best advice),
>>
>> I am using some sql statements to pull data from Excel into new Access
>> tables as per the following example:-
>>
>> mQry = "SELECT * INTO TempImportDataCL FROM [Excel
>> 8.0;HDR=Yes;DATABASE=C:\Project\CableListData.xls].[CableList$];"
>> mDb.Execute mQry, dbFailOnError
>>
>> Which works fine BUT columns which have only numbers in them are being
>> created as DataType NUMBER whereas in Excel the column is defined as
>> TEXT. I actually want the Access table Fields to be all TEXT datatype.
>> Can this be done using the above?
>>
>> I am planning on changing the resultant NUMBER fields to TEXT fields
>> after creating the table/data but dont want to hit any snags.
>>
>> Any help will be appreciated
>>
>> cheers
>> Jeff
>>
>