Getting data out of a locked MS Access MDE
May 11th, 2005 by alpriest
Problem: I have an access 97 MDE file (compiled MDB) which uses an external data source (.DAT ?) on a network via linked tables. It has user security on it and I need to get the data out into SQL server. Because of the user security the upsizing wizard with Access 2000 cannot convert the database before upsizing it.
Solution: If you have a login to the access database then open the MDE with shift held down, or press F11 once you’re in the MDE to bring up the table/query lists.
Select the table you want to export, hit File, Save As/Export and choose ODBC. Create an ODBC connection to your SQL server and export the data. This copies the data to SQL server.
However, you may get ‘overflow’ messages back with no detail as to what went wrong. If this happens export the same table to a temporary Excel spreadsheet. The export process this time will create a temp table called TABLENAME_export_errors. Fix those errors in your table, and you can export to ODBC again.
