AI-Assisted Access to SQL Server Migration

Where AI Helps, Phase by Phase

Phase 1: Document Before You Move Anything

Phase 2: Move Tables With SSMA, Review With AI

• Run SSMA for Access to create the SQL Server schema and migrate data.

• Export the SSMA assessment report and the generated table definitions.

• Ask AI to review the mappings: Currency to money or decimal, Date/Time to datetime2, Yes/No to bit with a default, Long Text to nvarchar(max).

• Ask it to propose indexes on every foreign key and on fields used in WHERE clauses of your heaviest queries.

• Add a rowversion column to tables edited through forms; Access uses it to detect changes efficiently.

Phase 3: Translate Heavy Queries to T-SQL

Prompt:
Translate this Microsoft Access query to a SQL Server T-SQL view.
- IIf → CASE, Nz → ISNULL/COALESCE, & → + or CONCAT, # dates → 'yyyy-mm-dd'
- DLookup/DSum → joins or correlated subqueries
- Format() for grouping → FORMAT or CONVERT, prefer date arithmetic
- Keep column names identical so forms and reports still bind.
- List anything whose behaviour may differ (nulls, rounding, collation).

Access SQL:
SELECT c.CustomerID, c.CompanyName,
       Nz(DSum("Amount","tblInvoices","CustomerID=" & c.CustomerID & _
           " AND Paid=False"),0) AS OpenBalance,
       IIf(OpenBalance>10000,"Review","OK") AS Flag
FROM tblCustomers AS c;

Expected T-SQL view:
CREATE VIEW dbo.vwCustomerOpenBalance AS
SELECT c.CustomerID, c.CompanyName,
       ISNULL(i.OpenBalance, 0) AS OpenBalance,
       CASE WHEN ISNULL(i.OpenBalance, 0) > 10000 THEN 'Review' ELSE 'OK' END AS Flag
FROM dbo.tblCustomers AS c
LEFT JOIN (SELECT CustomerID, SUM(Amount) AS OpenBalance
           FROM dbo.tblInvoices WHERE Paid = 0
           GROUP BY CustomerID) AS i
  ON i.CustomerID = c.CustomerID;

Phase 4: Scan VBA for Breaking Patterns

Prompt:
Here is an Access VBA module. The tables will become linked SQL Server
tables with identity primary keys and bit fields for Yes/No.
List every line likely to break or slow down, why, and the corrected code.
Do not change behaviour beyond what the migration requires.

Phase 5: Test by Comparing Results

• Keep a copy of the pre-migration Access database as the reference.

• For each important query and report, run it on both, export to Excel, and compare row counts and totals.

• Ask AI to draft a test list from your documentation: every form action that writes data, every report used monthly.

• Test with several users at once; concurrency problems do not show up in single-user testing.

• Time the slowest forms before and after; performance is often the reason for migrating.

Where AI Is Not Enough

• Deciding what to migrate, what to retire and what to rebuild.

• Security design: logins, roles and which users can see which tables.

• Backup, maintenance plans and hosting decisions.

• Judging when a translated query is 'equivalent enough', especially with nulls, rounding and text collation.

• Cutover planning when people use the database every day.