Access Import Issue 1

[psarros]

I am importing data from an Excel Worksheet into Access. Thre are 15 worksheets that I import.

When I do the import I generate some import errors because some of the data has "wierd" values. I cannot control the Excel sheet and I don't care that there are errors.

Is there a way to suppress the Import Error tables from generating?

Thanks

 

[mikemclean]

I must admit to not being able to answer the "suppress error tables" issue. I know the "Setwarning" property can be used to suppress the actual error messages.

I do want to ask how you import from multiple sheets as I have never been able to get this right. Do you import out of one Excel file, but across the 15 sheets? And do you use range names as a means of specifying where to import data from?

 

[psarros]

Here is the code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TempImport", "G:\capscreen\Russell2000.xls", False, "Auto Transportation!A5:AE300"

TempImport - is the table name
G:\capscreen\Russell2000.xls, - is the excel file name
"Auto Transportation!A5:AE300" - sheet and range


When you import data into access, if there re errors, Access will generate an import error tablewhich can ve used for debugging, in this case I don't want to generate the tables...

 

[AppStaff]

Some suggestions from experience.

1) Use a custom import specification. This allows you to define specifically how to handle the fields and eliminate problems that arise when Access attempts to define these fields for you. It really does a poor job of this for some reason. Often date fields become text and text become numeric which I assume is what your experiencing. This can be tricky if you've never done it.

Here is what access helps says on the import spec:
"An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file. An import/export specification is stored with the default name Filename_ImportSpec or Filename_ExportSpec in the database that you import to or export from.

You create an import/export specification by using either the Import Text Wizard or the Export Text Wizard. Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.

Alternatively, you can use a schema.ini file in a Microsoft Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Microsoft Windows registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini."

2) You can insert a first row in the spreadsheet you wish to import and enter test in every field being imported. This will trigger access to import everything as text which typically will reduce import errors and allow you to respecify the field types once in access with little to no conversion problems. If you have very large text fields then you may need to increase the "test" value to something much larger... not sure try it and see if it helps

 

[Spyder1000]

DoCmd.SetWarnings False

Will suppress the errors on import.

 

[psarros]

Thanks Spyder... this at least stops the warnings from poping up.

I will try to import everything as Text, and maybe that will stop the _ImportError files from generating.

AppStaff - A spec will not hep me because some of the records have - (dashes) or other erronious data in them.

Thanks

 

[AppStaff]

I'm not sure why a spec wouldnt help you. You should be able to bring dashes or other characters in as text. Defining difficult fields as text should bring anything from those fields in as long as you separate fields appropriately. With special characters in fields you need to carefully define how you delimit fields and separate text and perhaps consider fixed length if that is an option for you.

 

[psarros]

Appstaff .... the erronious data.... is erronious. I don't want it

 

[AppStaff]

Hmmm... sorry guess I don't understand your issue well enough. A spec is completely open your your specifications. Which fields to input under what types, etc. Hard for me to imagine that it wouldnt apply.

As long as you have your answer though thats all that matters. Good luck.

 

[hkartal]

How can I import a report/form from mde to mdb?

 

[AppStaff]

I don't believe you can import/export form or report objects from an MDE. Thats kind of the point of compiling it

You might want to make a new thread for that question though.