Access 2010, Development for SQL Server Backend
Posted on 2013-11-22
I had originally written an app for a client in Access 2003. The database was split with the data residing on the server and a local copy on each users desktop.
The data storage requirements have grown beyond the 2GB upper limit so we are going to make the backend SQL Server, also moving the application to Access 2010.
I moved the app to Access 2010 and ran the SQL upsize wizard to create the backend in SQL Server.
I have many reports, queries and coding in modules. The coding directly interacts with the database. Adding records, retrieving records etc...
When I first 'upsized' I had an issue because my linked tables all had a prefix of 'dbo_'. For example 'tblAccounts' became 'dbo_tblAccounts'. Any queries based on 'tblAccounts' didn't find it. I resolved that by removing the 'dbo_' prefix from the linked table names. Then the queries worked just as they had before the 'upsize'.
The good news is that as I run thru all the functionality of the application it all seems to work and the response is good. Of course I'm the only one in the database.
Should I be changing any application options, coding techniques, query design or form layouts to perform better using SQL Server?