Connect Access 2016 to dbf file with a name over 8 characters

Hi all,

I am trying to link data between a dBASE file (.dbf) and MS Access. The good news is that it is doable for Office 365 subscriptions, but the problem is that unless .dbf file's name is over 8 characters I get an error (The Microsoft Access database engine could not find the object ...).

For the record here is what I tried:

  • External data --> More --> dBASE file --> Browsed to the file i want
  • The file i want is on my local drive so this cannot be caused by a network access restriction 
  • Renaming the file to less than 8 characters works (Access links the data to a table), which is why I concluded that the cause of the problem is the file name and not something else

Problem is that the .dbf file is generated by another application and I cannot change its name as the other application becomes unusable if I do so.

So is there a way to link a .dbf to Access if its name is over 8 characters?

Thanks,

George

 

Question Info


Last updated November 21, 2018 Views 679 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hi George,

 

Based on our test. It is an expected behavior when you import a dbf file by using External data in the Access 2016. We welcome and encourage you to share your ideas through UserVoice. Understanding your experience helps us to make our product and service better for you and others.

 

Thanks for your understanding.

 

Tisky

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank you Tisky. I believe this shouldn't be a problem to resolve and hopefully Microsoft will get around this. I posted in uservoice as you suggested here:

https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/19181272-allow-access-to-connect-to-a-dbase-dbf-file-wit

To potential readers: If you are experiencing a similar problem please vote for my suggestion, so that it attracts attention.

In the meanwhile if anyone has a suggestion as to how to bypass this known problem please let us know.

Thanks,

George

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I haven't used dBase in years, but if it is okay with the filename then Access is at fault.  Now if the file is created by a 3rd party software and isn't respecting dBase file naming conventions, then it is the 3rd party software that is at fault.

I've asked other MVPs to tell me if this is the typical (as in this was the case in Access 2003, 2007 ...) behavior or not.  Once i hear back i will be forwarding the issue to the Access Dev team.  To me, this is a bug, or a problem to be fixed, definitely not "expected behavior".

--
Daniel Pineault
Microsoft MVP 2010-2018

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I'm not surprised by this behavior, because dBase originated in the DOS world. However its something that should have been caught. The best course of action is to bring this up on UserVoice.
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Here's a workaround for you.

You could create a form to perform the import, bring up the file dialog so you can pick the file and then with some simple code copy the file locally rename it so Access doesn't choke on it and then import or link it.  Not ideal but still pretty straightforward.

Also, from very brief research, this appears to be a long standing issue related to Jet not supporting long filename formats.

What database format are you using?  Mdb, accdb?

I'd hope that limitation would have been lifted with ACE, but God only knows.  Your answer to the above may answer that altogether.

--
Daniel Pineault
Microsoft MVP 2010-2018

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

As far as I know, this has always been a "feature" of linking to .dbf files going back to at least the 2000 version of Access.

I haven't had to use .dbf files for years, but it was an issue back when I did. I am not at all surprised that it has not changed. I would be surprised if it were to be changed now. On the other hand, MS did bring back the ability to link to them, so perhaps this would be the next step.

With Joy Wend Your Way

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Daniel,

Thank you for your suggestions. I definitely agree that this should not be "expected behavior". The only way I can explain it is that this is a really old problem and probably Microsoft wasn't too concerned about it as they intended to stop dbf support, which they did. And since they just restored .dbf support they didn't really make any improvements on it since the day it was dropped. So maybe we can justify this as "expected behavior", if something is actually done about it in the near future.

As far as my database format it is accdb. Just to give you a sense of my (lack of) experience though this is literally the first thing I tried to do with Access and I had to Google Mdb and accdb (as well as ACE). Seeing that accdb is the latest version I assume we are out of luck on ACE replacing Jet and resolving the problem :(.

Which brings us to your workaround. First i need to specify that the .dbf file and Access file need to have a dynamic relationship. I want no manual in between steps. Whenever the dbf file updates Access should update automatically. If I need to manually run a command to each time for Access to update your suggestion is in my case unacceptable and not less than ideal. Sorry if that sounds harsh, I am not being ungrateful. It's just that this doesn't suit my purposes and English is not my native language so I may come out a bit strong. If I misunderstood your comment and a true dynamic relationship can be created please provide a few more details so that I can better understand your proposed solution. 

If though in between steps are ok for other users though or a simple one off import is involved, you can also open the dbf file with excel and save it as excel. You can then import or link to that excel file. Needless to say you need to manually open the  dbf file with excel and replace the excel file you created each time the dbf file changes to have a dynamic relationship between Access and dbf. I don't know which of the two workarounds would work best, as they both sound more or less the same to me.

Sorry for the long post.

Regards,

George

Reg

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

The behavior under discussion here -- i.e. requiring the 8.3 naming convention for .dbf files in order to link to them from Access -- goes back to the very beginning of Access, or at least as far back as the time when the ability to link from mdbs to external files was first introduced back in the 1990s. I did a quick Bingoogle search but turned up nothing of use on that topic. I do know from personal experience, though, that this has ALWAYS been the case with mdbs and accdbs even when the .dbf format was much more prominent than it is now.

In other words, there has never been any other behavior for .dbf files. Therefore, it has to be considered "expected" behavior, in the sense that it has always been this way and no one expected it to be different.

Your particular application may require you to adapt your business processes accordingly. Daniel has offered a suggestion about how to do that, but you may feel the need to adopt a different, more robust approach.

For one thing, is it possible to simply adopt a naming convention that produces the 8.3 format required? Are you consuming .dbf files from another source over which you have no control? Or can you get the files you need in a usable format from the beginning?

With Joy Wend Your Way

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi George,

Thank you for your insights. As I said I appreciate Daniel's comments, but I need a more robust solution.

I have absolutely no control over the .dbf format or its name. Even if I did though I would still search for a solution as I saw such questions in the past without a solution posted by other users. So it would be nice to have a solution for this.

What I found out when googling 8.3 naming convention is that a short filename also exists. I used command prompt and dir /x which shows both long and short filenames. And I found out that my file also has a SFN of 8 characters. I will see if I can use the SFN instead of the LFN and achieve my objective. Will post back when I do so (probably tomorrow).

Best,

George

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

It works!

Linking the .dbf file to Access using the SFN creates a link between the Access table and the .dbf file.

To get the SFN I used command prompt, navigated to the folder the .dbf file is located and searched using dir *.dbf /x, so that only dbf files are searched for and both the LFN and the SFN are displayed. If anyone knows a better/ more efficient way to get the SFN please let us know.

Regards,

George

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.