[AccessD] Not all values paste from Excel to Access

Stuart McLachlan stuart at lexacorp.com.pg
Sun Mar 18 18:53:47 CDT 2012


Bug = Does not perform as designed/documented. (Assuming no documentation error<g>)




On 18 Mar 2012 at 18:04, William Benson wrote:

> Bug bug bug!
> 
> Nyah.
> 
> But thanks for a logical answer Stuart.
>  On Mar 18, 2012 5:06 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg> wrote:
> 
> > It's not a "bug" in Access, just the way that things work if you are using
> > Excel Copy and
> > Access Paste and  the Clipboard.
> >
> > When working within an application, the Clipboard can use "Registered
> > Formats" and
> > "Private Formats" which lets Excel store metadata about the values.
> >
> > When working between applications, the Clipboard is basically limited to
> > "Standard Formats"
> > so Access can't get that metadata and has to examine the actual text on
> > the Clipboard and
> > make assumptions about it.
> >
> > Apart from a couple of  odd formats, the clipboard is basically limited to
> > holding images and
> > chunks of text and Access can only use what Excel puts there.
> >
> > It appears that Access "guesses" the format based on the first ten rows
> > (which in your case is
> > a header and 9 numeric values) the same way that it does with Import from
> > a text file.
> >
> > --
> > Stuart
> >
> > On 18 Mar 2012 at 20:15, Benson, William (GE Global Re wrote:
> >
> > > I want to say from the start, I worked around this problem by changing
> > my Excel fields to text number format, but here goes, I still think this is
> > a bug in Access.
> > >
> > > I set up a table called Equipment Model with 3 fields. The first was an
> > autonumber, the next two were text. In Excel I had a table like this:
> > >
> > > Blank Field   Equipment Code          Equipment Model
> > >               CIGS                            85
> > >               CIGS                            90
> > >               CIGS                            130
> > >               CIGS                            135
> > >               CIGS                            140
> > >               CIGS                            145
> > >               CIGS                            150
> > >               CIGS                            155
> > >               CIGS                            160
> > >               Controller                      SunIQ
> > >               Inverter                        700kW
> > >               Inverter                        714.2kW
> > >               Inverter                        1MW
> > >               Skid (STP)                      Transformer
> > >
> > > I copied the three columns from Excel and pasted into the Access table.
> > Only the items up to and including the Equipment Model = 160 record got
> > pasted.
> > >
> > > If it was a numerical field, I could understand it, but it is a text
> > field. And it is a really small number of records, so I can hardly picture
> > Access getting confused.
> > >
> > > So I went back to Excel and checked NumberFormat - it was General. (At
> > this point I am thinking "So what, there are some text entries and besides,
> > the field is set up as text in Access").
> > >
> > > Anyway, I changed number format in Excel to Text, and retried. They all
> > pasted that time.
> > >
> > > I put this down as learning to deal with Microsoft's craziness, but I am
> > happy for someone to point out any improper expectation on my part as a
> > user/developer!
> > >
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 




More information about the AccessD mailing list