Getting data out of a locked MS Access MDE

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s