I am using transfertext and transferspreads heet in VBA to import an excel file (.csv or .xls) into access 2003.
Some of the fields are failing (I am getting an import error file), but I am not getting any warning message. I have explicitly turned on the warnings, but it didn't make a difference. If I manually import the table, the warnings appear ("Microsoft Access was unable to append all the data to the table. The contents of fields in X record(s) were deleted, and Y record(s) were lost due to key violations . . . ")
Does anyone have any ideas how to get these warnings to appear?
Note that the file (stSave) is importing, with the exception of a few fields.
Here is my code: - DoCmd.SetWarnings True
-
-
If Right(importFile, 4) = ".xls" Then
-
DoCmd.TransferSpreadsheet acImport, , tblName, stSave, True
-
Else
-
DoCmd.TransferText acImportDelim, , tblName, stSave, True
-
End If
-
Thank you.
4  3892  nico5038 3,080
Recognized Expert Specialist
In general this is cause by two main "problems"
1) The data type of the column is set automatically, but Access uses just some 8 rows. So a "change" further down causes a date or number to fail :-(
Solution:
Repeat the first line with the column names. This forces Access to import everything as text and (after deleting the first row) you can set the data type properly.
2) There are fields "mandatory" containing no data. Set the fields of a table to accept Null values by allowing zero length.
Nic;o)
This process will be used by many people, and the intention is that there not be any manipulation of the excel file (which comes from a third party). So, manually adding another row is out.
I am importing into a pre-existing table, so the field types are certainly preset.
Again, if I bring in the file manually to the existing table (Tables -> new -> import -> existing table), the warnings DO appear.
None of the fields that are failing are mandatory, they all allow zero length fields.
Any other ideas?
nico5038 3,080
Recognized Expert Specialist
Try to create a new table linked to the excel sheet.
Then use an Append query to load the data.
Nic;o)
NeoPa 32,579
Recognized Expert Moderator MVP
Always use SpecificationNa me where possible. This stops Access from trying to determine the type of the field from the data.
When doing a transfer manually, you can go into advanced mode, where you can load and save Import/Export Specifications.
Welcome to Bytes!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Dave Hammond |
last post by:
When using mysqlimport, the final statistics include a count of
"Warnings", for example:
Connecting to foo.whatever.com
Selecting database foo
Loading data from LOCAL file: foo_userInfo.imp into foo_userInfo
foo.foo_userInfo: Records: 248 Deleted: 0 Skipped: 6 Warnings: 248
Disconnecting from foo.whatever.com
I'd like to know what the warnings were, however there doesn't appear
|
by: Nathan Bloom |
last post by:
Hi,
I have a procedure that transfer data from an Excel spreadsheet to an Access
2000 table. There is a start date and an end date in the range specified.
One of the date fields transfers correctly and the other one transfers as a
text field containing a number like 37917. In the spreadsheet both date
cells are formatted as a date. The data in both fields are entered as dates
(i.e. 10/27/03).
The TransferSpreadsheet Command creates...
|
by: Bob Hynes |
last post by:
Hi All,
In Access97 I'm using a TransferSpreadsheet acExport in vba code and
it errors when the spreadsheet I'm exporting does not exist. If I
create a empty spreadsheet with the name I use in the
TransferSpreadsheet statement it works.
I don't understand whats going on here, why won't TransferSpreadsheet
create the spreadsheet if did does not exist?
thanks
bobh.
|
by: barma16 |
last post by:
I've hit a bit of a brick wall here, and could use some advice. I have
an Access application whose output is a four-tab Excel spreadsheet
where three of the four tabs are the result of database queries.
A number of fields in the queries are designed to become formulae in
Excel after the export has taken place (and they need to be in Excel
rather than Access). I need to use the TransferSpreadsheet Method
rather than the OutputTo to enable...
|
by: deko |
last post by:
I use a complied query to export to Excel like this:
SELECT * INTO .
FROM tblExcelData;
But I have a situation where I need to export several tables into the same
worksheet. The idea is to have each contiguous block of data on the
worksheet separated by 50 or so rows so a graph can be inserted between each
set of data.
| | |
by: Hank |
last post by:
Hello,
I use TransferSpreadsheet on a daily basis using a Table as a
datasource. I was under the impression from reading Help that you could
also use a Query as the datasource. This is my code: (simplified here)
strSQL = "SELECT FROM ;"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, strSQL,
Path, True
|
by: prasanna |
last post by:
i will be very thankful if you sent all the errors and warnings regarding
to the language C
thank you
|
by: superprad |
last post by:
Hi I am trying to write a python wrapper for a C code I have using
swig. when i try to compile the wrap.c i get a bunch of these warnings
and errors can anyone help
$swig -python test_hk.c
this generates test_hk.py and test_hk_wrap.c
then i compile it as
$gcc -Wall -std=c99 test_hk.c test_hk_wrap.c -I/usr/include/python2.3/
|
by: tony |
last post by:
Hello!!
I use VS 2003 and C# for all class library except MeltPracCommon.dll which
is C++.NET
The problem is that I get these warnings when building the exe file and use
my class libraries.
See below for a detail description.
Preparing resources...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
