Import an Access file into DB Studio
Import your Access file locally, review what was preserved, check tables and records, then export and reopen a SQLite copy.
What you'll learn
You will import an Access database, read the conversion report, check the imported structure and records, and export and reopen a .sqlite copy.
Before you begin
Open https://db.pyroom.app/ and have your own .accdb or .mdb file ready. The video uses a small, attributed software test fixture. Its Table1 contains two records; your file will have different table names and counts. Use those names and counts in the checks below.
Use a desktop to follow the whole video: table design is available on desktop and tablet, while the query designer is desktop-only.
Import runs in your browser. Nothing is uploaded, and database contents never leave your device. The original Access file is left unchanged. The whole file is read into memory, so start with a small classroom database. Encrypted or password-protected files are refused.
The importer can bring across tables, data, field types, simple formats, lookups, and relationships. Relationships are enforced only if they were enforced in Access and the data is clean. For a comparison of supported features, see Access alternative.
Steps
- In My databases, click Open file and select your
.accdbor.mdbfile. Wait for Import Access database to open. - Enter Access practice in Database name and click Import. DB Studio creates a local database from the file.
- Read Access import summary. Under Imported tables, compare the table names and row counts with your source file. The video shows Table1 with two rows.
- Read Details that could not be preserved and the Not imported report before clicking Close summary. Note anything you need to recreate or check.
- Open the Access practice card, then open Table1, or the corresponding table in your file. Check its records. Switch from Data to Design, inspect the field names and data types, then return to Data. The video checks the field A and two records.
- Click New query, choose Design, open SQL, and click Edit SQL. Enter
SELECT COUNT(*) AS "Imported rows" FROM "Table1";, replacing Table1 with your table name if needed. Press Escape and click Run. Compare the result with the import summary; the video gets 2. - Close the temporary query tab. In Unsaved query changes, click No to close it without saving.
- Click the PyRoom Database Studio logo to return to My databases. Open the menu on the Access practice card and choose Export. Keep the downloaded Access practice.sqlite alongside your original Access file.
- Click Open file and select Access practice.sqlite. In Import SQLite database, enter Access practice copy in Database name and click Import.
- Open Access practice copy and its Table1, or your corresponding table. Check that the records and count match the converted database. The video still shows two records.
Expected result
Access practice contains the converted tables and records, and Access practice copy reopens the exported SQLite database. The report tells you which details were lost or skipped. Both databases are stored in this browser profile; clearing site data deletes local databases, so keep the exported file and the original Access file.
Common mistakes
- Import does not bring across saved queries, forms, reports, macros, or VBA modules. Recreate the objects you need separately.
- Non-AutoNumber primary keys, indexes, validation rules, default values, and cascade options are not imported. Check the converted structure before relying on it.
- Non-image attachments and OLE objects, and linked tables, are not imported. Image attachments may be imported; check the report for your file.
- Do not assume every Access relationship is enforced after conversion. It must have been enforced in Access, and the data must satisfy it.
- Use your own table names and expected counts. Table1 and the result 2 belong to the video's test fixture.
- Export saves the converted database as
.sqlite. It does not save changes back to the original.accdbor.mdb. See Save and reopen for more practice with exported copies.