Adam,
The first thing I'd try would be to place a single quote in front of the numeric cells in Excel. This would serve to tell Excel that the cells, even though they are probably defined as text (my assumption but maybe not) with just numbers are to be defined as text. This could be done with just a few records into a copy of the production table to see if this will tell Access that the data, regardless whether alphanumeric or numeric data, is actually text data. BTW, the single quote is not visible in Excel and this is why I'd try is first as it's presence to to tell Excel that the cell contains text.
Your message doesn't say how Excel gets the data or how many records are to be added each month but if this works, a simple macro in Excel can automate the addition of the quote to eliminate the manual effort.
If the single quote doesn't work, then I'd build a macro that lets Excel add the data to the table. This is actually simple to do but one does, of course, need to know how in order to render the task simple. If this interface between Excel and Access is new to you, I'm happy to share snippets of code to help you. I'd need to have a better understanding of the table structure and the Excel data to help. Knowing this has a bearing on the choices made as adding data to an Access table one field at a time takes longer to run than doing the same using an Excel range of cells.
The worst case would be to have Excel add the data to an entirely new table and then to execute the query to move the data over to the production table. The rub with this, whether automated via Excel or done manually is the follow-up need to compress the Access database regularly to keep size in line.
Jeff
The first thing I'd try would be to place a single quote in front of the numeric cells in Excel. This would serve to tell Excel that the cells, even though they are probably defined as text (my assumption but maybe not) with just numbers are to be defined as text. This could be done with just a few records into a copy of the production table to see if this will tell Access that the data, regardless whether alphanumeric or numeric data, is actually text data. BTW, the single quote is not visible in Excel and this is why I'd try is first as it's presence to to tell Excel that the cell contains text.
Your message doesn't say how Excel gets the data or how many records are to be added each month but if this works, a simple macro in Excel can automate the addition of the quote to eliminate the manual effort.
If the single quote doesn't work, then I'd build a macro that lets Excel add the data to the table. This is actually simple to do but one does, of course, need to know how in order to render the task simple. If this interface between Excel and Access is new to you, I'm happy to share snippets of code to help you. I'd need to have a better understanding of the table structure and the Excel data to help. Knowing this has a bearing on the choices made as adding data to an Access table one field at a time takes longer to run than doing the same using an Excel range of cells.
The worst case would be to have Excel add the data to an entirely new table and then to execute the query to move the data over to the production table. The rub with this, whether automated via Excel or done manually is the follow-up need to compress the Access database regularly to keep size in line.
Jeff
-----Original Message-----
From: "wrmosca@comcast.net [MS_Access_Professionals]"
Sent: Apr 22, 2015 5:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Problem copying/importing from Excel
Adam
That might be the only way you can get it to work. Access looks at the first few rows to determine the data type. If it decides it is numeric it will error out on the alpha data.
---In MS_Access_Professionals@yahoogroups.com, <runuphillracing@yahoo.com> wrote :I'm missing data in one of the fields when copied from Excel into an existing table. The field, AcctNo, is a text field. That column in Excel contains both numbers and alphanumeric data. When I bring the data over, whether I copy and paste-append or import, only the all-numeric AcctNos come across and all of the alphanumeric fields are blank. It only works when I import to a new table, then write a query to bring it into the table I want. I don't want to do that because this is something that'll repeat every month.
Any ideas why this is happening, how to fix it?
Adam
---
Jeffrey Park Jones
919-671-9870
119 Ayersdale Dr.
Taylors, SC 29687
Jeffrey Park Jones
919-671-9870
119 Ayersdale Dr.
Taylors, SC 29687
__._,_.___
Posted by: Jeff <jpjones23@earthlink.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar