A field team needs a subset of our SQL Server data in a portable Access file they can use offline. I do not want to give them server access. How do I get selected tables into an .accdb?
How to convert a SQL Server database to MS Access?
Solved SQL & Databases
AJ
Andrew Jackson November 27, 2019
2 replies
5,540 views
Reviewed by moderators
Access can pull directly from SQL Server, which is the cleanest route.
1
In Access: External Data, New Data Source, From Database, SQL Server. Point it at the instance.
2
Choose Import rather than Link, since they need it offline. Pick just the tables the field team requires.
3
Watch data types: SQL datetime2, geography and large varchar(max) can truncate or fail on import. Cast them to Access friendly types in a view first, then import the view.
Imported through a view that cast the tricky columns. Portable accdb in the field team's hands. Solved.