Converting Access Application to SQL Server
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.
James
The "difficult" queries, however, it is not good at. Those you will have to handle manually.
A2SS0061: The identifier 'IIf(BOOLEAN, UNKNOWN, NUMERIC)' was not converted. --- I assume here I'll need to manually make a new query using SELECT CASE WHEN END.
A2SS0069: External variable cannot be converted. --- The query is referencing field on a form. I'll have to have the form save data to SQL Server first - maybe to a temp table.
A2SS0061: The identifier 'Month(UNKNOWN)' was not converted. --- Convert to SQL Server date time commands.
So I can create views, stored procedures, and so on all day in SQL. How do I get Access to use them instead of the queries it has? Is there a link command or something?
James
But several guides can be found for the browsing. Too large a subject for a tiny question.
James
But there is no right or wrong method, it all depends. An application may run fine with no converted queries at all - leaving the job to the ODBC driver. Most start there, then localise bottlenecks, if any, and then work with these.
Ok, we are getting somewhere. The vast majority of queries in the Access database are SELECT so they can all become views. I'm totally fine with that. The handful of Update queries I was going to make into Stored Procedures in SQL Server. How would I reference those in Access if Pass-through is not an option for them?
James
With over 400 queries and right at 300 of the failing the migration assistant, I do have some work ahead of me. But most of it is changing DATE() to GETDATE() and IIF() to SELECT CASE. I just needed to know how Access references the new objects once moved to SQL Server.
James
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions