We help IT Professionals succeed at work.

The Microsoft Jet database engine could not find the object ".

rsunga
rsunga asked
on
2,040 Views
Last Modified: 2012-06-27
Hi all,

I'm new to importing in MS Access and need some assistance. I have create a brand new DB and called it TESTME. I have a .TXT file with 121 rows of data. I need to section it out using FIXED LENGTH options because the data needs to be separated in a certain fashion. I know the data types that need to be applied to each field. Here is a sample of my data:
                     
2111111111122006020914572107492426                200505261111005992420060110NA        0000000225    5    1201920   NA                          
2111111111122006020914572109559636                200506281111004682320060110NA        0000000151    8    2501800   NA                          
2111111111122006020914572111049445                200511182111001332320060106NA        0000000336    6    3300480   NA                          
2111111111122006020914572112729192                200512151111003552420060109NA        0000000156    8    1501000   NA                          
2111111111122006020914572112846809                200510281191001103120060201NA        0000000447    7    4400990   NA                          
2111111111122006020914572113140329                200509141111002352320060111NA        0000000225    5    1200780   NA                          
2111111111122006020914572113792065                200509141111002202320060110NA        0000000225    5    1200800   NA                          
2111111111122006020914572114407646                200509091111001362420060105NA        0000000255    8    2500420   NA                          
2111111111122006020914572115314179                200509211111001502420060119NA        00000002225   5    1200800   NA

I am using data types: integer, date/time, text for certain ranges of data. After I go through all the steps in the Import Text Wizard, I click the FINISH button and an error window pops up saying "The Microsoft Jet database engine could not find the object ". Make sure the object exists and that you spell its name and the path name correctly. An error occurred trying to import file 'C:test.txt'. The file was not imported.

I'm not using VBA or macros and just trying to do it manually. It seems simple enough but I know there is something that I'm missing. Can anyone help?
Thanks in advance.
Comment
Watch Question

Commented:
1. Make sure you are importing into a valid table
2. try importing everything as text first to get past this first difficulty.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
following along with Nic ... before running the udate query you can/should check the record len ...
you can do this by opening the file directly with I/O or run a loop through the original import table

Author

Commented:
Stevbe,

In my database, I do not have a valid table or any tables for that matter. The reason being is because I was trying to import the .TXT file directly into a table after the Wizard was complete. Am I missing something here? Can you go step by step for me please.

Commented:
Just import the table manually and with "fixed width", but remove (drag and drop) all field separators to get just one field.

Nic;o)

Author

Commented:
Ok Nico, lemme try that. I'll let you know my results.

Author

Commented:
Nico,

I tried importing the file with just 1 field specified. I got the same error message as above. This is exactly what I did:
1) Right-clicked in open area in my TESTME database.
2) Selected IMPORT option in menu.
3) Selected my .TXT file to import.
4) Chose FIXED WIDTH for radio button.
5) Removed lines with arrows to NOT signify field breaks (i.e. FIELD 1 only).
6) In New table.
7) Field options: Field1, Text, No (Indexed)
8) No primary key.
9) Import to table: Test

and it still gave me the Microsoft jet engine error listed above. Could it be my data?

rsunga

Commented:
Yep, looks like there's some strange character(s) in the dataset.
Try to open the file with notepad and pay special attention to the final line to hold strange characters.
Some might show like small squares.

Nic;o)
Most Valuable Expert 2014

Commented:
What version of Access are you using? It seems Acc2K had/has problems with importing/exporting non-native data.

http://support.microsoft.com/default.aspx?kbid=241471
http://support.microsoft.com/kb/209924/en-us
http://support.microsoft.com/kb/260308/en-us

Author

Commented:
Nico,
The final characters in the row are 100+blanks. But in some of the data there are decimals for numbers but no strange characters like $, ^, &.

Jimpen,
I am using Microsoft Access 2002 SP3.

