Linked mdb tables in A2007

[derek]

I read that it is possible to link mdb tables as a back-end to A2007.
Do these have to be called *.mdb? Previously we could use any extension we
wanted. Now it seems A2007 uses the name to determine the format. Is this
true?

-Derek

 

[Allen Browne]

No, that's not correct.

The dialog for linking does not show file extensions other than those
registered for Access databases (accdb, mdb, etc.)

However if you type the name (including full path) into the File Open
dialog, or into the text box on the import wizard, you can still link to
files with non-registered extensions, as in previous versions.

 

[boblarson]

You can link:

An ACCDB to ACCDB
An ACCDB to MDB (any version)
An MDB to MDB (linked mdb must be same version or earlier)
An MDB to ACCDB (mdb needs to be Access 2007 mdb format as an earlier format
can't link to a newer format).
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[boblarson]

Sorry, misunderstood the question.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[derek]

Hi Allen --

Well it's simple enough to make a couple of API calls to add the different
extension as the default in a standard file-open dialog, so that part's OK.

So if I want to link to an Access table named (for example) MyTable.DAT, how
would A2007 determine if this was an mdb format table or an accdb format
table?

cheers, Derek.

 

[Allen Browne]

Just try it, Derek.

Use error handling in case it's not a file format Access recognises.

 

[derek]

Allen

I did try it last weekend, and it didn't work. That's what prompted me to
post the original question. If Access is asked to link to a file with an
extension of .xyz, does it somehow intelligently test the file to determine
the format, or does it make assumptions based on the extension? If the
former -- it doesn't seem to be working (see original post). If the
latter -- then logically only "mdb" or "accdb" or "dbf" or "txt" etc. would
be meaningful.

Cheers
Derek

 

[Allen Browne]

Derek, I can't tell you how Access works under the hood.

I can tell you that it is possible to link tables from an Access database
that has the wrong/unregistered extension. The example I tried used the
extension .wasmdb, and Access 2007 successfully linked the table (using the
External Data tab of the ribbon), and subsequently read the data.

That answers the question you asked. It will not be the same for all
scenarios. For example, when Access 97 was first relesed, this line worked
fine:
DoCmd.TransferText acExportDelim,,"Table1", "C:\test.prn"
However, Microsoft crippled that in one of the A97 service packs. Even
though *TransferText* is explicit enough, the unregistered extension prn
subsequently failed with a very cryptic and unhelpful error message
(something about being read-only.)

So, whatever you are trying to do, there is no guarantee it will work in the
future if you don't stick with the registered file types. But at present, it
does work.

 

[derek]

Allen

I really appreciate all the time you have taken to try and help me, and all
the information you have given. Thanks.

Derek