Opening MDB files without Access (not known)

Topic

New Reply

Hi.

My father has an Access MDB database that he wants to open and view, but doesn’t have Access. Short of him going out and buying it (which he’d rather not as it’s just this one file that he wants), can he connect via e.g. ODBC using some free database such as MySQL?

Thanks.

Stuart

PS – I’ve suggested that he try opening it through Excel, but he claims only to have had partial luck trying this.

Reply | Quote

Replies

It is possible to access the data in the database via Excel. However, he will need to import the tables individually into Excel, unless there is a query in the database that contains the information he wants from multiple tables if applicable.

.

Reply | Quote

The attached file (Excel 2000 format spreadsheet) demonstrates one possible method for doing this by “capturing” the data in Access database from Excel using ADO in VBA to open a recordset and copying the records to into Excel. The function uses the Jet 4.0 OLEDB provider, which should be available even if Access not installed (if using Office 2K or later), as Jet is used by other applications. To test, open attached file (you’ll get standard “Macro” warning) & enter full path of Access database in cell A2. Then click button. Column B will be populated with table (and query) names (excluding system tables) then the code will loop thru each table listed and copy the records into a new worksheet. Hyperlinks to each new sheet are inserted, and a record count is added to column C. I tested this with the “Northwind” sample database & worked OK, with one exception: The Excel CopyFromRecordset function apparently doesn’t like binary/OLE datatypes as found in a few Northwind tables to (clutzily) store images, etc. so these tables were not copied properly. If your db doesn’t use this data type should be no problem. Must enter a valid path in A2. If any of the tables have more than 65536 records will encounter “issues” due to Excel row limitations. Caveat: None of my PC’s do NOT have Access installed so cannot state with total certitude this will work on system w/o Access, but AFAIK Access is not required to read data from Jet databases when using methods provided by ADO, ODBC, etc.

Don’t know if you can read Access data from MySQL via ODBC or other methods. I’ve had no problem doing the converse – linking MySQL tables in Access, using the MySQL ODBC provider – but haven’t been doing much MySQL stuff lately so am not sure if possible.

HTH

Reply | Quote

Mark, hi.

Thank you for that – it worked beautifully on my PC (albeit that I have Access too) so I’ve sent it on to my father to see if it will work on his system. I’ll have to have a look inside and see how you did it!

Thanks again.

Stuart

Reply | Quote