Upsizing Access Database Question(s)

[Kocheace]

If I have an access fronet-end that's going to be upsized to SQL Server, will I have to make any changes to the embedded SELECT queries in my modules? I know that you have to use stored procedures to convert SELECT queries that have parameters but does that apply to Embedded SQL statements?

IT Professional

 

[SQLSister]

You might have to convert some of them because Access SQL and T-SQL are very different flavors of SQL. For instance, in AccessSQL you can use the IIF function in a SQl statement and the T-SQL equivalent is the Case Statement.

 

[Kocheace]

SQLSIS:

Would something like this (See Below) work after being uploaded?


DoCmd.OpenForm "View", acFormDS, , "Building ='" & BuildingID & "'"


IT Professional

 

[cheerio]

You need to ask yourself where the query will be executed. If Access will execute then you use JET syntax. If the SQL Server engine will execute you need MSSQL syntax.

The chances are that most of your queries will still be executed locally and will still work.

However, to get the real benefit of upsizing you need to get queries onto the server. For example, on another thread where I was advising on the use of Access Pass Through queries the user has achieved a reduction from 3 hours to 20 minutes by converting local processes into server processes.