Extracting Excel Data into New Access Tables

  • Thread starter Thread starter jeff
  • Start date Start date
J

jeff

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 said:
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 said:
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
 
Back
Top