Commented:
is there somewhere you can post the file so we can take a look at it?

Author

Commented:
Stevbe,
I don't have anywhere I can post it. sorry.

Author

Commented:
Would it help if I just post 10 full rows of data here and see if you can duplicate the same error or get it to import successfully? I'll just post it here.

2111111111122006020914572091023401                200506281111001172320060110NA        0000000151    8    2501800   NA   22                                                                                                                                                                                                                                                                
2111111111122006020914572107492426                200505261111005992420060110NA        0000000225    5    1201920   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572109559636                200506281111004682320060110NA        0000000151    8    2501800   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572111049445                200511182111001332320060106NA        0000000336    6    3300480   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572112729192                200512151111003552420060109NA        0000000156    8    1501000   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572112846809                200510281191001103120060201NA        0000000447    7    4400990   NA   22                                                                                                                                                                                                                                                                
2111111111122006020914572113140329                200509141111002352320060111NA        0000000225    5    1200780   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572113792065                200509141111002202320060110NA        0000000225    5    1200800   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572114407646                200509091111001362420060105NA        0000000255    8    2500420   NA   21                                                                                                                                                                                                                                                                
2111111111122006020914572115314179                200509211111001502420060119NA        00000002225   5    1200800   NA   21                                                                                                                                                                                                                                                                

If you can copy/paste this amount of data in a .TXT file in Notepad and try to import the data into MS Access and see if you get the same error as I have listed.
Thanks again for your speedy responses and assistance.
Most Valuable Expert 2014

Commented:
I tried it and had no problem.  There could be bad characters in your text file that get dropped when you post it to the web.

Commented:
yup ... when I tried as Fixed width I got the same error but when I told it to import delimited and chnage the delimiter to |  (pipe keym shit + back slash) Access wanted to make it a Memo field, I said no to setting primary key and it worked fine. This follows Nic's suggestion.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Hmm, looks like your data is over 121 characters, 379 to be precise.
This will cause an overflow in a textfield with a max of 255 characters.

Just use the import and use now two fields, one to end a few (some 20) characters behind the last "NA   21".

Nic;o)

Commented:
ack ... I need to slow down ...

<pipe keym shit + back slash)>

apologies :-)

Author

Commented:
Thanks again for the quick responses. I have tried myself using Let Access assign primary key and it worked but the results I get are not what I want. It seems when I use FIXED WIDTH and assign certain data types and field names in the FIELD OPTIONS it doesn't want to work. I know I can manipulate the date in order to get to where I want to be but it seems I should be able to use wizards/tools to not do the process manually.

But again, I'm trying to specifically break up the data in meaningful chunks. For instance,
first 3 columns, integer
next 3 columns, integer
next 3 columns, integer
next 3 columns, integer
next 12 columns, date/time
next 3 columns, integer
next 3 columns, text
etc.

I'm not sure if that is why it's not importing correctly.

Commented:
When you have the two fields (the second being dummy), then you can use the MID() function as described to select the ranges needed. Also for the date I've added a sample to get it correctly entered into a target table.
Just define the target table and then create a query based on the import table with the fields in the graphical query editor like:
Code1:mid(field1,1,12)
Date1:mid(13,4) & "/" & mid(18,2) & "/" mid(20,2)
etc.
Then change the query type into an append queryand Acces wil ask for the target table.
Now in an additional line under the fields you can select the target fields.

Nic;o)

Author

Commented:
Thanks for the input. I thought they was an easier way to get the fields more defined by using an option or tool in Access.

Commented:
The import should provide that, but when the data in the first 8 rows isn't consistent with the remaining rows Access will crash. Thus I often use code to extract the by default imported text field(s) into a target table.
Other options you could try are to process the file by using the fileobject to read the data line by line and/or to link a table to the textfile.

Sorry for the bad news. Perhaps it gets better when we all switch to using XML :-)

Success with your application !

Nic;o)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.