troubleshooting Question

Converting Access Application to SQL Server

Avatar of JamesNT
JamesNT asked on
DatabasesMicrosoft AccessMicrosoft SQL ServerSQL
10 Comments1 Solution162 ViewsLast Modified:
I'm using the SQL Server Migration Assistant for Access to move an Access database to SQL Server.  Many of the queries failed to migrate.  Here is an example of one:

UPDATE ttbl_Equian_Clients, qryEquian SET qryEquian.Parent = IIf([AccountName]="Equian",[ClientName],[AccountName]), qryEquian.[Employer/Insured] = [ClientName], qryEquian.payor = IIf([AccountName]="Equian",[ClientName],[AccountName]), qryEquian.[claim admin] = IIf([AccountName]="Equian",[ClientName],[AccountName])
WHERE (((ttbl_Equian_Clients.[Client ID])=Left([CLAIM NUMBER],4) Or (ttbl_Equian_Clients.[Client ID])=Left([CLAIM NUMBER],5)));

How do I fix such a query and manually migrate it?  I can easily turn that into a stored procedure, but how does Access then know to use the stored procedure?

Apologies if I seem a bit dumb, this project kinda got thrown at me and I'm a SQL Server guy, not an Access guy.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros