asked on
Converting Access Application to SQL Server
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:
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
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
DatabasesMicrosoft AccessMicrosoft SQL ServerSQL
Last Comment
Gustav Brock
Hey, James.
What are the errors you are getting?
What are the errors you are getting?
ASKER
I'm getting three basic errors depending on which query failed:
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
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
It's not just like that.
But several guides can be found for the browsing. Too large a subject for a tiny question.
But several guides can be found for the browsing. Too large a subject for a tiny question.
ASKER
Ok, let me ask this: Queries that are converted from Access to SQL Server then become pass-through queries in Access, correct?
James
James
No, they could be views that are linked. Also, PT queries are read-only which, in many cases, is useless.
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.
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Gustav,
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
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
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Very good. Thank you for the assist.
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
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
You are welcome!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
The "difficult" queries, however, it is not good at. Those you will have to handle manually.