Pasting Data into a Field

[IGinc]

I am creating a database of Countries, and now need to create fields for the states/provinces, as this data is easily found in lists, what steps do I take to paste into the correct field, I receive an error 'The Text is too long to be edited' when a direct paste. Not familiar with access or vba a simple solution for a newbie or documented way is most appreciated, thanks.

 

[Trevor G]

Find the list on a website that you want to use, copy it into Excel so you can play around with the formats and data, then you can copy the data into an Access database without considering which table it needs to go into as it will create a table. That is one method.

Another is to do the same but rather than copy into Access you can use a macro in the Database to TransferSpreadsheet into a table if you have it already designed.

Another method again is to get the data into Excel and format save etc, then in your database you can import it and use the guide to determing the table, and field names etc to use.

Can I suggest you remove the word Beta from your signature block as 2010 has been launched and it has gone beyound a Beta version. Sorry to raise that with you.

 

[IGinc]

thanks will try....no longer use Beta - do need to update my sig...

 

[Trevor G]

Good luck and post back if you have any problems.

 

[IGinc]

In Access, I have a table created and designed, as it has other fields, I have tried a copy and paste from excel by pasting into the first row of the field and get an error 'the text is too long to be edited' again. Though I hit ctrl V by accident while waiting for an answer, and it pasted into the field, however, it wasnt in the right field, and I'm not sure why it worked that time.

How would I do a macro of TransferSpreadsheet? That sounds as the perfect solution. Thanks in advance.

 

[Trevor G]

OK what you need to do first in the design of your table make sure the text fields are set to the largest amount available which is 255 characters, if when you try the import and it fails again you might need to change them to a memo field which allows more than 255 characters.

Build your macro.

Open the Database on the Create Tab look to the Right and Select Macro.
In the Macro select to show all the Actions near the top as from 2007 they restricted the Initial list.

Then select the Actions and find TransferSpreadsheet this will then open up all the arguments below, so you would work you way down the list.

Import is there by defualt
Excel Workbook there by defualt
Table Name = Add your table name here
File Name = Complete path to the workbook, its name and file extension Place " quotes around this part"
Has Field names = Yes
Shouldn't have to worry about the range.

Save the macro and then run it to see if it imports correctly.

 

[IGinc]

Trevor Thanks for your continued help, I do not know how to build macros though. I have no knowledge of VBA.

 

[IGinc]

I see - going to try. oopsie.

 

[IGinc]

I have been unable to solve this issue. Using access 2003, as I need to to have an .mdb format

 

[Trevor G]

If you are using Access 2003 then in the database window select Macros and New then follow my instructions.