Import as memo

[Nick 'The Database Guy']

I am using access 2003.

I have to import a Excel spreadsheet in to Access, I am using the
TransferSpreadsheet command. Is there any way of forcing the columns
to be memo fields. I am getting a great deal of truncation as the
field are coming across as regular text fields at the moment.

The import has to be as invisible to the user as possible, all that
they can do is browse for the file and then hit import.

All help will be greatly appreciated.

Nick

 

[Douglas J. Steele]

Try creating the table first, and importing into it, rather than having the
TransferSpreadsheet method create it.

 

[Nick 'The Database Guy']

Try creating the table first, and importing into it, rather than having the
TransferSpreadsheet method create it.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)









- Show quoted text -

I thought of that but I want to preserve the column headings, and they
might change every time. Is there any way that I can create the table
with the correct headings, delete all the data, change all the fields
to the Memo datatype and then import my data once again?

 

[Douglas J. Steele]

I thought of that but I want to preserve the column headings, and they
might change every time. Is there any way that I can create the table
with the correct headings, delete all the data, change all the fields
to the Memo datatype and then import my data once again?

Sure. You've even outlined all of the steps required!

You should be able to change the data types using DDL:

CurrentDb().Execute "ALTER TABLE MyTable " & _
"ALTER COLUMN Field1 MEMO, " & _
"Field2 MEMO", dbFailOnError

 

[Nick 'The Database Guy']

Sure. You've even outlined all of the steps required!

You should be able to change the data types using DDL:

CurrentDb().Execute "ALTER TABLE MyTable " & _
"ALTER COLUMN Field1 MEMO, " & _
"Field2 MEMO", dbFailOnError

The only trouble that I am having now is that the query that I was
going to use to populate the new table does not 'see' the data,
whereas VBA does. Is there any way that I can, ooh, wait a minute
just talking it through led me to a solution, thanks for all your help
Doug

 

[Nick 'The Database Guy']

Sure. You've even outlined all of the steps required!

You should be able to change the data types using DDL:

CurrentDb().Execute "ALTER TABLE MyTable " & _
"ALTER COLUMN Field1 MEMO, " & _
"Field2 MEMO", dbFailOnError

One other thing, I used DAO, rather than SQL to create my new table.
Thanks all the same.