I have two people who need to use the same database at the same time. The original database was built in Microsoft Access but we had problems with both users trying to access the same MDB file.
I decided to move all the data tables from Access over to a MySQL server using the migration tools. Then I connected Access back to these MySQL tables using an ODBC connection.
During this process I noticed a few things:
Tables without primary keys won’t let you add, edit, or remove records (makes sense)
Auto-increment fields from Access only work right if they are set as the main key, otherwise MySQL just treats them as regular number fields
Even though I had relationships set up in Access, these didn’t get converted to foreign key rules in MySQL when using InnoDB tables
Now that everything is set up and running, what other problems should I watch out for? I’m especially worried about what might happen when both users are editing data in the same table at the same time.
sql syntax varies between access and mysql, so you’ll hit issues. access uses different wildcards and date functions - ur queries won’t work as-is. mysql’s also case-sensitive for table and field names while access isn’t. this trips up tons of people during migration.
Nobody’s mentioned how Access handles memo fields versus MySQL text fields yet. I had a project where long text entries got truncated randomly - the ODBC connection was cutting off anything over 255 characters unless you configure field sizes properly. Also, watch your network stability. Regular Access databases handle dropped connections fine, but with MySQL you’ll get corrupted forms and half-saved records. Users need different habits too - they can’t leave records open while going to lunch anymore. You’ll have to manage the optimistic versus pessimistic locking yourself. One more gotcha - Access calculated fields break once you move to MySQL. You’ll need to recreate that logic as MySQL computed columns or handle it in your forms.
Been there, done that. The real issue isn’t just the concurrency problems - you’re patching together two systems that weren’t built to work together.
Access caches data so you’ll get phantom reads constantly. MySQL connection timeouts are a nightmare when users leave forms open. ODBC adds another failure point.
This setup needs automation. Instead of forcing Access forms to talk to MySQL, build a proper workflow that handles data operations automatically. Set up triggers for data changes, automatic conflict resolution, real-time syncing between users.
I’ve seen this exact problem solved by automating the whole data pipeline. Create workflows that manage database operations, handle user permissions, and send notifications when conflicts happen. Way cleaner than hoping Access and MySQL play nice.
You can keep your existing MySQL setup and just automate all the messy parts.
ODBC driver version matters way more than people think. Hit this same issue two years back - newer drivers handle pessimistic locking much better. Your biggest headache will be users who browse records slowly. Access holds locks forever, blocking everyone else for no good reason. What really got me was how Access treats null values vs MySQL. Empty Access fields can break things when MySQL expects proper nulls. Performance tanks hard once you’re past a few thousand records since everything goes through ODBC. Set shorter connection timeouts in your ODBC config or you’ll get zombie connections piling up. Also, Access loves caching old schema info. Make structural changes to MySQL tables? Users won’t see them until they completely restart Access.
You’re absolutely right to worry about concurrent editing. I ran into this exact problem with a similar setup. The biggest headache was record locking - Access and MySQL handle it completely differently. Users ended up overwriting each other’s work without any warning. Access forms also won’t refresh properly when someone else changes data, so you’ll be looking at outdated info. With MySQL, you really need proper indexes on fields you query a lot. It’s way more critical than with regular Access tables. Also, heads up on date/time formats - MySQL is much pickier about this than Access ever was. I’d definitely add a timestamp field to track when records get modified. Makes it so much easier to spot conflicts when they happen.
true, concurrency is tricky! MySQL can be tough with access, so u might face some data issues. Make sure to back up regularly, and try using transactions to keep things safe. It can really help with data integrity.